Update difficulty in .NET

Question

Update can't work.

sqlstr ="UPDATE emp SET  bDate='"+Convert.ToDateTime(txtbDate.Text)+"'";

can't update emp table. I tried also using Parse method. It throws error message : The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.

Answer 1

You should allways use sql parameters when accepting input from a user. This will probably solve your problem as well as increasing security. Try this:

sqlstr ="UPDATE emp SET bDate=@bDate";
SqlCommand.Parameters.AddWithValue("@bDate", Convert.ToDateTime(txtbDate.Text));

Answer 2

it should be plain string because you store it in a sqlstr ;)

Answer 3

You can use parameterised stored procedures.

The .net datetime contains more values than the SQL DateTime, so thus the out of range error.

Parameterised stored procs also provide more security against sql injection attacks.

Answer 4

Don't use adhoc SQL like this, use parameterised SQL:

sqlstr = "UPDATE emp SET bDate=@NewDate WHERE...."

Then on your SqlCommand, add the @NewDate parameter:

YourSqlCommand.Parameters.Add("@NewDate", SqlDbType.DateTime);
YourSqlCommand.Parameters["@NewDate"].Value = Convert.ToDateTime(txtbDate.Text);

Answer 5

use Parameters to pass the date to the query this if you are using ole db:

sqlstr = "UPDATE emp SET bDate=? "
command.Parameters.Add(New OleDbParameter("@bDate", Convert.ToDateTime(txtbDate.Text)))

Answer 6

Hi,

have you tried to parse the date value to SQL format(yyyy-MM-dd), ex 2000-12-31

Convert.ToDateTime(txtbDate.Text).ToString("yyyy-MM-dd");

Cheers.

Answer 7

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated."

You're date-time is not in the range accepted by the SQL DateTime. What date are you trying to parse? I've this error for some really early dates (1/15/103 for example). Dates are stored in ticks from an arbitrary start point.

The start point for .net is 1/1/0001
The start point for SQL is 1/1/1753

I'm not sure about end values. Try running these and compare. Either code trace, or console writeline.

DateTime netDate = DateTime.MinValue;
SqlDateTime sqlDate = SqlDateTime.MinValue; DateTime netMaxDate = DateTime.MaxValue;
SqlDateTime sqlMaxDate = SqlDateTime.MaxValue;

Read what everyone else said about parameterizing queries.