#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    4
    Rep Power
    0

    Update MySql database


    I get the following error using UPDATE. I can't figure out the syntax error.
    Any help would be appreciated.


    Error Message
    -----------------------------------------------------------
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [MySQL][ODBC 3.51 Driver][mysqld-5.0.91-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''20''' at line 1

    zeo_Editactivity_status_asp, line 43
    -----------------------------------------------------------


    Where I think the problem is

    -----------------------------------------------------------
    [CODE]
    30 Dim data_source, Connection, sql_insert
    31
    32 data_source = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=planning.readyhostingmysql.com; PORT=3306;" &_
    33 "DATABASE=zeo; USER=xxxxxxxxx; PASSWORD=xxxxxxx; OPTION=3;"
    34
    35
    36 sql_insert = "UPDATE ZEO_Incidents SET str_OtherPersonsPresent = '" & str_OtherPersonsPresent & "', str_ConditionsNoted = '" & str_ConditionsNoted & "', str_Comments = '" & str_Comments & "' WHERE ID = '" & ID & "' "
    37
    38
    39
    40 ' Creating Connection Object and opening the database
    41 Set Connection = Server.CreateObject("ADODB.Connection")
    42 Connection.Open data_source
    43 Connection.Execute sql_insert
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,692
    Rep Power
    1958
    is ID a number or text? If it is a number you should not use ' ' around the value.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by MrFujin
    is ID a number or text? If it is a number you should not use ' ' around the value.

    Thanks for the quick reply.

    ID is a number (INT with auto increment that acts as the primary key)

    I removed the ' ' around the value but is didn't work.

    36 sql_insert = "UPDATE ZEO_Incidents SET str_OtherPersonsPresent = '" & str_OtherPersonsPresent & "', str_ConditionsNoted = '" & str_ConditionsNoted & "', str_Comments = '" & str_Comments & "' WHERE ID = & ID & "

    I also tried the following, but it didn't work.

    36 sql_insert = "UPDATE ZEO_Incidents SET str_OtherPersonsPresent = '" & str_OtherPersonsPresent & "', str_ConditionsNoted = '" & str_ConditionsNoted & "', str_Comments = '" & str_Comments & "' WHERE ID = " + ID


    Any ideas?
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,692
    Rep Power
    1958
    Can you try to print out sql_insert with data inserted (which is being send to the database)?

    How is the table created?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by MrFujin
    Can you try to print out sql_insert with data inserted (which is being send to the database)?

    How is the table created?
    Here is the following that results.

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [MySQL][ODBC 3.51 Driver][mysqld-5.0.91-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1

    /ZEO_database/zeo_Editactivity_status.asp, line 44

    Using the following syntax:

    sql_insert = "UPDATE ZEO_Incidents SET str_OtherPersonsPresent = '" & str_OtherPersonsPresent & "', str_ConditionsNoted = '" & str_ConditionsNoted & "', str_Comments = '" & str_Comments & "' WHERE ID=" + ID


    The code works if I manually enter the row I'd like to update. For example:

    sql_insert = "UPDATE ZEO_Incidents SET str_OtherPersonsPresent = '" & str_OtherPersonsPresent & "', str_ConditionsNoted = '" & str_ConditionsNoted & "', str_Comments = '" & str_Comments & "' WHERE ID= '21'"
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    4
    Rep Power
    0
    I figured it out. The following works now.

    sql_insert = "UPDATE ZEO_Incidents SET str_OtherPersonsPresent = '" & str_OtherPersonsPresent & "', str_ConditionsNoted = '" & str_ConditionsNoted & "', str_Comments = '" & str_Comments & "' WHERE ID=" + ID


    The previous page was missing a ' from the hidden field.

    Response.write "<input type='hidden' name='ID' value="
    Response.write ID
    Response.write "'>"

    Should have been:
    Response.write "<input type='hidden' name='ID' value='"
    Response.write ID
    Response.write "'>"


    Thanks for the help

IMN logo majestic logo threadwatch logo seochat tools logo