[RESOLVED] Trouble adding a date value into a SQL Server database using VB.net

[appolospb]

I am having trouble getting my vb.net program to update a date value in my SQL Server database.

The datatype for the field 'Date Out' is set to 'date' type in the database.

The code i am using to update the database fields and date is as follows:

        Dim cmd As SqlCommand = New SqlCommand
        cmd.CommandTimeout = "60"
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "UPDATE Job SET Status='" & CBEquipmentTestRepairStatus.SelectedItem & "', Report='" & TBEquipmentTestTestReport.Text & "', [Beyond Economical Repair]='No', Forename='" & TBEquipmentTestForename.Text & "', Surname='" & TBEquipmentTestSurname.Text & "', [Date Out]='" & DTPEquipmentTestDateOut.Value & "' WHERE [Returns ID]='" & DGVEquipmentTest(7, currentr).Value & "'"
        cmd.Connection = con

        cmd.ExecuteReader(CommandBehavior.Default)

I have tried several methods of supplying the 'Date Out' value to the SQL database. The above sample is using a 'Date time picker' i have also tried manually submitting the date using the below code:

Dim testdate As Date = New Date(My.Computer.Clock.LocalTime.Year, My.Computer.Clock.LocalTime.Month, My.Computer.Clock.LocalTime.Day)

Using the above two methods the database comes back with the error 'Conversion failed when converting date and/or time from character string' . The only time i have ever successfuly got the date to add to the database was when i manually typed in '1/12/2010' into the SELECT statement.

Any help would be very much appreciated

[appolospb]

I have tried the below and this also doesnt seem to work:

        Dim cmd As SqlCommand = New SqlCommand
        cmd.CommandTimeout = "60"
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "UPDATE Job SET Status='" & CBEquipmentTestRepairStatus.SelectedItem & "', Report='" & TBEquipmentTestTestReport.Text & "', [Beyond Economical Repair]='No', Forename='" & TBEquipmentTestForename.Text & "', Surname='" & TBEquipmentTestSurname.Text & "', [Date Out]='CONVERT(date," & DTPEquipmentTestDateOut.Value & ",103)' WHERE [Returns ID]='" & DGVEquipmentTest(7, currentr).Value & "'"
        cmd.Connection = con

        cmd.ExecuteReader(CommandBehavior.Default)

[appolospb]

Solved the problem. The below statement works, guess it was something to do with the Apostrophe's i was using and that i also needed to use the 'Convert' statement.

cmd.CommandText = "UPDATE Job SET Status='" & CBEquipmentTestRepairStatus.SelectedItem & "', Report='" & TBEquipmentTestTestReport.Text & "', [Beyond Economical Repair]='No', Forename='" & TBEquipmentTestForename.Text & "', Surname='" & TBEquipmentTestSurname.Text & "', [Date Out]=CONVERT(date,'" & DTPEquipmentTestDateOut.Value & "',103) WHERE [Returns ID]='" & DGVEquipmentTest(7, currentr).Value & "'"

[si_the_geek]

This is one of the many things that would never have been an issue if you used Parameters, rather than building the SQL statement as you do.

For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).

[appolospb]

Yep what you have said is fairly common sense with a few things i didnt know about security thrown in. Our company is only small so i dont at the moment need to cater for other people reading my code (Im the sole programmer). However i will probably adopt this method on any future code so thanks for the info .