Syntax error converting character string to smalldatetime data type.

I am passing an update statement in sql and am geting the following error message.  PLease help.  The Step1, Step2, Step3, and Step4 are date fields in my Sql DAtabase in the smalldatetime datatype.

Syntax error converting character string to smalldatetime data type.


Here is my code:



Dim SelectedKeyValue As String = dgTraining.DataKeys(e.Item.ItemIndex)

        'Dim Name As String = CType(e.Item.FindControl("txtName"), TextBox).Text
            Dim Step1 As String = CType(e.Item.FindControl("txtStep1"), TextBox).Text
        Dim Step2 As String = CType(e.Item.FindControl("txtStep2"), TextBox).Text
        Dim Step3 As String = CType(e.Item.FindControl("txtStep3"), TextBox).Text
        Dim Step4 As String = CType(e.Item.FindControl("txtStep4"), TextBox).Text
        Dim AuditID As String = CType(e.Item.FindControl("ddlAuditor"), DropDownList).SelectedItem.Value
        Dim auditname As String = CType(e.Item.FindControl("ddlAuditor"), DropDownList).SelectedItem.Text

        SQLString = "UPDATE tblTraining SET " & _
                     "trStep1 = '" & Step1 & "', " & _
                     "trStep2 = '" & Step2 & "', " & _
                     "trStep3 = '" & Step3 & "', " & _
                     "trStep4 = '" & Step4 & "', " & _
                     "trAuditor = '" & auditname & "'" & _
                     "WHERE trRcdID = '" & SelectedKeyValue & "'"

        DBConnection = New SqlConnection("data source=eReports;initial catalog=SOP;user id=sa")
        DBConnection.Open()
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?
 
recklezConnect With a Mentor Commented:
If you want the values to remain null make sure there is no default binding and also check the values and construct your SQL statement accordingly.

Dim commaFlag = false
SQLString = "UPDATE tblTraining SET "

If (Step1.Trim() <> '' Or Step1 <> Nothing) Then
   SQLString += "trStep1 = '" + Step1 + "'"
   commaFlag = true
End If

If (Step2.Trim() <> '' Or Step2 <> Nothing) Then
  If (commaFlag) Then SQLString += ", "

   SQLString += "trStep2 = '" + Step2 + "'"
   commaFlag = true
End If

If (Step3.Trim() <> '' Or Step3 <> Nothing) Then
  If (commaFlag) Then SQLString += ", "

   SQLString += "trStep3 = '" + Step3 + "'"
   commaFlag = true
End If

If (Step4.Trim() <> '' Or Step4 <> Nothing) Then
  If (commaFlag) Then SQLString += ", "
   SQLString += "trStep4 = '" + Step4 + "'"
   commaFlag = true
End If

If (commaFlag) Then SQLString += ", "
SQLString += "trAuditor = '" + auditname + "' "

SQLString += "WHERE trRcdID = '" + SelectedKeyValue + "'"

                                         
0
 
callrsCommented:
http://www.thescripts.com/forum/thread498445.html   http://www.dotnetspider.com/qa/Question16775.aspx
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_21609152.html
Just guessing here, based above research:  In the statement:
 "trStep1 = '" & Step1 & "', " & _
Should Step1 be converted from text back to a date? Maybe using Convert function?
0
 
recklezCommented:
That is probably because you are trying to update a datetime field with a value that does not match one of the default date formats SQL Server accepts.
Try to hardcode the values in your update statement and see if SQL Server acceptes that. If it succeeds then you know the data you are trying to insert
is in the wrong format.

Here is a useful link.
http://www.databasejournal.com/features/mssql/article.php/2191631
0
 
JonathanTheManCommented:
I'm not familiar with mySql, but the issue sounds identical to that which you might encounter using SQL Server.

A reliable format for date-time strings that SQL Server will accept without requiring conversion is "yyyy-mm-dd" style (e.g. '2006-06-19'). Might be worth a try with mySql. Otherwise you will need to use the mySql equivalent of the SQL CONVERT function to convert the date string into a sql date.

Hope this helps.
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
Im sorry...I am using SQL Server....I mean my SQLServer..hehe....oops.

I wrote my statements directly from sql server as well.  Some of these date values are a null in the datebase as a smalldatetime format.  My problem is I would like to remain null if nothing is inserted into them when I insert on that row.  For whatever reason they take on a value from 1900 and such.  I looked at the properties on the row and do not understand why they are taking on default values.


Hopefully this explains better.

Thanks.

0
All Courses

From novice to tech pro — start learning today.