How to update "Date" field into database ?

D

desmondtan

Active member
Joined
Dec 24, 2002
Messages
40
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 .
 
This might help:

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

Code: 
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.:

Code: 
Dim varDate = datetimepicker1.value

And then use that within the SQL as:

strSQL = "UPDATE Employee SET Dob = #" & varDate & "# WHERE ....."
 
I alway convert my dates to double before putting into database using this method

Code: 
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:
[VB]
strSQL = "UPDATE Employee SET Dob = " & Datetimepicker1.Value.ToOADate() " & WHERE EmployeeID = 10001"
[/VB]

Andy
 
Back
Top