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]

Re: Problem inserting dates to SQL Server

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.ParseE xact(txtDOB.tex t, "dd-MM-yyyy",
System.Globaliz ation.DateTimeF ormatInfo.Curre ntInfo).ToStrin g("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

[RP]

Re: Problem inserting dates to SQL Server

Tom,

Doing something like this.

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

public Int32 InsertNewRecord (string myQuery)
{
objModCon.OpenC onnection();
SqlCommand cmdInsert = new SqlCommand(myQu ery,
objModCon.myCN) ;
try
{
Int32 RecordsAffected = cmdInsert.Execu teNonQuery();
return RecordsAffected ;
}
catch (SqlException ex)
{
Console.WriteLi ne(ex);
return 0;
}
finally
{
cmdInsert.Dispo se();
objModCon.Close Connection();
}

}

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

Re: Problem inserting dates to SQL Server

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.InsertNe wRecord("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]
- mvp@spam.guard. caspershouse.co m

"RP" <rpk.general@gm ail.comwrote in message
news:1187371261 .799495.298830@ q3g2000prf.goog legroups.com...

Tom,
>
Doing something like this.
>
DateTime myDate = dateTimePicker1 .Value;
Int32 myRes = ModRes.InsertNe wRecord("Insert into TestDate
values ('" + myDate + "')");
MessageBox.Show (myRes.ToString ());
>
public Int32 InsertNewRecord (string myQuery)
{
objModCon.OpenC onnection();
SqlCommand cmdInsert = new SqlCommand(myQu ery,
objModCon.myCN) ;
try
{
Int32 RecordsAffected = cmdInsert.Execu teNonQuery();
return RecordsAffected ;
}
catch (SqlException ex)
{
Console.WriteLi ne(ex);
return 0;
}
finally
{
cmdInsert.Dispo se();
objModCon.Close Connection();
}
>
}
>

[RP]

Re: Problem inserting dates to SQL Server

Nicholas,

Finally this:

int myRes = ModRes.InsertNe wRecord("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]

Re: Problem inserting dates to SQL Server

RP wrote:

Nicholas,
>
Finally this:
>
int myRes = ModRes.InsertNe wRecord("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

[Chris Dunaway]

Re: Problem inserting dates to SQL Server

On Aug 17, 12:39 pm, RP <rpk.gene...@gm ail.comwrote:

What is SQL Injection?

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

Chris

[RP]

Re: Problem inserting dates to SQL Server

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.InsertNe wRecord("Insert into TestDate values ('"
+ dateTimePicker1 .Text + "')");
=============== =============== =============== =============== ====

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

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

Re: Problem inserting dates to SQL Server

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]
- mvp@spam.guard. caspershouse.co m

"RP" <rpk.general@gm ail.comwrote in message
news:1187372388 .828458.115310@ x35g2000prf.goo glegroups.com.. .

Nicholas,
>
Finally this:
>
int myRes = ModRes.InsertNe wRecord("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?
>

[RP]

Re: Problem inserting dates to SQL Server

Nicholas,

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

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

Re: Problem inserting dates to SQL Server

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("ins ert into TestDate values
(@testDate)", connection))
{
// Add the parameter.
command.AddWith Value("@testDat e", myDate);

// Execute the command.
command.Execute NonQuery();
}
}


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"RP" <rpk.general@gm ail.comwrote in message
news:1187375223 .569244.67480@j 4g2000prf.googl egroups.com...

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

[RP]

Re: Problem inserting dates to SQL Server

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

[RP]

Re: Problem inserting dates to SQL Server

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]]

Re: Problem inserting dates to SQL Server

RP,

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


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"RP" <rpk.general@gm ail.comwrote in message
news:1187377049 .823711.285080@ x35g2000prf.goo glegroups.com.. .

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

[RP]

Re: Problem inserting dates to SQL Server

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