How to update "Date" field into database ?

[desmondtan]

In VB.NET , I tried to update "date" filed in my table form my Datetimepicker from a Windows form. The SQL is :

 

strSQL = "UPDATE Employee SET Dob = Datetimepicker1.value WHERE EmployeeID = 10001"

 

However , the DOB field is always updated as 01/01/1900

 

The databse I used in MSDE , the Dob field is smalldatetime .

 

 

I tried get many get answer from some reference book ,but most of example are in updating String and Integer . I really need someone who knows the ways to update "date" field. Thank you .

[stustarz]

This might help:

 

Have you set the date format of the datetimepicker control to the same as your database

 

datetimepicker1.customformat = 'whatever your date format should be

 

Also in SQL I would create a variable (e.g. varDate) set to the value of the datetimepicker value e.g.:

 

Dim varDate = datetimepicker1.value

 

And then use that within the SQL as:

 

strSQL = "UPDATE Employee SET Dob = #" & varDate & "# WHERE ....."

[a_jam_sandwich]

I alway convert my dates to double before putting into database using this method

 

DateTimePicker1.Value.ToOADate()

 

You can then be sure no matter what format or reginal settings are on the date, the date value is correct

 

SQL:

strSQL = "UPDATE Employee SET Dob = " & Datetimepicker1.Value.ToOADate() " & WHERE EmployeeID = 10001"

 

Andy

[desmondtan]

Thank you stustarz and Andy .

 

I applied the Andy method , it works . Both of you are my Lucky Star