Why am I getting an SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

[rickclark54]

I have an order form where my buyers can add items to my form. I am using a loop that adds an extra line when they add a new product. I am storing the info in two different mySQL tables. The first is alll of the personal info, the second is the items. THe personal info saves correctly. However, I get the following error when saving the items:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1, 'wer 2345', 1, 1 )' at line 9

The error occurred in E:\webs\clarkwebhosting.com\SSL\esrc\order_add.cfm Line 74

++ After storing the personal info I grab the order ID to populate the items table

<cflock timeout="5">

<cfquery name="qGetorderID" datasource="#application.dsn#">

select max(orderID) as oid from orders

</cfquery>

</cflock>

<cfoutput><cfset getoid = #qGetorderID.oid#></cfoutput>

++ this is the loop I am using to populate the items table ++

<cfloop from="1" to="#form.numba#" index="idx">

    <cfset getqty = evaluate("form.qty" & idx)>

    <cfset getitem = evaluate("form.item" & idx)>

    <cfset getunit = evaluate("form.unit" & idx)>

    <cfset gettotal = evaluate("form.total" & idx)>

<cfquery name="insertItems" datasource="#application.dsn#">

INSERT into items

                (orderID,

                qty,

                item,

                unit,

                total

            )

VALUES(

<cfqueryparam value="#getoid#" CFSQLType="CF_SQL_INTEGER" >

<cfqueryparam value="#getqty#" CFSQLType="CF_SQL_INTEGER" >,

<cfqueryparam value="#getitem#" CFSQLType="CF_SQL_VARCHAR" >,

<cfqueryparam value="#getunit#" CFSQLType="CF_SQL_INTEGER" >,

<cfqueryparam value="#gettotal#" CFSQLType="CF_SQL_INTEGER" >

)

</cfquery>

</cfloop>   

This line: '1, 'wer 2345', 1, 1 is the first line item from the order form. Any ideas as to why this isn't working? I am using CF9 and mySQL 5.5.9-log

[haxtbh]

You are missing a , (comma) after <cfqueryparam value="#getoid#" CFSQLType="CF_SQL_INTEGER" >. Is this a typo or is it missing in your code?

[Dave Ferguson]

As stated, you are missing a comma after one of the params.  That may be causing the error you are receiving.

Also, there are some fundamental issues with the code you posted beyond the error you are receiving.  I only point these out to help so please don't take as criticism.

First, you get an orderid at the top of the code.  The lock is pointless in that scenario and will just add unnecessary overhead.  How do you know that is the correct id?  Just grabbing the max can result in getting the wrong id. It is highly probable that the id returned could be the wrong id. There should be something in place to make sure you are getting the id for the order for this user/request. 

Next, this code:

<cfoutput><cfset getoid = #qGetorderID.oid#></cfoutput>

You don't need to put cfoutput around a set statement.  Also, unless you are concatenating in quotes or outputting you don't need to put pound signs around vars.  You should change it to...

<cfset getoid = qGetorderID.oid>

Try to avoid using evaluate.  It is slow and generally not necessary.  You could rewrite those set statements as...

<cfset getqty = form[qty & idx]>

Thanks,

--Dave

[rickclark54]

Thanks, no problem on the suggestions. I am always trying to improve my skills. I added the changes you suggested,unfortunately I am now receiving the following error:

Variable QTY is undefined.

The error occurred in E:\webs\clarkwebhosting.com\SSL\esrc\order_add.cfm  Line 56, which is: <cfset getqty = form[qty & idx]>

Also, I am using the evaluate  statement in my input: <cfinput type="text" name="qty#idx#" value="#evaluate("form.qty#idx#")#" size="25%" class="right">

I tried using value="#form[qty#idx#]#" but I get a different error.

[Dave Ferguson · Accepted solution]

My apologies... that should have been (note the quotes)...

<cfset getqty = form["qty" & idx]>

Also, this would be...

<cfinput type="text" name="qty#idx#" value="#form["qty" & idx]#" size="25%" class="right">

On another note... You should probably avoid using cfinput and just use standard form tags.

[rickclark54]

Works perfectly, thanks.