Skip to main content
U
USN-WD
Known Participant
February 17, 2010
Question

Frustrating syntax error:cfqueryparam

  • February 17, 2010
  • 1 reply
  • 1093 views

I must be brain dead. I have been looking at this code for about 45 minutes trying to figure out where the syntax error is. I think it is in one of the "," but I have rearranged them so many times I am going bonkers. Can someone find where my error is.

<cfquery name="addRegistration" datasource="RBDS">

INSERT INTO

     tournamentReg

        (tDate

        ,tName

        ,regDateTime

        ,regNumber

        ,regFName

        ,regLName

        ,streetAddr

        ,cityStateZip

        ,regPhone

        ,regEmail

        ,lastBook

        ,usbcCard

        ,regNotify

        ,regReturn

        ,regMembership

        ,regLeague

        ,

        <cfif tName EQ #URL.tName# AND 'doubles' IS 'yes'>

        BowlerTwoName, BowlerTwoBook

        </cfif>

        <cfif tName EQ #URL.tname# AND 'threePerTeam' is 'yes'>

        BowlerTwoName, BowlerTwoBook,

        BowlerThreeName, BowlerThreeBook

        </cfif>

        <cfif tName EQ #URL.tname# AND 'fourPerTeam' IS 'yes'>

        BowlerTwoName, BowlerTwoBook,

        BowlerThreeName, BowlerThreeBook,    

        BowlerFourName, BowlerFourBook

        </cfif>

        <cfif tName EQ #URL.tname# AND 'fivePerTeam' IS 'yes'>

        BowlerTwoName, BowlerTwoBook,

        BowlerThreeName, BowlerThreeBook,    

        BowlerFourName, BowlerFourBook,

        BowlerFiveName, BowlerFiveBook

        </cfif>)

    VALUES

        VALUES

        (

        <cfif tName EQ #URL.tname# AND 'doubles' IS 'yes'>

                 <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerTwoName#">

                ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerTwoBook#">

            </cfif>

            <cfif tName EQ #URL.tname# AND 'threePerTeam' is 'yes'>

               <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerTwoName#">

                ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerTwoBook#">

                ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerThreeName#">

                ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerThreeBook#">

            </cfif>

            <cfif tName EQ #URL.tname# AND 'fourPerTeam' IS 'yes'>

               <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerTwoName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerTwoBook#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerThreeName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerThreeBook#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerFourName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerFourBook#">

            </cfif>

            <cfif tName EQ #URL.tname# AND 'fivePerTeam' IS 'yes'>

               <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerTwoName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerTwoBook#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerThreeName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerThreeBook#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerFourName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerFourBook#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerFiveName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.BowlerFiveBook#">

            </cfif>

           ,<cfqueryparam cfsqltype="CF_SQL_DATE" value="#URL.tdate#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.tname#">

               ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.regDateTime#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.regNumber#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regFName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regLName#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.streetAddr#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.cityStateZip#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regPhone#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regEmail#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.lastBook#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.usbcCard#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regNotify#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regReturn#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regMembership#">

               ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regLeague#">

        )

</cfquery>

Error Dump states the error is on line 151 (above). Until I added the <cfif>'s it worked fine. BTW- the <cfif>'s work find until it processes the VALUE.


Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
The error occurred in C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 151
Called from C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 29
Called from C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 28
Called from C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 1
Called from C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 151
Called from C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 29
Called from C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 28
Called from C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 1
149 :                 <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regReturn#">,
150 :                 <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regMembership#">,
151 :                 <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regLeague#">
152 :                
153 :         )

    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    February 17, 2010

    First, refer to your other thread where I alluded to commenting things out and uncommenting them one by one until it crashes.

    Next, in an effort to keep things as simple as possible, I tend to avoid if/else logic inside a query.  I do it in advance and use the resulting variables in the query.  This enables you to examine the variables before the query runs and ensure they match your expectations.  Something like this.

    if (something) {

    TheseFields = "f1,f2";

    TheseValues = something;

    }

    else  {

    TheseFields = "f3,f4";

    TheseValues = something;

    }

    Then, in your query, you can do

    insert into sometable

    (#TheseFields#)

    values

    (#TheseValues#)

    The values part depends on the version of coldfusion you are running.  I use version 7 and can't put a queryparam into the TheseValues variable. I understand with later versions that is not the case.

    U
    USN-WDAuthor
    Known Participant
    February 17, 2010

    Hey Dan. Thanks for the tips yesterday. I went back to the version of the code when it worked. The working code is at the top. The non-working code is at the bottom. It starts not working when I include the CFIF's. I am not sure I understand your other suggestion. But maybe if you take a look at the difference between the working code and the other you can see what I don't.

    Working CODE:

    <cfquery name="addRegistration" datasource="RBDS">

    INSERT INTO

         tournamentReg

            (tDate

            ,tName

            ,regDateTime

            ,regNumber

            ,regFName

            ,regLName

            ,streetAddr

            ,cityStateZip

            ,regPhone

            ,regEmail

            ,lastBook

            ,usbcCard

            ,regNotify

            ,regReturn

       )

        VALUES

            (

                      <cfqueryparam cfsqltype="CF_SQL_DATE" value="#URL.tdate#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.tname#">

                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.regDateTime#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.regNumber#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regFName#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regLName#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.streetAddr#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.cityStateZip#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regPhone#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regEmail#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.lastBook#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.usbcCard#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regNotify#">

                    ,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regReturn#">

             )

    </cfquery>

    PRODUCES THIS:

    CONGRATULATIONS YOU ARE NOW REGISTERED FOR THE

    SUPER NO-TAP

    WHO TO CONTACT: Rick Rossiter  -  TELEPHONE: 775-813-5492

    The table below contains all of the information you entered for the Super No-Tap tournament.


    All the information you entered to register for this tournament is displayed bwlow. Reno Bowls wishes you the best of luck in this tournament. If there is any information below that is incorrect, please inform us as soon as possible by calling Reno Bowls or sending us an email to: registration@renobowls.com. Please make certain to include your registration number (8701967) when making any inquiries.

    YOUR REGISTRATION NUMBER

    		8701967

    TIME AND DATE YOU REGISTERED

    		02/17/10 - 1:02:09 PM

    DATE OF TOURNAMENT

    		2009-12-13 00:00:00.0

    WHERE THE TOURNAMENT WILL BE

    		High Sierra Bowling Lanes
    3390 South Virginia Street
    Reno, NV, 89502-4502
    (775) 825-1048

    DIRECTONS

    TIME TOURNAMENT BEGINS

    		12:00 PM

    YOU SAVED $5.00 BY REGISTERING ONLINE

    		$25

    CAPTAIN'S NAME (FIRST REGISTRANT)

    		Rick Rossiter

    YOUR MAILING ADDRESS:

    		105 N. McCarran Blvd.
    Reno, NV 89523

    YOUR EMAIL ADDRESS IS:

    		rick@renobowls.com

    YOUR PHONE NUMBER IS:

    		813-0850

    YOUR LAST YEARS BOOK AVERAGE IS:

    		191

    YOUR USBC MEMBER NUMBER IS:

    		8130-5041

    NOTIFY YOU OF FUTURE TOURNAMENTS

    		Yes

    RETURNING RENO BOWLS PLAYER

    		welcome back and thank you

    The NON-WORKING CODE: Even though I removed the CFIF structure. However, I have to be able to discern the number of bowlers according to the tournament the registrant is entering.

    <cfquery name="addRegistration" datasource="RBDS">

    INSERT INTO

    tournamentReg

            (tDate

            ,tName

            ,regDateTime

            ,regNumber

            ,regFName

            ,regLName

            ,streetAddr

            ,cityStateZip

            ,regPhone

            ,regEmail

            ,lastBook

            ,usbcCard

            ,regNotify

            ,regReturn

            ,regMembership

            ,regLeague

    VALUES

            (

                   <cfqueryparam cfsqltype="CF_SQL_DATE" value="#URL.tdate#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.tname#">,

                   <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.regDateTime#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.regNumber#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regFName#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regLName#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.streetAddr#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.cityStateZip#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regPhone#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regEmail#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.lastBook#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.usbcCard#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regNotify#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regReturn#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regMembership#">,

                   <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regLeague#">

            )

    </cfquery>

    According to the dump the "," syntax is incorrect, but it looks fine to me. What am I missing?

    Error Occurred While Processing Request

    Error Executing Database Query.

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
    The error occurred in C:\Inetpub\wwwroot\renobowls\forms\ProcessRegistration.cfm: line 168
    166 :                     <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regReturn#">,
167 :                     <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regMembership#">,
168 :                     <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.regLeague#">
169 :                
170 :         )
    SQL   INSERT INTO tournamentReg (tDate ,tName ,regDateTime ,regNumber ,regFName ,regLName ,streetAddr ,cityStateZip ,regPhone ,regEmail ,lastBook ,usbcCard ,regNotify ,regReturn ,regMembership ,regLeague VALUES ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) , (param 10) , (param 11) , (param 12) , (param 13) , (param 14) , (param 15) , (param 16) )
    DATASOURCE  RBDS
    VENDORERRORCODE  -3502
    SQLSTATE  42000
    D
    Dan_Bracuk
    Inspiring
    February 17, 2010

    Large amounts of code and sql are too difficult for a simple soul like me to comprehend.  I suggest the following simplifications.

    For the top part of your query that is always the same, comment out every field and value except the first one.  You need the first one to ensure your commas work.

    For the if / else part, start with a simple if/else.  It could be as simple as

    insert into your table ( field1

    <cfif true>

    , field2

    <cfelse>

    , field3

    </cfif>

    values ( value1

    <cfif true>

    , value2

    <cfelse>

    , value3

    </cfif>

    )

    Then start adding your other conditions, one at a time, until it crashes.  Then you'll know what caused the problem.

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