1

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.

sourcerebels
  • 5,140
  • 1
  • 32
  • 52
Dejene
  • 11
  • 1
  • 4
  • Just an interesting note; If your format is in the following form: YYYY-MM-DD HH:MM:SS (e.g. 2009-08-12 12:27:50), SQL Server will always parse it correctly regardless of your regional settings. – Mr. Smith Aug 12 '09 at 10:28
  • 1
    See for why parameters are your friend: http://stackoverflow.com/questions/1263125/how-does-sql-query-parameterisation-work/1263142#1263142 – Eric Aug 12 '09 at 12:12

8 Answers8

4

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));
Espo
  • 41,399
  • 21
  • 132
  • 159
  • 2
    I agree with the parameterisation, but would recommend not using .AddWithValue as it may not behave as you expect as you don't actually specify the underlying datatype. e.g. http://chrisrickard.blogspot.com/2007/06/addwithvalue-is-evil.html – AdaTheDev Aug 12 '09 at 10:39
3

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);
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
2

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.

Russell
  • 17,481
  • 23
  • 81
  • 125
2

You can kill 2 birds with one stone and use a parameter:

UPDATE emp SET bDate=@newDate

And fill the parameter value with a Date directly, using DateTime.Parse() to do the conversion. This also eliminates the SQl injection problem you have now.

H H
  • 263,252
  • 30
  • 330
  • 514
1

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.

dkartopr
  • 366
  • 2
  • 8
  • I will test your solutions with parameterized query soon. but Convert.ToDateTime(txtbDate.Text).ToString("yyyy-MM-dd") works perfectly. I tried this for more than half a day. Thank you dkartopr Thank all – Dejene Aug 12 '09 at 10:44
0

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)))
Wael Dalloul
  • 22,172
  • 11
  • 48
  • 57
0

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

Russell Steen
  • 6,494
  • 6
  • 38
  • 56
  • Why was this downvoted? This directly addresses his actual error (failed conversion due to the date time being out of range) – Russell Steen Aug 12 '09 at 12:29
-1

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

RvdK
  • 19,580
  • 4
  • 64
  • 107