?
Solved

Syntax error converting character string to smalldatetime data type.

Posted on 2006-06-19
5
Medium Priority
?
559 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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

770 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