Inserting Orders! Error General error: Column count doesn't match valu 1

[bluesauceuk]

Hey,

I have this error "General error: Column count doesn't match value count at row 1" I take this to mean I am trying to insert 4 columns into a 5 column table.. sort of thing..

I have in this orderitems table (this holds the items related to the order)

These are the columns.

id, orderid (the parent), productid, quantity, size.

		<cftransaction>
 				<cflock timeout="30" name="addorder" type="exclusive">
 					<cfquery name="createorder" datasource="#application.dsn#" username="#application.dsnun#" password="#application.dsnpw#">
						INSERT INTO orders (firstname, surname, email, newsletter, phone, address1, address2, address3, address4, postcode, subtotal, pandp, total)
						VALUES ('#form.firstname#', '#form.surname#', '#form.email#', '#form.newsletter#', '#form.phone#', '#form.address1#', '#form.address2#', '#form.address3#', '#form.address4#', '#form.postcode#', #form.subtotal#, #form.pandp#, #form.total#)
					</cfquery>
					<cfquery name="getorderid" datasource="#application.dsn#" username="#application.dsnun#" password="#application.dsnpw#">
						SELECT MAX(id) AS maxordernumber
						FROM orders
						WHERE email = '#form.email#'
					</cfquery>
				</cflock>
 			</cftransaction>
 			<!--- add the items  --->
			<cftransaction>
				<cflock timeout="30" name="additems" type="exclusive">
					<cfloop collection="#session.cart#" item="i">
						<cfquery name="additems" datasource="#application.dsn#" username="#application.dsnun#" password="#application.dsnpw#">
								INSERT INTO orderitems {orderid, productid, quantity, size}
								#getorderid.maxordernumber#, #session.cart[i][1]#, #session.cart[i][4]#, '#session.cart[i][5]#'
						</cfquery>
					</cfloop> 		
				</cflock>
			</cftransaction>

any ideas?

Thanks

 

[bluesauceuk]

OOPS!

Forgot the VALUES (etc...)

This is because I just CFOUTPUTed it and it worked?

Here's the later bit as it is now..

			<cftransaction>
				<cflock timeout="30" name="additems" type="exclusive">
					<cfloop collection="#session.cart#" item="i">
						<cfquery name="additems" datasource="#application.dsn#" username="#application.dsnun#" password="#application.dsnpw#">
								INSERT INTO orderitems {orderid, productid, quantity, size}
								VALUES (#getorderid.maxordernumber#, #session.cart[i][1]#, #session.cart[i][4]#, '#session.cart[i][5]#')
						</cfquery>
					</cfloop> 		
				</cflock>
			</cftransaction>

 

[bluesauceuk]

IT WAS A } squigly bracket!!! Doh!

 

[jalpino]

Hi bluesauceuk,

Although your second peice of code isn't incorrect, there are ways that you could optimize it and further reduce the risk of lock contention by using the proper <cflock> syntax.

1. Remove the processing from within the <cflock> tags by copying your shared scope objects to local scoped objects.

2. Use 'scope' based locking instead of name based locking for shared variable scopes.

3. Use request scope instead of application scope to store datasource information. (optional, not neccessary)

You could rewrite your code as follows:

<!--- Make a deep copy of your session cart --->
<cflock timeout="5" scope="session" type="readonly">
	<cfset tmpCollection = duplicate(session.cart)>
</cflock>

<cftransaction>
   <cfloop collection="#tmpCollection#" item="i">
      <cfquery name="additems" datasource="#application.dsn#" username="#application.dsnun#" password="#application.dsnpw#">
	  INSERT INTO orderitems {orderid, productid, quantity, size}
	  VALUES (#getorderid.maxordernumber#, #tmpCollection[i][1]#, #tmpCollection[i][4]#, '#tmpCollection[i][5]#')
      </cfquery>
   </cfloop>         
</cftransaction>

Here is some additional information on using <cflock>

http://www.macromedia.com/devnet/server_archive/articles/cf_locking_best_practices.html

jalpino