Skip to main content
J
johnbruso23
Inspiring
March 22, 2017
Question

update multiple fields within multiple records

  • March 22, 2017
  • 2 replies
  • 919 views

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

    This topic has been closed for replies.

    2 replies

    WolfShade
    WolfShade
    Legend
    March 23, 2017

    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,

    ^_^

    H
    haxtbh
    Inspiring
    March 23, 2017

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices