cfloop using session to insert values

devondago · Sep 5, 2003

I am almost done with a registration form for events. I am bringing in all my values throught my session.Crtcalid. My problem is that if there are 3 events a user signed up for the session.crtcalid inserts all the values of that field on the column rathen than one at a time loop and insert the values again. Here is the code I was using before.

<cfloop index="IndexCount" from="1" To="#ListLen(Session.Crtcalid, "^&quot

#">
<cfoutput>
<cfset xname = #ListGetAt(Session.Crtxname, IndexCount, "^&quot

#>
<cfset ProdQty = #ListGetAt(Session.CrtQuantity, IndexCount, "^&quot

#>
<cfset ProdCost = #ListGetAt(Session.CrtPrice, IndexCount, "^&quot

# *
#ListGetAt(Session.CrtQuantity, IndexCount, "^&quot

#>
</cfoutput>
<CFQUERY Name = "AddItemsToDbase" Datasource = #application.dsn#>
--insert here-----
I got the following error due to the loop inserting all the values at once of session.crtcalid.
Error","5384","09/03/03","17:10:37",,"xxx.xx.xxx.xxx, Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322), ODBC Error Code = 22001 (String data right truncation) [MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-01401: inserted value too large for column The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (73:1) to (73:75).Date/Time: 09/03/03 17:10:36
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
Remote Address: xxx.xx.xxx.xxx
HTTP Referrer:

http://www.testone.com/cart/testcart.cfm"

I went ahead and chaged the loop to the following to see if it got better.
<cfset num_of_recurs = #Session.Crtcalid#>
<cfloop index="IndexCount" from="1" to="#ListLen(num_of_recurs)#">
<CFQUERY Name = "AddtemsToDbase" Datasource = #application.dsn#>
I have 14 inserts on this query and it keeps inserting all values at once. The session crt.calid holds the id for each event but it keeps trying to insert all values in one row. How can I break this down so that for each event there is a row added to the db? Any ideas how I can set up the loop.
This is the error that I am getting now.
"Error","3164","09/05/03","10:08:13",,"xxx.xx.xxx.xxx, Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322), TO Cannot convert 0000002394^0000002408 to number.Please, check the ColdFusion manual for the allowed conversions between data typesThe error occurred while processing an element with a general identifier of (CFLOOP), occupying document position (62:1) to (62:48).Date/Time: 09/05/03 10:08:13
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
Remote Address: xxx.xx.xxx.xxx
HTTP Referrer:

http://www.testl.com/cart/testcart.cfm"

ANy suggestions would be appreciated.

csteinhilber · Sep 5, 2003

There are a couple of things going on, I think.

First, you're original code:

Code:

<cfloop index=&quot;IndexCount&quot; from=&quot;1&quot; To=&quot;#ListLen(Session.Crtcalid, &quot;^&quot;)#&quot;>
<cfoutput>
<cfset xname = #ListGetAt(Session.Crtxname, IndexCount, &quot;^&quot;)#>
<cfset ProdQty = #ListGetAt(Session.CrtQuantity, IndexCount, &quot;^&quot;)#>
<cfset ProdCost = #ListGetAt(Session.CrtPrice, IndexCount, &quot;^&quot;)# * 
#ListGetAt(Session.CrtQuantity, IndexCount, &quot;^&quot;)#> 
</cfoutput>

you're looping over one list and pulling items from another. That's usually a very dangerous thing to do, particularly in ColdFusion. Because you're assuming that Session.Crtcalid has exactly the same number of items in it's list as Session.Crtxname, Session.CrtPrice and Session.CrtQuantity. This seems like an easy thing to guarantee, until you remember that ColdFusion completely ignores list items that are null. In other words, you may set up:

Code:

Session.Crtxname = &quot;name1,name2,name3&quot;

and

Code:

Session.CrtQuantity= &quot;1,,1&quot;

with three items in each, but ColdFusion actually only considers the CrtQuantity list to have two items. And, in fact,

Code:

ListGetAt(&quot;#Session.CrtQuantity#&quot;,2)

will return a value of "1".

You would be much safer storing everything in an array of structures, or a structure of structures.

Code:

<CFSET arCrt = ArrayNew(1)>
<CFSET arCrt[1] = StructNew()>
<CFSET arCrt[1][&quot;name&quot;] = &quot;name&quot;>
<CFSET arCrt[1][&quot;quantity&quot;] = &quot;1&quot;>
<CFSET arCrt[1][&quot;price&quot;] = &quot;9.99&quot;>
       :

that way... you loop over the parent array, and you're absolutely assured that all your quantity, price, and name values are always in sync.

At any rate... it looks like the error in the first code sample is due to the fact that

Code:

ListGetAt(&quot;#Session.CrtPrice#&quot;, IndexCount, &quot;^&quot;) * ListGetAt(&quot;#Session.CrtQuantity#&quot;, IndexCount, &quot;^&quot;)

is resolving to a integer value that's greater than the maximum value that can be stored in the database column as it's currently set up. You didn't mention what database you were using, or what the datatype of the ProdCost column is... but somehow you're getting a value that's higher than you probably expected. You'll either need to change the column type in your table to allow for a larger number, or just store the quantity and price values, then calculate the ProdCost when you pull the values out of the data.

Your second code sample doesn't have the proper delimiter, I'm assuming.
If

Code:

<cfset num_of_recurs = #Session.Crtcalid#>

then your loop will want the "^" delimiter, just as in your first sample

Code:

<cfloop index=&quot;IndexCount&quot; from=&quot;1&quot; to=&quot;#ListLen(num_of_recurs,&quot;^&quot;)#&quot;>

Without it, you're passing "0000002394^0000002408" to your SQL... which is actually scientific notation for some sort of astronomical number it can't decipher.

-Carl

devondago · Sep 5, 2003

Thanks for the feedback but I am a little lost. I am using Oracle as my db. You got me a little lost on the array structure part. Do you have a close enough sample for guidance.
My error is revolving around the Session.Crtcalid when the values are inserted into the table I see that all values are going in once but when I am inserting the session.crt.calid is inserting the following. (error from log file)
Error","5384","09/05/03","16:36:53",,"172.16.177.115, Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322), ODBC Error Code = 22001 (String data right truncation) [MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-01401: inserted value too large for column The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (81:1) to (81:75).Date/Time: 09/05/03 16:36:53 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322) Remote Address: xxx.xx.xxx.xxx HTTP Referrer:

http://www.test.com/cart/testt.cfm"

"Error","732","09/05/03","17:06:28",,"ODBC Error Code = 22001 (String data right truncation) [MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-01401: inserted value too large for column SQL = ""Insert Into test_data.event
xstatus, xdate, xtime, xgroup$, xgroupdate, xgrouptime, xcreate, xupdate, xuser, rhas_note$, xlocator, xuniquekey, xorigin, xnumber )
Values (
'E',to_date('05-Sep-03', 'DD-MON-YY'),'17:06:27',
'E',to_date('05-Sep-03', 'DD-MON-YY'),'17:06:27',
to_date('05-Sep-03', 'DD-MON-YY'),
to_date('05-Sep-03', 'DD-MON-YY'),
'WEB', '0','0000002408^0000002395', 'W00000561',
'01','W0000000561' )"" "
As you can see the session.crtcalid is inserting 2 values for the field (since i selected 2 events)each of those values being inserted into the xlocator is an event and I need to find a way to make a single insert per event selected and hold in the session.crtcalid field.
I want to loop over the query for each value that the session.crtcalid holds until its done with the insert.
You mentioned setting up and array but how?
Hope you can help me on this.
thanks in advance.

csteinhilber · Sep 5, 2003

Well... I'm not sure how much clearer I can make it. How do you set up the array? I already provided sample code for that above. If you really need to store it in a session variable, it would simply be:

Code:

<CFSET session.arCrt = ArrayNew(1)>
<CFSET session.arCrt[1] = StructNew()>
<CFSET session.arCrt[1][&quot;id&quot;] = &quot;12345&quot;>
<CFSET session.arCrt[1][&quot;name&quot;] = &quot;name&quot;>
<CFSET session.arCrt[1][&quot;quantity&quot;] = &quot;1&quot;>
<CFSET session.arCrt[1][&quot;price&quot;] = &quot;9.99&quot;>
       :

or, if this truly is a shopping cart, you'd probably be better off using a structure, rather than an array. It makes some things just a bit easier:

Code:

<CFSET session.strCrt = StructNew()>
<CFSET session.strCrt[&quot;12345&quot;] = StructNew()>
<CFSET session.strCrt[&quot;12345&quot;][&quot;name&quot;] = &quot;Product 1&quot;>
<CFSET session.strCrt[&quot;12345&quot;][&quot;quantity&quot;] = &quot;1&quot;>
<CFSET session.strCrt[&quot;12345&quot;][&quot;price&quot;] = &quot;9.99&quot;>

<CFSET session.strCrt[&quot;67890&quot;] = StructNew()>
<CFSET session.strCrt[&quot;67890&quot;][&quot;name&quot;] = &quot;Product 2&quot;>
<CFSET session.strCrt[&quot;67890&quot;][&quot;quantity&quot;] = &quot;3&quot;>
<CFSET session.strCrt[&quot;67890&quot;][&quot;price&quot;] = &quot;12.99&quot;>
       :

you would actually do this when you're inputing the values into your cart.

Then, when you need to insert the data into your database, you would do something like:

Code:

<CFLOOP collection=&quot;#session.strCrt#&quot; item=&quot;whichItem&quot;>
   <CFQUERY ...>
       INSERT into test_data
         (xstatus, 
          xdate, 
          xtime,  
          xprodid, 
          prodname, 
          prodprice, 
          prodqty)
       VALUES 
         ('E',
          to_date('#DATEFORMAT(now(),&quot;dd-mmm-yy&quot;)#','DD-MON-YY'),
          '#TIMEFORMAT(now(),&quot;HH:mm:ss&quot;)#',
          '#whichItem#',
          '#session.strCrt[whichItem][&quot;name&quot;]#',
          '#session.strCrt[whichItem][&quot;price&quot;]#',
          '#session.strCrt[whichItem][&quot;quantity&quot;]#',
   </CFQUERY>
</CFLOOP>

or whatever.

-Carl

devondago · Sep 8, 2003

Thanks for taking the time to help., I went ahead and created the added the array. I am now in need to add another column in my insert statement. When I added this column I got the following error.
"Error","4516","09/08/03","15:39:18",,"ODBC Error Code = HY000 () [MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-20273: XLocator() not found in test_cl table ORA-06512: at ""testreg_DATA.SBS_TRG_EVENT"", line 114 ORA-04088: error during execution of trigger 'testreg_DATA.SBS_TRG_EVENT' SQL = ""Insert Into test_data.event ( xstatus, xdate, xtime, xgroup$, xgroupdate, xgrouptime, xcreate, xupdate, xuser, rhas_note$, xlocator, xuniquekey, xorigin, xnumber ) Values ( 'E', to_date('08-Sep-03', 'DD-MON-YY'), '15:39:18', 'E', to_date('08-Sep-03', 'DD-MON-YY'), '15:39:18', to_date('08-Sep-03', 'DD-MON-YY'), to_date('08-Sep-03', 'DD-MON-YY'), 'WEB', '0', '', 'W00000591', '01', 'W0000000591' )"" "
"Error","4516","09/08/03","15:39:18",,"xxx.xx.xxx.xxx, Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322), ODBC Error Code = HY000 () [MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-20273: XLocator() not found in CLASS table ORA-06512: at ""testreg_DATA.SBS_TRG_EVENT"", line 114 ORA-04088: error during execution of trigger 'testreg_DATA.SBS_TRG_EVENT' The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (87:1) to (87:39).Date/Time: 09/08/03 15:39:18 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322) Remote Address: xxx.xx.xxx.xxx HTTP Referrer:

http://www.testsitereg.com/cart/testCart.cfm"

<CFLOOP collection="#session.calid#" item="whichItem">
<CFQUERY datasource= #application.dsn#>
<cfset rid="W" & numberformat(getrid.recurid + 1,"0000000000&quot

>
Insert Into registrar_data.event
(
xstatus, xdate, xtime, xgroup$, groupdate, xgrouptime, xcreate,
xupdate, xuser,rhas_note$, xlocator,xuniquekey,xorigin, xnumber
)
Values
(
'E', to_date('#DateFormat(now(),"dd-mmm-yy&quot

#', 'DD-MON-YY'),
'#registertime#', 'E',
to_date('#DateFormat(now(),"dd-mmm-yy&quot

#', 'DD-MON-YY'),
'#registertime#',
to_date('#DateFormat(now(),"dd-mmm-yy&quot

#', 'DD-MON-YY'),
to_date('#DateFormat(now(),"dd-mmm-yy&quot

#', 'DD-MON-YY'),
'WEB', '0','#session.calid[whichItem]["calid"]#',
'#CustomerIDNumber#', '01', '#rid#' )
</cfquery> </cfloop>

Any suggestions getting over this error?

Thanks!

csteinhilber · Sep 8, 2003

That's saying that you need to revise the "testreg_DATA.SBS_TRG_EVENT". The columns it's trying to act on don't match the columns available in your table "test_cl".

-Carl

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

cfloop using session to insert values

devondago

Programmer

csteinhilber

Programmer

devondago

Programmer

csteinhilber

Programmer

devondago

Programmer

csteinhilber

Programmer

Similar threads

Part and Inventory Search

Sponsor