We help IT Professionals succeed at work.

update smalldatetime in database

running32
running32 asked
on
Medium Priority
235 Views
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()
      objConn.Close()
Comment
Watch Question

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

 

Author

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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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

Commented:
my regional setting are MDY
Is that also true of the server where the SQL Server database resides  ?

Author

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()
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.