2 Replies Latest reply on Mar 23, 2017 6:52 AM by WolfShade

    update multiple fields within multiple records

    johnbruso23

      Hi All,

       

      I'm trying to edit this piece of code to allow me to edit more than just one field within a multi-record update. However, when I add a second field to the SET, I get this error:

       

      "Invalid list index 5. In function ListGetAt(list, index [, delimiters]), the

      value of index, 5, is not a valid as the first argument (this list has 4

      elements). Valid indexes are in the range 1 through the number of elements in

      the list.

      The error occurred on line 120." (Line 120 is BESFollowUpStatus)

       

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

      <CFOUTPUT >
      <cfloop from="1" to="#form.totRecords#" index="ct">
        <cfquery name="Recordset2_UPDATE" datasource="Incident_Reporting">
              UPDATE dbo.tbl_P1
              SET 
              AllDocumentsAttached = '#listgetat(form.AllDocumentsAttached,ct)#',

              BESFollowUpStatus   = '#listgetat(form.BESFollowUpStatus,ct)#'

       

       

       

          WHERE
              ID = #listgetat(form.ID,ct)#
      </cfquery>

       

      </cfloop>
      <!---***************Ends : Multiple Active Reocrds************************--->
      </CFOUTPUT>

       

      Can you help me understand the error in the logic or if I am just going about this all wrong?

       

      Thanks,

       

      John

        • 1. Re: update multiple fields within multiple records
          haxtbh Level 4

          It is telling you that you are trying to select an element from the list that doesn't exist. You are looping using the 'ct' index but this index goes higher then the amount of list items in 'form.BESFollowUpStatus'

           

          What is the value of form.BESFollowUpStatus when its passed through?

           

          If form.totRecords is always greater then the amount of list items, this error will always occur.

          • 2. Re: update multiple fields within multiple records
            WolfShade Level 4

            What database are you using?  MS-SQL, Oracle, MySQL?

             

            I'm not trying to sound like a jerk, but the code you presented made me wince, just a bit, because you are using a CFQUERY within a loop.  Even if you have persistent connections, this is inefficient for both CPU and network.

             

            If you are using Oracle, you can use one query to manipulate multiple rows of data.

            Oracle:

            INSERT ALL 
                INTO table(col1,col2,col3) VALUES(val1,val2,val3)
                 INTO table(col1,col2,col3) VALUES(val1,val2,val3)
                 INTO table(col1,col2,col3) VALUES(val1,val2,val3)
            SELECT 1 FROM DUAL; <!---  Yes, this is a necessary step - remove the semicolon if it errors. --->
            

             

            So you loop within the query to get each INTO table written, then execute the query once.  BAM!

            INSERT ALL
            <cfloop from="1" to="#form.totRecords#" index="ct">
                 INTO table(col1,col2,col3) VALUES(#val1#,#val2#,#val3#)
            </cfloop>  SELECT 1 FROM DUAL;  <!---  Yes, this is a necessary step - remove the semicolon if it errors. --->
            

             

            I think MS-SQL has something similar.

             

            Not sure about your index range issue.  It's trying to update a record that doesn't exist, apparently.

             

            V/r,

             

            ^_^