Hi,
Here is the scenareo as well as I, a nubi can 'splain' it.
Three pages:
1. Order Form page. Collects the data and use input. Very complicated, but works and does its stuff well.
2. Processing page.
--a. checks for errors, returns errors - all ok
--b. If no errors, spits out the 'order' line by line accurately and
--c. Saves the line by line information in an array - the actual SQL insert statement
--d. Only allows user to Confirm Order or Cancel. If cancel, well you know. If Confirm then it goes to Third page
3. Thank you page.
--a. Says thanks.
--b. Insert the line items into the DB
--c. Emails Client with order information
--d. Done button - closes window.
Here is the problem and I have only seen it written about once.
When the THIRD page processes it the SQL fails because CF(7) puts in extra quotes in the SQL.
Because I loop thru and pop a query? Yes. I have discovered the problem.
I need the fix!
Here is building the array: Works fine. I can reitterate the all the arrays and visually check that the correct information is being presented in the ThankYou page...
<cfset insert_sql="
(
ORDER_ID,
INSERTED_DATE,
ITEM_ID,
LINE_NUMBER,
QUANTITY_UPDATED,
PERCENT_FEMALE,
SHIP_TO_REP,
BILL_TO_REP
#form_rows_caps#
)
VALUES (
#FORM.ORDER_ID#,
SYSDATE,
'#Form[CountVar & '_item_id']#',
#Form[CountVar & '_line_number']#,
#line_qty#,
#female_percent#,
#FORM.SHIP_TO_REP#,
#FORM.BILL_TO_REP#
#form_vars_caps#
)
">
Here is the loop: From the Thank you page. The looping works fine.
<CFLOOP FROM="1" TO="#form.product_count#" index="i">
<cfquery name="InsertRequest" datasource="my-datasource" >
INSERT INTO my.order_table
#Form["INSERT_LINES_" & i]#
</cfquery>
</CFLOOP>
Inside that cfquery CF add quotes to the quotes and produces incorect SQL insert statements which causes the DB to give out the error, missing comas...
Help? Write a UDF that stops this from happening?
Thanks.
Scan.