Skip to main content
J
johnbruso23
Inspiring
July 28, 2016
Question

coldfusion update multiple records with one form listgetat

  • July 28, 2016
  • 1 reply
  • 1297 views

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

    This topic has been closed for replies.

    1 reply

    WolfShade
    WolfShade
    Legend
    July 28, 2016

    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,

    ^_^

    J
    johnbruso23Author
    Inspiring
    July 28, 2016

    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
    WolfShade
    Legend
    July 28, 2016

    In your CFLOOP, place your index name in quotes.

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

    HTH,

    ^_^

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