?
Solved

Using cfloop for multiple inserts

Posted on 2005-04-06
4
Medium Priority
?
377 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:dh2oing
4 Comments
 
LVL 1

Accepted Solution

by:
intervations-au earned 750 total points
ID: 13722582
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
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 13723288
whats the problem with #3?
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13723780
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
 

Author Comment

by:dh2oing
ID: 13726208
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question