Incorrect date being updated

[hubbard92]

Hi,

I'm new at vb.net and am trying to write a program to update a sql server 2000 database datetime field using ado.net.

I'm pulling my date (Dec 12, 2006) from a datetimepicker control. I've tried to format the date to be "yyyy-mm-dd" which updates the sql table with a date value of June 06, 1905.

I have also tried using the date format of "yyyy-mm-dd hh:mm:ss:fff" which I receive the error that the hh field has incorrect syntax and gives me error number 170 (what ever that is).

Here is my code currently:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
If DateTimePicker1.Value = ws_date Then
MsgBox("The Date Your Trying To Update Is The Same As The Current Run_Tracker Date, Please Choose A Different Date!")
GoTo endsub
End If
Dim ws_date3 As String
Dim ws_date4 As String
ws_date2 = Format(DateTimePicker1.Value, "yyyy/MM/dd 00:00:ss")
ws_date3 = Format(ws_date2, "yyyy-MM-dd hh:mm:ss:fff")
ws_date4 = CDate(DateTimePicker1.Value.ToString("yyyy-MM-dd"))
'Instantiate the connection
cnnRunTracker = New SqlConnection
cnnRunTracker.ConnectionString = "server=sqlproduction;database=" + ws_Database + ";UID=rbeals;password=password;"
' Open the conncection
cnnRunTracker.Open()
' Build query string
strSQL = "UPDATE DBO.RUN_TRACKER SET PROCESS_DATE = " & ws_date3
'Instantiate and execute the command
cmmRunTracker = New SqlCommand(strSQL, cnnRunTracker)
' cmmRunTracker.ExecuteNonQuery()
Try
cmmRunTracker.ExecuteNonQuery()
Catch excsql As SqlClient.SqlException
MsgBox("Messsage is " & excsql.Message & _
"Error number is " & excsql.Number)
Catch excfill As System.Exception
Throw excfill
End Try
strSQL = "SELECT PROCESS_DATE FROM DBO.RUN_TRACKER"
' cnnRunTracker.Open()
cmmRunTracker = New SqlCommand(strSQL, cnnRunTracker)
strRunTimeDate = CStr(cmmRunTracker.ExecuteScalar().ToString)
DateTimePicker1.Value = strRunTimeDate
DateTimePicker1.Visible = True
Button3.Enabled = True
ws_date = DateTimePicker1.Value
' Open the conncection
cnnRunTracker.Close()
ENDSUB:
End Sub

Any help would be greatly appreciated.

 

[lingsn]

Hope this can give you a little help

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706111&SiteID=1

 

[hubbard92]

The link on the site you directed me too no longer works. Can you please tell me what you mean by "typed parameters, using SqlParameter" or direct me to a another link.

Thanks again

 

[lingsn]

Ops, sorry about that, may be check on this on page 20

http://www.kemmou.com/content/binary/ADO.Net.ppt

 

[hubbard92]

Thanks lingsn for the help you provided. It took me several days to be able to figure out how to do what you were asking, but I finally got it to work using parameters and stored procedures. Actually, you helped me twice as I posted another thread on another topic. Happy Holidays