Solved

Syntax error converting character string to smalldatetime data type.

Posted on 2006-06-19
5
556 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
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SharePoint 2013 Searchbox Branding 11 117
paypal ipn url 5 79
Sources to create infographic resume 2 20
PHP Curl to output a url 7 48
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to count occurrences of each item in an array.

828 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