473,388 Members | 1,499 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

Problem inserting dates to SQL Server

RP
I have a DateTimePicker with format dd-MM-yyyy. While attempting to
insert this date in SQL Server Date column, following exception is
thrown:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Please help.

Aug 17 '07 #1
19 1953
RP wrote:
I have a DateTimePicker with format dd-MM-yyyy. While attempting to
insert this date in SQL Server Date column, following exception is
thrown:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
Are you passing the parameter in as string or as DateTime (referring to
the other thread you had started on this).

In the other thread you had code that was attempting to parse the string
into a DateTime object. I assumed that you were then passing that into
your SQL call. But you are saying the error is still in around
conversion, which indicates to me that you are passing a string into SQL
server and hoping it can parse that into the correct type.

So my first suggestion would be to change your call to SQL to pass in
the DateTime object rather than a string.

If that is not an option, then the following code should work.

string dateToPass = DateTime.ParseExact(txtDOB.text, "dd-MM-yyyy",
System.Globalization.DateTimeFormatInfo.CurrentInf o).ToString("MM/dd/yyyy");

You could surely also do the conversion using regular expression,
possibly more efficiently, but that just isn't my forte.
--
Tom Porterfield
Aug 17 '07 #2
RP
Tom,

Doing something like this.

DateTime myDate = dateTimePicker1.Value;
Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate
values ('" + myDate + "')");
MessageBox.Show(myRes.ToString());

public Int32 InsertNewRecord(string myQuery)
{
objModCon.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(myQuery,
objModCon.myCN);
try
{
Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
return RecordsAffected;
}
catch (SqlException ex)
{
Console.WriteLine(ex);
return 0;
}
finally
{
cmdInsert.Dispose();
objModCon.CloseConnection();
}

}

Aug 17 '07 #3
That's just not a good idea. You should be creating a command which
parameterizes the sql statement, like so:

insert into testdate values (@testDate)

And then set the value of the parameter in code.

If you HAVE to use a generated string (and I really suggest you don't)
then you need to do this:

int myRes = ModRes.InsertNewRecord("insert into TestDate values ('" +
myDate.ToString("yyyy-MM-dd") + "')");

The format yyyy-MM-dd is the format that SQL server will always
recognize.

The code that you have now, btw, is an injection attack waiting to
happen.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"RP" <rp*********@gmail.comwrote in message
news:11**********************@q3g2000prf.googlegro ups.com...
Tom,

Doing something like this.

DateTime myDate = dateTimePicker1.Value;
Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate
values ('" + myDate + "')");
MessageBox.Show(myRes.ToString());

public Int32 InsertNewRecord(string myQuery)
{
objModCon.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(myQuery,
objModCon.myCN);
try
{
Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
return RecordsAffected;
}
catch (SqlException ex)
{
Console.WriteLine(ex);
return 0;
}
finally
{
cmdInsert.Dispose();
objModCon.CloseConnection();
}

}

Aug 17 '07 #4
RP
Nicholas,

Finally this:

int myRes = ModRes.InsertNewRecord("insert into TestDate values ('" +
myDate.ToString("yyyy-MM-dd") + "')");

worked.

I wonder how things have complicated in C#. I have been using VB.NET
but did not encounter such problem. Please let me know whether myDate
used must be of type DateTime or String.

What is SQL Injection?

Aug 17 '07 #5
RP wrote:
Nicholas,

Finally this:

int myRes = ModRes.InsertNewRecord("insert into TestDate values ('" +
myDate.ToString("yyyy-MM-dd") + "')");

worked.

I wonder how things have complicated in C#. I have been using VB.NET
but did not encounter such problem. Please let me know whether myDate
used must be of type DateTime or String.

What is SQL Injection?
As Nicholas has said, you need to set up a parameterized command. But
if you refuse to do that, then the date must be a string, properly
formatted, as that is all you have, which is what the above is
converting it to.
--
Tom Porterfield
Aug 17 '07 #6
On Aug 17, 12:39 pm, RP <rpk.gene...@gmail.comwrote:
What is SQL Injection?
http://en.wikipedia.org/wiki/Sql_injection_attack

Chris

Aug 17 '07 #7
RP
Tom,

Please illustrate what you said.

Anyway, I got a simple solution. I changed the format of
DateTimePicker to dd-MMM-yyyy and used following code:

================================================== ==============
Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate values ('"
+ dateTimePicker1.Text + "')");
================================================== ==============

It worked. If possible, please also show how to use parameterized
command.

Aug 17 '07 #8
RP,

There is another post in this thread with a link describing SQL
injection.

As for things being complicated in C#, and working in VB.NET, I don't
think it is a matter of complication.

First, SQL Server works under a locale, and when confronted with a date
in string form that is not the universal format in SQL Server (either
'yyyy-MM-dd' or 'yyyyMMdd' in .NET date format terms) it will try to parse
it using the locale that SQL Server is running in (and maybe some others, I
am not sure).

If the local of the SQL Server and the local of the client running the
code and converting the string to be sent are different, you have a good
chance that SQL Server will not understand the string.

Now, when you use the + operator when concatenating strings, it is going
to call ToString on the operands. In this case, the DateTime will have
ToString called on it, using the current thread's culture info to determine
the format to represent the date in. How VB does this I do not know, as the
language might be resorting to a different conversion method than C# (when
using the concatenator in the language).

This is why calling ToString explicitly with that date format will
always work.

However, it is better to use the parameterized command, as it will
convert directly from the .NET type without you having to worry about any of
that.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"RP" <rp*********@gmail.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.com...
Nicholas,

Finally this:

int myRes = ModRes.InsertNewRecord("insert into TestDate values ('" +
myDate.ToString("yyyy-MM-dd") + "')");

worked.

I wonder how things have complicated in C#. I have been using VB.NET
but did not encounter such problem. Please let me know whether myDate
used must be of type DateTime or String.

What is SQL Injection?

Aug 17 '07 #9
RP
Nicholas,

VB used & to concatenate strings. Please show how to use parametrized
commands.

Aug 17 '07 #10
Well, I can't show it for the InsertNewRecord method, but with the
parameters you have shown, this is how you would do it:

// Create the connection.
using (SqlConnection connection = <code to get sql connection>)
{
// Create the command.
using (SqlCommand command = new SqlCommand("insert into TestDate values
(@testDate)", connection))
{
// Add the parameter.
command.AddWithValue("@testDate", myDate);

// Execute the command.
command.ExecuteNonQuery();
}
}
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"RP" <rp*********@gmail.comwrote in message
news:11*********************@j4g2000prf.googlegrou ps.com...
Nicholas,

VB used & to concatenate strings. Please show how to use parametrized
commands.

Aug 17 '07 #11
RP
Parameterized commands must be used for all values or only for dates?

Aug 17 '07 #12
RP
I feel a Stored Procedure must have done a good job. In case so, is it
suitable in client/server environment?

Aug 17 '07 #13
RP,

You can use stored procedures in a client/server environment. The two
are not mutually exclusive.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"RP" <rp*********@gmail.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.com...
>I feel a Stored Procedure must have done a good job. In case so, is it
suitable in client/server environment?

Aug 17 '07 #14
RP
Is it possible to change SQL Server locale, so that it uses the format
which I want to use?

Aug 17 '07 #15
Hi,

"RP" <rp*********@gmail.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.com...
>I feel a Stored Procedure must have done a good job. In case so, is it
suitable in client/server environment?
A SP has nothing todo with the environment, it's just another object stored
in the DB
Aug 17 '07 #16
Yes, but you shouldn't be doing that anyways, because then all the code
that you have issuing code against that database would have to have the same
locale.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"RP" <rp*********@gmail.comwrote in message
news:11**********************@x40g2000prg.googlegr oups.com...
Is it possible to change SQL Server locale, so that it uses the format
which I want to use?

Aug 17 '07 #17
RP
No problem. I will use a standard format for my app. How to change the
locale?

Aug 17 '07 #18
Again, why change the locale when there is a locale-independent format
which SQL Server will recognize?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"RP" <rp*********@gmail.comwrote in message
news:11**********************@z24g2000prh.googlegr oups.com...
No problem. I will use a standard format for my app. How to change the
locale?

Aug 17 '07 #19
On Fri, 17 Aug 2007 16:43:37 -0000, RP <rp*********@gmail.comwrote:
>I have a DateTimePicker with format dd-MM-yyyy. While attempting to
insert this date in SQL Server Date column, following exception is
thrown:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Please help.
Also be aware that SQL Server will likely throw the same error if
given a datetime earlier than 1 Jan 1732

--
http://bytes.thinkersroom.com
Aug 20 '07 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Dominique Javet | last post by:
Hello, I'm new to php and mysql and I use Dreamweaver MX 2004, so sorry for this "newbie" question... I've found no answer in the forum ... I've a date problem with my formular. In my mysql DB...
13
by: perplexed | last post by:
How do you convert a user inputted date to a unix timestamp before insterting it into your database? I have a form, with a textfield for a date that the user inputs in the format mm-dd-yyyy and...
6
by: Jerome | last post by:
Hi, I know this is an old problem and I've already tried to look the solution up on the web but I didn't find what I need. So, there's the following situation: I've got an ASP page with a...
19
by: Lauren Quantrell | last post by:
I have a stored procedure using Convert where the exact same Convert string works in the SELECT portion of the procedure but fails in the WHERE portion. The entire SP is listed below....
5
by: Sparrow | last post by:
I have created a table with the following columns... Date(datetime),Actual (Int),Planned (Int) I need to insert weekending dates starting from 23/04/04 looping thru'for the next 52weeks...
2
by: Tronex | last post by:
Hi there, I am desperate and need help on this from someone who actually knows what he does... cuz I certainly don't ;-) This is the situation: I have an ASP.NET web application (written in...
1
by: Ugur Ekinci | last post by:
Hi , I have two Sql Server 2000 on seperate machines , First one accepts datetime format like ("dd.MM.yyyy hh:mm:ss") And Second one accepts datetime format like ("MM.dd.yyyy hh:mm:ss") 1-...
1
by: suslikovich | last post by:
Hi all, I am getting this error when insert values from one table to another in the first table the values are varchar (10). In the second they are datetime. The format of the data is mm/dd/yyyy...
6
by: Mark | last post by:
Hi, i have an application which works with date. The regional settings of the computer (XP prof. dutch version) are set to French (Belgium). Asp.net and Sql server take the short date format of...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.