Using cfloop for multiple inserts

This should be easy, but I cannot figure it out. I am building a shopping cart and am working on the order entry page. This has three main parts: 1) enter the order/customer information into tblOrder, 2) catch the order id assigned in step 1, and 3) loop through the items in the cart, adding their item_id and the order_id to tblOrder_Item. Steps 1 and 2 are a breeze, 3 I cannot seem to get. Here is my code thus far:

<cftransaction>
      <cftry>
            <cflock timeout="30" name="insertOrder">
                  <!--- Insert Order into DB --->
                  <cfquery name="addOrder" datasource="#application.dsn#"    application.usr#" password="#application.pswd#">
                  
                      this query works, so I will spare you the mess

                  </cfquery>
                  
                  <!--- Get Order ID --->
                  <cfquery name="orderID" datasource="#application.dsn#" username="#application.usr#" password="#application.pswd#">
                        Select @@identity as order_id
                  </cfquery>
                  
                  <!--- Add items to order/item table --->
                        <cfloop collection="#session.cart#" item="i">
                        <cfquery datasource="#application.dsn#" username="#application.usr#" password="#application.pswd#">
                        Insert Into tblOrder_Item
                        (order_id,item_id) Values (#orderID.order_id#,'#session.cart[i][1]#')
                        </cfquery>                              
                        </cfloop>
            </cflock>
            <cfcatch type="any">
                  <cftransaction action="rollback" />
                  <cfset session.orderConfirm = "failed">
                  <cflocation url="confirm.cfm">
            </cfcatch>
            </cftry>
      </cftransaction>

any help would be appreciated. I also tried variations such as <cfquery name="additem#i#"> to no avail.

Thanks,
Josh
dh2oingAsked:
Who is Participating?
 
intervations-auCommented:
Try taking the cflock out of your code to see if that works. I would also suggest take out the cftry and cfcatches as well until you know the inserts are working.
Add back in the cftry & catch then add in one by the the <cfcatch type="any"> elements one by one.

I also noted in your sample above that you are missing the semi-colen from step 3.

your sample:
                    <cfquery datasource="#application.dsn#" username="#application.usr#" password="#application.pswd#">
                    Insert Into tblOrder_Item
                    (order_id,item_id) Values (#orderID.order_id#,'#session.cart[i][1]#')
                    </cfquery>  


Should be:
                    <cfquery datasource="#application.dsn#" username="#application.usr#" password="#application.pswd#">
                    Insert Into tblOrder_Item
                    (order_id,item_id) Values (#orderID.order_id#,'#session.cart[i][1]#');       <<< NOTE SEMI-COLEN AT THE END
                    </cfquery>  

This could be your problem... Or have a look at the cftransaction I have used before for both my site and clients sites in the past.

<cftransaction>

          <!--- Step 1: INSERT ORDER //--->
          <cfquery name="AddOrder" datasource="#Application.DSN#">
                    Insert your order information into your orders table
          </cfquery>

          <!--- Step 2: GET ORDER ID //--->
          <cfquery name="GetOrderID" datasource="#Application.DSN#">
                    SELECT max(id) as OrderID from tblInvoices
          </cfquery>

          <cfset NEWORDERID = "#GetOrderID.OrderID">

            
<!--- Step 3: LOOP THE SESSION VARIABLES //--->
<cfloop collection="#session.Cart#" item="i">
            
     <cfquery name="AddOrderItem" datasource="#Application.DSN#">
          INSERT INTO tblOrderItems
                              ( InvoiceNumber, ClientID, ItemCode, ItemName, ItemDescription, Qty, Cost )
          VALUES ( '#NEWORDERID#',
                              '#ClientID#',
                              '#session.Cart[i][6]#',
                              '#session.Cart[i][2]#',
                              '#session.Cart[i][7]#',
                              '#session.Cart[i][4]#',
                              '#session.Cart[i][3]#' );
     </cfquery>

</cfloop>
      
</cftransaction>
0
 
Jerry_PangCommented:
whats the problem with #3?
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi dh2oing!

It seems this part has a problem :
  <cfloop collection="#session.cart#" item="i">
    <cfquery datasource="#application.dsn#" username="#application.usr#" password="#application.pswd#">
    Insert Into tblOrder_Item
    (order_id,item_id) Values (#orderID.order_id#,'#session.cart[i][1]#')
    </cfquery>                        
  </cfloop>

Kindly give me information about the result for this code : <cfdump var="#session.cart#">

Then, I will be going to revised the part of your insert statement.


Regards!
eNTRANCE2002 :-)
0
 
dh2oingAuthor Commented:
thanks for the help guys, I will be out of my office this morning, but will try the above suggetions this afternoon and will also post the error messages.

Thanks,
Josh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.