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>

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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

_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 :)