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

[haxtbh]

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.

[WolfShade]

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,

^_^