4 Replies Latest reply on Aug 6, 2008 4:46 AM by 539E

    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[t]>
      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?
        • 1. Re: Looping to add qty inside a cfoutput
          Dan Bracuk Level 5
          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[t]>
          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.


          • 2. Looping to add qty inside a cfoutput
            539E Level 1
            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


            • 3. Re: Looping to add qty inside a cfoutput
              Dan Bracuk Level 5
              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[t]>

              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.
              • 4. Re: Looping to add qty inside a cfoutput
                539E Level 1
                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.