Copy link to clipboard
Copied
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
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,
^_^
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
Copy link to clipboard
Copied
In your CFLOOP, place your index name in quotes.
<cfloop from="1" to="#form.totRecords#" index="ct">
HTH,
^_^
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>
]
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.
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,
^_^