coldfusion update multiple records with one form listgetat

[johnbruso23]

Hi All,

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

[WolfShade]

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,

^_^

[johnbruso23]

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

[WolfShade]

In your CFLOOP, place your index name in quotes.

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

HTH,

^_^

[johnbruso23]

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>

]

[johnbruso23]

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.

[WolfShade]

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,

^_^