6 Replies Latest reply on Jul 28, 2016 11:19 AM by WolfShade

    coldfusion update multiple records with one form listgetat

    johnbruso23 Level 1

      Hi All,

      Only1Result.PNG

      I'm not sure what I'm doing wrong. The form that I have has 11 records but my CFloop to CFQUERY is only returning 1 result. I'm expecting to see 11 records in the Recordset2_UPDATE query. The Recordset2_UPDATE query is just a SELECT statement for now, for testing.

       

      <cfloop from="1" to="#form.totRecords#" index=ct>

        <cfquery name="Recordset2_UPDATE" datasource="Incident_Reporting">

          SELECT '#form.ID#' as "UID",

              '#listgetat(form.ID,ct)#' as ID,

              '#listgetat(form.AllDocumentsAttached,ct)#'  as AllDocumentsAttached

               FROM dbo.tbl_P1

          WHERE

              ID = #listgetat(form.ID,ct)#

      </cfquery>

      </cfloop>

       

      Thanks for the second set of eyes!

       

      John

        • 1. Re: coldfusion update multiple records with one form listgetat
          WolfShade Level 4

          Hi, johnbruso23,

           

          Firstly, unless you absolutely, positively cannot avoid it, never, never, never, ever put a CFQUERY inside a loop.  It is highly inefficient, and could bottleneck your network.  And, unless you have "maintain database connections" turned on, you are connecting, submitting the SQL, disconnecting, connecting, submitting SQL, disconnecting, over and over and over and over.  This is CPU intensive.

           

          I do not see a "form.totRecords" value.  Is this a hidden field with a numeric value?

           

          Depending upon what database server you are running (you don't indicate), you may be able to do an insert with multiple form values; you state that the select is just for testing purposes.  The following works in MySQL.

           

          INSERT into tableA(col1, col2, col3, col4)
          VALUES(val1a, val2a, val3a, val4a),
              (val1b, val2b, val3b, val4b),
              (val1c, val2c, val3c, val4c)
          

           

          HTH,

           

          ^_^

          • 2. Re: coldfusion update multiple records with one form listgetat
            johnbruso23 Level 1

            Hi WolfShade,

             

            Thank you for the feedback.

             

            I do have a form.totRecords. It is a hidden field: <INPUT NAME="totRecords" TYPE="hidden" ID="totRecords" value="#Recordset2.RecordCount#"/> Even if I hard code 11 as the number of records I am only getting the 1 result. The values are in the UID field though. What is wrong with my WHERE statement?

             

            I am using SQLSERVER 2008.

             

            John

            • 3. Re: coldfusion update multiple records with one form listgetat
              WolfShade Level 4

              In your CFLOOP, place your index name in quotes.

               

              <cfloop from="1" to="#form.totRecords#" index="ct">

               

              HTH,

               

              ^_^

              • 4. Re: coldfusion update multiple records with one form listgetat
                johnbruso23 Level 1

                that would have been great if that was it.. still just 1 record.

                 

                Here's the larger code block. I have this at the bottom of the page after the </CFOUTPUT> and /form above it:

                 

                [

                <!---<CFIF ISDEFINED("Form2.actionVal")>--->
                <CFIF IsDefined("actionVal")>
                <CFIF actionVal EQ "UPDATE">
                <!--- ***************Starts : Multiple Active Reocrds************************ --->

                <CFSET Form.totRecords = Val(Form.totRecords) >

                <CFOUTPUT >

                {<cfloop from="1" to="#form.totRecords#" index="ct">
                  <cfquery name="Recordset2_UPDATE" datasource="Incident_Reporting">
                    SELECT '#form.ID#' as "UID",
                        '#listgetat(form.ID,ct)#' as ID,
                        '#listgetat(form.AllDocumentsAttached,ct)#'  as AllDocumentsAttached
                         FROM dbo.tbl_P1
                    WHERE
                        ID = #listgetat(form.id,ct)#
                </cfquery>
                  
                </cfloop>}

                   #Recordset2_UPDATE.UID#,<BR>
                #Recordset2_UPDATE.ID#<BR>
                <!---***************Ends : Multiple Active Reocrds************************--->
                </CFOUTPUT>

                <cfdump var="#FORM#" expand="yes">

                <cfdump var="#VARIABLES#" expand="yes">
                </CFIF>
                </CFIF>

                ]

                • 5. Re: coldfusion update multiple records with one form listgetat
                  johnbruso23 Level 1

                  found the issue.. the problem was that I was using the select statement... not sure why though the Where would function different with a select vs the UPDATE. but, it is working now.

                  • 6. Re: coldfusion update multiple records with one form listgetat
                    WolfShade Level 4

                    Odd.  I was just going to say that if the image you provided in your first post was an indication, then the CFLOOP of the form scope was producing nothing in between { and }, which still wouldn't make sense, as at least the first iteration was being presented.

                     

                    Glad you got it fixed, though.

                     

                    V/r,

                     

                    ^_^