I decided that is was time for me to tackle it so here goes.
Scenario:
You have a form with multiple rows.
You need to perform CRUD on the records all in one place. (Brain starting to hurt yet?)
Don't worry. It's fairly easy.
Step 1 - Retrieve your records:
<cfquery name="getArt" datasource="cfartgallery" maxrows="10">
select artid, artname from art
</cfquery>
#currentRow# is used to give each row a unique identifier and I pass the row count as a hidden field to use to loop through the fields once submitted (see red highlights below).
<form action="formLoopUpdate.cfm" method="post">
<table>
<cfloop query="getArt">
<tr> <cfoutput>
<td>
<input type="text" name="art#currentRow#" value="#artname#" />
<input type="hidden" name="artID#currentRow#" value="#artID#" />
</td>
</cfoutput> </tr>
</cfloop>
</table>
<input type='hidden' name='totalNumberOfRecords' value='<cfoutput>#getArt.recordCount#</cfoutput>'>
<input type="submit" name="save" value="Save" />
</form>
3. Submit and save
No need for commentary here. It's explains itself.
<cfif structKeyExists(form, "save")>
<cfloop from="1" to="#form.totalNumberOfRecords#" index="counter">
<!--- extract the values of the dynamic form fields --->
<cfset art = form["art" & counter]>
<cfset artID = form["artID" & counter]>
<cfquery datasource="cfartgallery">
update art
set artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#art#">
where artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#">
</cfquery>
</cfloop>
</cfif>
That's it.
Let me know what you think or how this could have been done differently.
-MB
Another example:
<cfloop from="1" to="#form.totalNumberItems#" index="counter">
<!--- update category order --->
<cfif isdefined("form.categoryOrder#counter#")>
<cfset categoryOrder = form["categoryOrder" & counter]>
<cfset categoryID = form["categoryID" & counter]>
<cfquery name="updateCategoryOrder" datasource="#application.var#">
update Checklist_Category
set sortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#categoryOrder#">
where categoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#categoryID#">
</cfquery>
</cfif>
<!--- update checklist item order --->
<cfif isdefined("form.checkListOrder#counter#")>
<cfset checkListOrder = form["checkListOrder" & counter]>
<cfset checkDetailID = form["checkDetailID" & counter]>
<cfquery name="updateCheckListOrder" datasource="#application.var#">
update Checklist_Admin
set sortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#checkListOrder#">
where checkDetailID = <cfqueryparam cfsqltype="cf_sql_integer" value="#checkDetailID#">
</cfquery>
</cfif>
</cfloop>
</cfif>
