Highlighted

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

New Here ,
Jun 04, 2015

Copy link to clipboard

Copied

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

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.



Views

234

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

New Here ,
Jun 04, 2015

Copy link to clipboard

Copied

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

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.



Views

235

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 04, 2015 0
Advocate ,
Jun 04, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 04, 2015 0
Engaged ,
Jun 04, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 04, 2015 1
New Here ,
Jun 04, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 04, 2015 0
Engaged ,
Jun 04, 2015

Copy link to clipboard

Copied

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.



Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 04, 2015 0
New Here ,
Jun 04, 2015

Copy link to clipboard

Copied

Works perfectly, thanks.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 04, 2015 0