Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Apr 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax error in UPDATE statement

    can any 1 help me with this code:

    rs.Open "update status set current='" & current & "' where id like '" & id &"'", conn


    i am getting the following error but cant figure out why:


    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error in UPDATE statement.

    thanks

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    is the current field a text or memo data type?
    if not then the code in blue is your error
    is the id field a text or memo data type ?
    if not then the code in red is your error
    if neither field is of the text or memo data type then both fields are in error
    rs.Open "update status set current='" & current & "' where id like '" & id &"'" , conn

    also you do not need to use a recordset connection you can use the connection objects execute method and save on server overhead like so

    if incorrect current data type
    conn.Execute("update status set current=" & current & " where id = '" & id & "'")

    if incorrect id data type
    conn.Execute("update status set current='" & current & "' where id = " & id)

    if both data types are incorrect
    conn.Execute("update status set current=" & current & " where id = " & id)

  • #3
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,075
    Thanks
    0
    Thanked 256 Times in 252 Posts
    Are you using Access as database?
    I don't know if you are using any reserved keyword but try enclosing all the field names in square brackets.

    Code:
    rs.Open "update [status] set [current]='" & current & "' where [id] like '" & id &"'", conn
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #4
    New Coder
    Join Date
    Jan 2005
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if your your fields is numeric than remove single quote and check it.

  • #5
    New Coder
    Join Date
    Apr 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    rs.Open "update status set current='" & current & "' where id like '" & id &"'", conn


    current is a text field & id is an autonumber...

    i tried the above code but still the same error.

  • #6
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,075
    Thanks
    0
    Thanked 256 Times in 252 Posts
    Try this:
    Code:
    rs.Open "update [status] set [current]='" & current & "' where [id]=" & id, conn
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #7
    New Coder
    Join Date
    Apr 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks buddy this one is working...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •