CF -Incorrect syntax near '@P1'.

JohnMac328
JohnMac328 used Ask the Experts™
on
I am using a form with a insert query - I did a form dump and all the values are correct - for some reason I keep getting the Incorrect syntax near '@P1'.  which does not google many answers.

Thanks

SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P1'.  
 
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\Buy_Prize.cfm: line 53
 
51 :         <cfqueryparam value="#form.gift_present15#" cfsqltype="cf_sql_varchar"> ,
52 :         <cfqueryparam value="#form.value15#" cfsqltype="cf_sql_integer"> ,
53 :         <cfqueryparam value="#form.gift_presentcolor#" cfsqltype="cf_sql_varchar">
54 :     )
55 :     </cfquery>


<cfif structKeyExists(FORM, "submitButton15")>
 <cfquery name="entershirts" datasource="IntranetDB_Coldfusion">
 INSERT INTO prize_records (purchaser, award_desc, award_cost, award_color)
 (
        <cfqueryparam value="#form.employeeID#"  cfsqltype="cf_sql_integer"> ,
        <cfqueryparam value="#form.gift_present15#" cfsqltype="cf_sql_varchar"> ,
        <cfqueryparam value="#form.value15#" cfsqltype="cf_sql_integer"> ,
        <cfqueryparam value="#form.gift_presentcolor#" cfsqltype="cf_sql_varchar">
    )
    </cfquery>
    </cfif>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is  @P1  part of a value in one of your form variables?

If your cfqueryparam contains a string value, it has to be enclosed in single quotes, if it a numeric value, then it does not.

You should add single quotes.


 '<cfqueryparam value="#form.gift_presentcolor#" cfsqltype="cf_sql_varchar">'

Author

Commented:
No idea where @P1 is coming from - made the change and it gives

SQLServer JDBC Driver]Invalid parameter binding(s).  
 
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\Buy_Prize.cfm: line 53
 
51 :         <cfqueryparam value="#form.gift_present15#" cfsqltype="cf_sql_varchar"> ,
52 :         <cfqueryparam value="#form.value15#" cfsqltype="cf_sql_integer"> ,
53 :         '<cfqueryparam value="#form.gift_presentcolor#" cfsqltype="cf_sql_varchar">'
 

The fields and data match up
You should check all of the lines, it appears that line 51 in the above is also a varchar that does not have quotes around it.

For integers, you have to be sure that the variable has a numeric value (an empty value is not numeric).  If the variable is empty, you can either use val() around the variable to make it a zero or use the NULL parameter of cfqueryparam to tell it to enter a null value.

But I think the error is caused by my first comment about line 51
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I put the quotes around the varchar types and it is back to parameter binding error

Invalid parameter binding(s).  
 
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\Buy_Prize.cfm: line 53
 
51 :         '<cfqueryparam value="#form.gift_present15#" cfsqltype="cf_sql_varchar">' ,
52 :         <cfqueryparam value="#form.value15#" cfsqltype="cf_sql_integer"> ,
53 :         '<cfqueryparam value="#form.gift_presentcolor#" cfsqltype="cf_sql_varchar">'

Author

Commented:
I was missing the word VALUES
Most Valuable Expert 2015

Commented:
You couldn't figure that out from "Incorrect syntax near '@P1'."? It so completely obvious. I knew it the moment I saw it .. (not) ;-)  That has to be the most cryptic/misleading error message I've seen all day, lol. Glad you figured it out.

Author

Commented:
LOL - thanks agx - what got me was that there is nothing out there that explains it when you google the problem
Most Valuable Expert 2015

Commented:
Yeah, there wouldn't be. Due to the cfqueryparam's the actual values are translated into @P1, @P2,  ... nonsense in jdbc (where the "1" is the first parameter, etc...).

Unfortunately the real error (without the params) isn't much better ;-)  In MS SQL this:

       INSERT INTO someTable ( 1 )

Gives the wonderfully informative error:

        Msg 102, Level 15, State 1, Line 2
        Incorrect syntax near '1'.

Ye-ah, incorrect syntax could be one of a hundred things. Could you narrow it down a little? At least the "near" part is accurate.  You eventually figure those kinds of errors out. But it would be nice to see something a little more direct like "missing VALUES keyword" maybe? ;-)

Author

Commented:
At least now I know the first thing to look for when I see that one :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial