update smalldatetime in database

Posted on 2006-03-28
Last Modified: 2010-04-23
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()
Question by:running32
    LVL 6

    Expert Comment



    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


    Author Comment

    I get the error Incorrect syntax near '='.
    LVL 6

    Accepted Solution

    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.
    LVL 7

    Expert Comment

    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.

    Author Comment

    my regional setting are MDY
    LVL 6

    Expert Comment

    Is that also true of the server where the SQL Server database resides  ?

    Author Comment

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now