sql - 溶胶/冷聚变 : INSERTS with multiple foreign keys from session

Question

对于这个冗长的问题深表歉意，我以为我会涵盖我尝试过的所有内容!

我有一个购物车应用程序，它将选择的项目存储在一个数组中，然后将这些项目传递到 OrderItems 表以存储在数据库中。 orderItems 表中的条目可以作为客户的明细订单页面输出，例如，如果客户想要查看过去的订单。

我想遍历 session ，并确定 session.itemid 属于哪个产品表，并基于此，将属于正确 PK 列名称的 ID 插入到 OrderItems 表中。

例如session.cart的CFDUMP:

以上每一项都属于一个单独的表。

OrderItems 表

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

如果购物车中只有一件商品，当前部分工作的代码可以正常执行，但是它将 itemid 存储在所有 3 列中，而不是与 ID 列名称匹配的列中。此外，如果我有多个不同 ID 的项目，则会返回错误。请在下面的代码后查看错误代码。

 <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>

如果 session.cart 中有多个具有不同 ID 的项目，则会出错:

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: 

尝试使用以下方法，如果有多个项目，它会起作用，但是它会用相同的 ID 填充所有三个 ID 列，而且它只存储项目的 1 个条目而不是数量，即。我有两个，所以我想将这两个项目存储为单独的记录:

<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>

产生错误的代码:

  <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>

错误:

执行数据库查询时出错。[Macromedia][SQLServer JDBC 驱动程序][SQLServer]关键字“where”附近的语法不正确。

错误发生在C:/ColdFusion10/cfusion/wwwroot/ce0932a/coursework/process2.cfm: line 29

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

---

我对此束手无策，不知道从这里去哪里......

将 Coldfusion 10 与 SQL 2008 R2 结合使用

Answer 1

(从评论中扩展)

老实说，我看到的最大问题是您的表结构。当您有多个列存储相同类型的对象时，这通常是您需要 normalize 的标志。 .

例如，将订购的每件商品存储在单独的记录中。 item 表本身应该包含项目的“类型”(即“parking”、“performance”等)。由于它可能不会更改，因此无需将其存储在您的 orderItems 表中。 注意:您可以根据需要添加其他列(例如“数量”)。

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

使用规范化的表结构会容易得多。只需循环遍历购物车数组并为每个 item 插入一条记录。请务必对所有值使用 cfqueryparam。 (如果您还没有这样做，也可以将所有查询包装在一个事务中)。

<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>