471,337 Members | 810 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 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 1866
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Dominique Javet | last post: by
6 posts views Thread by Jerome | last post: by
19 posts views Thread by Lauren Quantrell | last post: by
1 post views Thread by Ugur Ekinci | last post: by
6 posts views Thread by Mark | last post: by
reply views Thread by rosydwin | last post: by

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.