5 Replies Latest reply on Jun 4, 2015 10:09 AM by rickclark54

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

    rickclark54 Level 1

      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