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.

 

[Tom Porterfield]

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.CurrentInfo).ToString("MM/dd/yyyy");

You could surely also do the conversion using regular expression,
possibly more efficiently, but that just isn't my forte.

 

[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();
}

}

 

[Nicholas Paldino [.NET/C# MVP]]

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.

 

[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?

 

[Tom Porterfield]

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.

 

[Chris Dunaway]

What is SQL Injection?

http://en.wikipedia.org/wiki/Sql_injection_attack

Chris

 

[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.

 

[Nicholas Paldino [.NET/C# MVP]]

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.

 

[RP]

Nicholas,

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

 

[Nicholas Paldino [.NET/C# MVP]]

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();
}
}

 

[RP]

Parameterized commands must be used for all values or only for dates?

 

[Nicholas Paldino [.NET/C# MVP]]

All values. The call to AddWithValue determines what the type on the DB
should be, and creates the parameter accordingly.

There was an error in that code, this line:

command.AddWithValue("@testDate", myDate);

Should be:

command.Parameters.AddWithValue("@testDate", myDate);

You should look at the documentation for the Add method on the
SqlParametersCollection for info on how to add a parameter of other types.

 

[RP]

I feel a Stored Procedure must have done a good job. In case so, is it
suitable in client/server environment?

 

[Nicholas Paldino [.NET/C# MVP]]

RP,

You can use stored procedures in a client/server environment. The two
are not mutually exclusive.

 

[RP]

Is it possible to change SQL Server locale, so that it uses the format
which I want to use?

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

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

 

[Nicholas Paldino [.NET/C# MVP]]

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.

 

[RP]

No problem. I will use a standard format for my app. How to change the
locale?

 

[Nicholas Paldino [.NET/C# MVP]]

Again, why change the locale when there is a locale-independent format
which SQL Server will recognize?