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()
      objConn.Close()
running32Asked:
Who is Participating?
 
cubixSoftwareCommented:
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.
0
 
cubixSoftwareCommented:
Hi

Try

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

 
0
 
running32Author Commented:
I get the error Incorrect syntax near '='.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
wtconwayCommented:
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.
0
 
running32Author Commented:
my regional setting are MDY
0
 
cubixSoftwareCommented:
Is that also true of the server where the SQL Server database resides  ?
0
 
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)
      objConn.Open()
      objCmd.ExecuteNonQuery()
      objConn.Close()
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.