Solved

Syntax error converting character string to smalldatetime data type.

Posted on 2006-06-19
5
546 Views
Last Modified: 2013-12-03
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()
0
Comment
Question by:Robb Hill
5 Comments
 
LVL 30

Expert Comment

by:callrs
Comment Utility
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
 
LVL 4

Expert Comment

by:recklez
Comment Utility
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
 
LVL 1

Expert Comment

by:JonathanTheMan
Comment Utility
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
 
LVL 11

Author Comment

by:Robb Hill
Comment Utility
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
 
LVL 4

Accepted Solution

by:
recklez earned 500 total points
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Uploading files to the web server has become common part of almost any kind of web application. People use different technologies to solve this, but regardless of the technology used, it is always useful to have some kind of progress indicator shown…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now