Skip to main content
G
GwenH
Inspiring
October 25, 2008
Answered

Syntax Error in Update Statement

  • October 25, 2008
  • 6 replies
  • 1514 views
Would a smart and kind CF pro mind putting a fresh pair of eyes on this code and tell me where the syntax error is? All the fields in the statement are numeric except the last one (comments). I have enclosed them in val() to ensure they are numeric when inserted into the DB. The fields they're being inserted into are numeric. I need to be numeric because I will be doing calculations on them. Also, I have triple-checked to ensure the datasource, table, and field names all match.

Thanks,
GwenH
    This topic has been closed for replies.
    Correct answer -__cfSearching__-
    > Check for reserved keywords.

    There may be more than one. This list is a good start, but it may not include all words
    http://support.microsoft.com/kb/286335

    6 replies

    D
    Dan_Bracuk
    Inspiring
    October 25, 2008
    Also check for empty fields.

    To troubleshoot, comment out all the lines starting with a comma and run the query again. Assuming it works, uncomment them one by one until it crashes.

    You shouldn't need the val function.
    -
    -__cfSearching__-Correct answer
    Inspiring
    October 25, 2008
    > Check for reserved keywords.

    There may be more than one. This list is a good start, but it may not include all words
    http://support.microsoft.com/kb/286335
    G
    GwenHAuthor
    Inspiring
    October 25, 2008
    Thanks so much! Removing the reserved words did the trick. Boy what a pain!

    GwenH
    -
    -__cfSearching__-
    Inspiring
    October 25, 2008
    > Does anyone have any fresh ideas?

    Check for reserved keywords. If you are using Access "TOP" is definitely one of them. Using a reserved keyword causes errors because the database does not recognize the keyword as an object name.

    Rename the column if possible, or use square brackets to escape it:

    [Top] = #val(form.top)#, ...

    > WHERE resumeID = #form.resumeID#

    You should use cfqueryparam on all query parameters.

    G
    GwenHAuthor
    Inspiring
    October 25, 2008
    I'm sorry, I should have mentioned that I was getting the syntax error ***before*** I added the val() to each set statement. So, please ignore the val(), and look for other syntax errors. Thanks!

    GwenH
    A
    Anonymous
    October 25, 2008
    Could you provide the exact error message you're getting. Cut and paste it in (not the stack trace though). Just the main error.

    First possibilities would be:

    1 - Give your query a name. e.g. name="qryUpdate"
    2 - Change the inserted data from val(#form.strengths#) to val(form.strengths).
    3 - Do what the poster before me mention - use CFQUERYPARAM. I would recommend ALWAYS using this when you can. It stops stuff like SQL injections and solves some issues with quote marks etc.

    Hope this helps.
    F
    fober1
    Inspiring
    October 25, 2008
    Hi,
    the val should be inside the "#"

    cheers,
    fober



    <cfquery datasource="reviews">
    UPDATE evals
    SET
    focus = #val(form.focus)#
    , strengths = #val(form.strengths)#
    ...

    G
    GwenHAuthor
    Inspiring
    October 25, 2008
    More information: I contacted my web hosting company and had them temporarily enable robust error handling, to see if I could get more information on the error. (They have this feature turned off for customers on shared hosting servers - a huge pain in the butt!). Here is the more specific error message.

    ==========================================
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

    The error occurred in C:\Inetpub\vhosts\resumesforless.com\httpdocs\aa\frraction.cfm: line 26
    24 : , none = #val(form.none)#
    25 : , comments = '#form.comments#'
    26 : WHERE resumeID EQ #form.resumeID#
    27 : </cfquery>
    ===========================================

    It appears that the error is in the WHERE portion of the query. The resumeID column in the database table is formatted as a long integer, general number, no decimal places. The form.resumeID value is a hidden field on a form, wrapped in val() like this: value="#val(resumeiD)#. I am trying to ensure that the resumeID is always viewed as a number by the query. I also tried changing WHERE resumeID = to WHERE resumeID EQ. Same error message.

    I am attaching the latest version of my code to this message. It hasn't changed much; I just moved val() inside the # #. Does anyone have any fresh ideas?

    Many thanks,
    GwenH
    N
    Newsgroup_User
    Inspiring
    October 25, 2008
    Try using <cfqueryparam>

    <cfquery datasource="reviews">
    UPDATE evals
    SET focus = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.focus#">
    , strengths = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.strengths#">
    , tailored = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.tailored#">
    , badinfo = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.badinfo#">
    , format = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.format#">
    , visual = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.visual#">
    , grammar = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.grammar#">
    , pronouns = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.pronouns#">
    , written = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.written#">
    , achieve = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.achieve#">
    , sell = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.sell#">
    , negative = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.negative#">
    , top = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.top#">
    , general = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.general#">
    , intro = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.intro#">
    , orientation = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.orientation#">
    , paragraphs = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.paragraphs#">
    , two = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.two#">
    , length = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.length#">
    , none = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.none#">
    , comments = <cfqueryparam cfsqltype="cf_sql_clob" value="#form.comments#">
    WHERE resumeID = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#form.resumeID#">
    </cfquery>


    --
    Ken Ford
    Adobe Community Expert Dreamweaver/ColdFusion
    Adobe Certified Expert - Dreamweaver CS3
    Adobe Certified Expert - ColdFusion 8
    Fordwebs, LLC
    http://www.fordwebs.com


    "GwenH" <webforumsuser@macromedia.com> wrote in message
    news:gdtvto$i8v$1@forums.macromedia.com...
    > Would a smart and kind CF pro mind putting a fresh pair of eyes on this
    > code
    > and tell me where the syntax error is? All the fields in the statement are
    > numeric except the last one (comments). I have enclosed them in val() to
    > ensure
    > they are numeric when inserted into the DB. The fields they're being
    > inserted
    > into are numeric. I need to be numeric because I will be doing
    > calculations on
    > them. Also, I have triple-checked to ensure the datasource, table, and
    > field
    > names all match.
    >
    > Thanks,
    > GwenH
    >
    > <cfquery datasource="reviews">
    > UPDATE evals
    > SET
    > focus = val(#form.focus#)
    > , strengths = val(#form.strengths#)
    > , tailored = val(#form.tailored#)
    > , badinfo = val(#form.badinfo#)
    > , format = val(#form.format#)
    > , visual = val(#form.visual#)
    > , grammar = val(#form.grammar#)
    > , pronouns = val(#form.pronouns#)
    > , written = val(#form.written#)
    > , achieve = val(#form.achieve#)
    > , sell = val(#form.sell#)
    > , negative = val(#form.negative#)
    > , top = val(#form.top#)
    > , general = val(#form.general#)
    > , intro = val(#form.intro#)
    > , orientation = val(#form.orientation#)
    > , paragraphs = val(#form.paragraphs#)
    > , two = val(#form.two#)
    > , length = val(#form.length#)
    > , none = val(#form.none#)
    > , comments = '#form.comments#'
    > WHERE resumeID = #form.resumeID#
    > </cfquery>
    >

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices