Looping to add qty inside a cfoutput

[539E]

I'm having trouble with outputing from one table, but also have a cfloop to add data from another table to display on the same cfoutput id..if that made sense.

See Code.

Also, for some reason if there is more than one record listed, it stops at the first record at totalrel & says
"The value '' cannot be converted to a number.' for line <cfset totalR = totalR + getallR.rqty>
But, if only one record, the whole thing is displayed fine (except knowing to add ALL qty's..not just keeping the last)
It's very annoying.

What is wrong w/ this?

[Dan_Bracuk]

quote:

---

Originally posted by: 539E
I'm having trouble with outputing from one table, but also have a cfloop to add data from another table to display on the same cfoutput id..if that made sense.

See Code.

Also, for some reason if there is more than one record listed, it stops at the first record at totalrel & says
"The value '' cannot be converted to a number.' for line <cfset totalR = totalR + getallR.rqty>
But, if only one record, the whole thing is displayed fine (except knowing to add ALL qty's..not just keeping the last)
It's very annoying.

What is wrong w/ this?

---

cfdump your getAllR query right after you run it. Look at the rqty column.

[539E]

Page did this after cfdump....
(the db format for rqty is a number & formated as 'long integer'. Regualar integer it said it only goes up to 32,~~~.
the number can be higher than that)

Total Ordered
0

Total Released
query
RESULTSET query
CUSTNO RID RQTY
1 4703 16 5000
2 4703 17 7000
3 4703 18 4000
4 4703 19 7000

CACHED false
EXECUTIONTIME 0
SQL select Rid,CustNo,rqty from releas where forid = ?
SQLPARAMETERS array
1 5

query
RESULTSET query
CUSTNO RID RQTY
1 4703 16 5000
2 4703 17 7000
3 4703 18 4000
4 4703 19 7000

CACHED false
EXECUTIONTIME 0
SQL select Rid,CustNo,rqty from releas where forid = ?
SQLPARAMETERS array
1 5

query
RESULTSET query
CUSTNO RID RQTY
1 4703 16 5000
2 4703 17 7000
3 4703 18 4000
4 4703 19 7000

CACHED false
EXECUTIONTIME 0
SQL select Rid,CustNo,rqty from releas where forid = ?
SQLPARAMETERS array
1 5

query
RESULTSET query
CUSTNO RID RQTY
1 4703 16 5000
2 4703 17 7000
3 4703 18 4000
4 4703 19 7000

CACHED false
EXECUTIONTIME 0
SQL select Rid,CustNo,rqty from releas where forid = ?
SQLPARAMETERS array
1 5

query
RESULTSET query
CUSTNO RID RQTY
1 4703 16 5000
2 4703 17 7000
3 4703 18 4000
4 4703 19 7000

CACHED false
EXECUTIONTIME 0
SQL select Rid,CustNo,rqty from releas where forid = ?
SQLPARAMETERS array
1 5

[Dan_Bracuk]

Let's look this part
<cfloop from="1" to="#viewR.recordcount#" index="t">
<cfquery name="getallR" datasource="#DS#">
select Rid,CustNo,rqty from releas where forid = <cfqueryparam value="#getfor.fID[currentrow]#" CFSQLType="CF_SQL_INTEGER">
</cfquery>
<cfif viewR.recordcount LT 1>
<cfset totalamountR = 0>
<cfelse>
<cfset totalR = 0>
<cfset totalR = totalR + getallR.rqty>

It's probably the value of t that is getting you. If query viewR returns, let's say 10 rows, and query getallR is returning 4 or 5, eventually you are going to be looking for the 6th record out of 5.

That will produce an empty string, which, as your error message said, can't be converted to a number.

Your entire approach is very convoluted and inefficient by the way. You should look for ways to avoid running select queries inside loops.

[539E · Accepted solution]

yah, I know it's quite sloppy. 🙂 Thanks for your quidance.

For now, I've just left out needing the other var. & just used the qty's... so I changed it to ..
Today, I needed to show what the section does/looks like.

<cfquery name="ckR" datasource="#DS#">
select * from releas where formid = <cfqueryparam value="#getfor.fID[currentrow]#" CFSQLType="CF_SQL_INTEGER">
</cfquery>
<cfif ckR.recordcount EQ 0><cfset totalrel = 0>
<cfelseif ckR.recordcount GT 0>
<cfquery name="viewR" datasource="#DS#">
select SUM(rqty) AS totalrel from releas where forid = <cfqueryparam value="#getfor.fID[currentrow]#" CFSQLType="CF_SQL_INTEGER">
</cfquery>
<cfset totalrel = viewR.totalrel>
</cfif>
& so for the orders table

Now, everything adds properly for the forms displayed.