Avatar of JohnMac328
JohnMac328
Flag for United States of America asked on

CF -Incorrect syntax near '@P1'.

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>
ColdFusion LanguageMicrosoft SQL Server

Avatar of undefined
Last Comment
JohnMac328

8/22/2022 - Mon
gdemaria

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">'
JohnMac328

ASKER
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
ASKER CERTIFIED SOLUTION
gdemaria

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JohnMac328

ASKER
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">'
Your help has saved me hundreds of hours of internet surfing.
fblack61
JohnMac328

ASKER
I was missing the word VALUES
_agx_

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.
JohnMac328

ASKER
LOL - thanks agx - what got me was that there is nothing out there that explains it when you google the problem
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
_agx_

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? ;-)
JohnMac328

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