• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

update smalldatetime in database

I need to update a sql database where the field is a smalldate and time.  I have converted the values from the text boxes to date and txttimeout to cdate but I keep getting the error Incorrect syntax near '4'.   The string is 3/27/2006 4:15:00 PM.   I am guessing it does not like the space.  How can I change the sql query to work with this.  Thanks

Dim strtimeout As string
      Dim strtimein As string
      Dim strdatein As string
      strdatein = txtdatein.text
      strtimein=cdate(strdatein & " " & txttimein.text)
      strtimeout= cdate(strdatein & " " & txttimeout.text)
      objCmd.Connection = objConn
                objCmd.CommandText = "UPDATE Employeehours SET timeout = " & strtimeout  & " where recordid = " & id      
                objRdr = objCmd.ExecuteReader()
  • 3
  • 3
1 Solution


objCmd.CommandText = "UPDATE Employeehours SET timeout = '" & strtimeout  & "' where recordid = " & id  

I think you need to enclose the date in single quotes like a character value

running32Author Commented:
I get the error Incorrect syntax near '='.
mmmm.. it works for me so....

You definetly need the single quotes so keep them.

Can you go into SQL Query Analyzer and type your SQL statement and this should generate the same error.

Is the date and time settings on your client the same as the server ? Mine are set as UK (so DMY) but if I enter a date as MDY then I get your syntax error. If I enter your testdata as '27/3/2006 4:15:00 PM' then it works.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

If your regional settings are YMD then try this:

Dim timeout as DateTime = CType(strtimeout,DateTime)

"UPDATE Employeehours SET timeout='" & timeout.ToString("yyyy/MM/dd hh:mm:ss") & "' where recordid=" & id

That way you can force the output of the date/time value.
running32Author Commented:
my regional setting are MDY
Is that also true of the server where the SQL Server database resides  ?
running32Author Commented:
I tried something different and it worked.  Thanks for our help

Dim strtimeout As String
      Dim strtimein As String      
      intrecordid = txtid.text
      response.write (intrecordid)
      strtimeout = txtdatein.text & " " &  txttimeout.text
      strtimein = txtdatein.text & " " &  txttimein.text
      'strtimein = ctype(strtimein)
      response.write (strtimeout)
      strCmd = "UPDATE Employeehours SET timeout=@timeout, timein=@timein WHERE recordID=@recordID"
      objCmd = New SqlCommand(strCmd, objConn)
      objCmd.Parameters.Add("@timeout", strtimeout)
      objCmd.Parameters.Add("@timein", strtimein)
      objCmd.Parameters.Add("@recordid", intrecordid)

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now