SOL/ColdFusion: INSERTS with multiple foreign keys from session

Question

Apologies for this long winded question, I'd thought I'd cover everything I've tried!

I have a cart application, that stores the items selected in an array, these items are then passed on to the OrderItems table to store in database. The entries in the orderItems table can then be output as an itemized order page for the customer, if they want to view past orders for example.

I'd like to loop through the session, and determine the session.itemid belongs to which product table, and based on that, insert into the OrderItems table the ID that belongs to the correct PK column name.

For example CFDUMP of session.cart:

enter image description here

Each of the above items belongs to a separate table.

OrderItems Table

itemID  int 
orderID nvarchar(10)    
ticketperformanceID int 
ticketparkingID int 
accommCategoryID int    
itemCost decimal(6, 2)

Current code that works partially this executes okay if only one item is in the cart, however it stores the itemid in all 3 columns, rather than the one that matches the ID Column name. Furthermore if I have more than one item of a diffrent ID, then it returns an error. Please see error code after code, below.

 <cfquery name="addOrder" datasource="sql1007539" result="insert">
    INSERT INTO orders 
           (customerID
          ,orderDate
          ,orderValue)

    values ('#cust.customerID#', '#DateFormat(Now())#','#variables.totalprice#')

    </cfquery>
    <cfquery name="orderItems" datasource="sql1007539">
    insert into orderItems (
          orderID,
          ticketperformanceID
          ,ticketparkingID
          ,accommCategoryID
          ,itemCost
    )

    values('#insert.GENERATEDKEY#', '<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>','<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>','<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>','<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].price#<cfelse>#session.cart[i].price#, </cfif></cfloop></cfoutput>')</cfquery>
    </cftransaction>

    <cfoutput> Your order has been committed</cfoutput>

Error if more than one item of a different id, is in session.cart:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting the varchar value '1, 2, 101' to data type int.

The error occurred in C:/ColdFusion10/cfusion/wwwroot/ce0932a/coursework/process.cfm: line 28

26 : )
27 : 
28 : values('#insert.GENERATEDKEY#', '<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>','<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>','<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>','<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].price#<cfelse>#session.cart[i].price#, </cfif></cfloop></cfoutput>')</cfquery>
29 : </cftransaction>
30 : 

VENDORERRORCODE       245
SQLSTATE      22018
SQL        insert into orderItems ( orderID, ticketperformanceID ,ticketparkingID ,accommCategoryID ,itemCost ) values('81', '1, 2, 101','1, 2, 101','1, 2, 101','75.00, 20.00, 10.00')
DATASOURCE    sql1007539
Resources:

Tried to play with the following which works if there is more than one item , however it populates all three ID columns with the same ID, also it only stores 1 entry of an item rather than the quantity, ie. I have a quantity of two, so I'd like to store the two items as separate records:

<cfloop from="1" to="#ArrayLen(SESSION.cart)#" index="i">

 <cfquery name="orderItems" datasource="sql1007539">
insert into orderItems (
orderID,
ticketperformanceID
      ,ticketparkingID
      ,accommCategoryID
      ,itemCost
)

values('#insert.GENERATEDKEY#','#session.cart[i].itemid#', '#session.cart[i].itemid#', '#session.cart[i].itemid#', '#session.cart[i].price#'
)
</cfquery>
</cfloop>

<cfoutput> Your order has been committed</cfoutput>

enter image description here

Code, which generates an error:

  <cfloop from="1" to="#ArrayLen(SESSION.cart)#" index="i">
    <cfquery datasource="sql1007539">
      INSERT INTO orderItems(  
      orderID
      ,ticketperformanceID
         ,ticketparkingID
      ,accommCategoryID
      ,itemCost

      where ticketperformanceID='#SESSION.cart[i].itemid#' and ticketparkingID='#SESSION.cart[i].itemid#' and accommCategoryID='#SESSION.cart[i].itemid#';)

      VALUES('#insert.GENERATEDKEY#', '#SESSION.cart[i].itemid#', '#SESSION.cart[i].price#')
      </cfquery>
  </cfloop>

Error:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'where'.

The error occurred in C:/ColdFusion10/cfusion/wwwroot/ce0932a/coursework/process2.cfm: line 29

27 : where ticketperformanceID='#SESSION.cart[i].itemid#' and ticketparkingID='#SESSION.cart[i].itemid#' and accommCategoryID='#SESSION.cart[i].itemid#';) 28 :
29 : VALUES('#insert.GENERATEDKEY#', '#SESSION.cart[i].itemid#', '#SESSION.cart[i].price#') 30 : 31 :

I'm at my wits end with this, and don't know where to go from here...

Using Coldfusion 10 with SQL 2008 R2

Honestly, the biggest problem I see is your table structure. When you have multiple columns storing the same type of object, it is usually a sign you need to [normalize](http://msdn.microsoft.com/en-us/library/ms191178%28v=SQL.105%29.aspx). For example, store each item ordered in a separate record ie `orderID, itemID, itemCost`. The "item" table itself should contain the "type" of item (parking, performance, etcetera). — Leigh, Jun 05 '13 at 14:56
Thanks Leigh,I should have mentioned this in the question. I have two tables, one is Orders table, this stores the ( OrderID, CustomerID, Date, ValueofOrder). Okay, I'll give this a go. — Claudia Sasi, Jun 05 '13 at 15:04
Works like a charm! I can't believe I did not think of that, now my issue is the quantity, would you recommend that I add a quantity column and record how many were bough? — Claudia Sasi, Jun 05 '13 at 15:38

score 3 · Accepted Answer · answered Jun 05 '13 at 16:04

(Expanded from comments)

Honestly, the biggest problem I see is your table structure. When you have multiple columns storing the same type of object, it is usually a sign you need to normalize.

For example, store each item ordered in a separate record. The item table itself should contain the "type" of item (ie "parking", "performance", etcetera). Since it probably will not change, there is no need to store it in your orderItems table. Note: You can add other columns as needed (like "quantity").

CREATE TABLE OrderItems (
   orderID ...
   , itemID ...
   , itemCost ....
)

It will be much easier to work with the normalized table structure. Just loop through the cart array and insert one record per item. Just be sure to use cfqueryparam on all values. (Also wrap all of the queries in a transaction if you are not doing so already).

<cfloop array="#SESSION.cart#" index="details">
   <cfquery ....>
      INSERT INTO orderItems ( 
         orderID
         , itemID
         , itemCost
      )
      VALUES (
         <cfqueryparam value="#insert.GENERATEDKEY#" cfsqltype="cf_sql_integer">
         , <cfqueryparam value="#details.itemid#" cfsqltype="cf_sql_integer">
         , <cfqueryparam value="#details.price#" cfsqltype="cf_sql_decimal">
      )
   </cfquery>
</cfloop>

score 1 · Answer 2 · edited May 23 '17 at 11:48

It looks like you problem is similar to this one: How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" part of the statement?

For your code:

values('#insert.GENERATEDKEY#', '<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>',
'<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>',
'<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].itemid#<cfelse>#session.cart[i].itemid#, </cfif></cfloop></cfoutput>',
'<cfoutput><cfloop from="1" to="#arrayLen(session.cart)#" index="i"><cfif i eq arrayLen(session.cart)>#session.cart[i].price#<cfelse>#session.cart[i].price#, </cfif></cfloop></cfoutput>')
</cfquery>

Should be something like:

values

 <cfoutput>
    <cfloop from="1" to="#arrayLen(session.cart)#" index="i">
    ('#insert.GENERATEDKEY#', 
       #session.cart[i].itemid#,
       #session.cart[i].itemid#,
       #session.cart[i].price#
    )
    <cfif i NEQ arrayLen(session.cart)>,</cfif
    </cfloop>
 </cfoutput>

Many thanks James. I will give this a go. Also, as advised by Leigh, I need to consider normalization. :-) — Claudia Sasi, Jun 05 '13 at 15:43

SOL/ColdFusion: INSERTS with multiple foreign keys from session

2 Answers2