Highlighted

coldfusion update multiple records with one form listgetat

Explorer ,
Jul 28, 2016

Copy link to clipboard

Copied

Hi All,

Only1Result.PNG

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

Views

843

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

coldfusion update multiple records with one form listgetat

Explorer ,
Jul 28, 2016

Copy link to clipboard

Copied

Hi All,

Only1Result.PNG

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

Views

844

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
Jul 28, 2016 0
LEGEND ,
Jul 28, 2016

Copy link to clipboard

Copied

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,

^_^

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...
Jul 28, 2016 0
Explorer ,
Jul 28, 2016

Copy link to clipboard

Copied

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

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...
Jul 28, 2016 0
LEGEND ,
Jul 28, 2016

Copy link to clipboard

Copied

In your CFLOOP, place your index name in quotes.

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

HTH,

^_^

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...
Jul 28, 2016 0
Explorer ,
Jul 28, 2016

Copy link to clipboard

Copied

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>

]

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...
Jul 28, 2016 0
Explorer ,
Jul 28, 2016

Copy link to clipboard

Copied

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.

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...
Jul 28, 2016 0
LEGEND ,
Jul 28, 2016

Copy link to clipboard

Copied

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,

^_^

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...
Jul 28, 2016 0