Solved

Syntax error converting character string to smalldatetime data type.

Posted on 2006-06-19
5
558 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 30

Expert Comment

by:callrs
ID: 16937221
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
ID: 16937288
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
ID: 16937917
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
ID: 16960341
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
ID: 16961558
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

696 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