Highlighted

update multiple fields within multiple records

Explorer ,
Mar 22, 2017

Copy link to clipboard

Copied

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

Views

615

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

update multiple fields within multiple records

Explorer ,
Mar 22, 2017

Copy link to clipboard

Copied

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

Views

616

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 22, 2017 0
Advocate ,
Mar 23, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2017 0
LEGEND ,
Mar 23, 2017

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 23, 2017 0