Pema asks:

How can I update multiple records with single click of a button in ColdFusion?

I had to double check with Pema to grok exactly what he meant, but basically, instead of the normal form where you edit one row of data, he had multiple rows of data and wanted to update them when the form was processed. This is pretty simple (advanced readers can skip ahead to the comments and make fun of my code) but he needed an example so I whipped something up.

First, I wrote a template with a simple query and list of dynamic form fields:


<cfquery name="getArt" datasource="cfartgallery" maxrows="10">
select	artid, artname
from	art
</cfquery>

<form action="test.cfm" method="post">
<table>
<cfloop query="getArt">
	<tr>
		<cfoutput><td><input type="text" name="art_#artid#" value="#artname#" /></td></cfoutput>
	</tr>
</cfloop>
</table>
<input type="submit" name="save" value="Save" />
</form>

My query grabbed the primary key and artname from the ColdFusion sample database cfartgallery. I create one text field for each row and pass the primary key value in the name of the form field.

Processing is rather simple as well:


<cfif structKeyExists(form, "save")>
	<cfloop item="field" collection="#form#">
		<cfif findNoCase("art_", field)>
			<cfset artid = listLast(field, "_")>
			<cfquery datasource="cfartgallery">
			update	art
			set		artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">
			where	artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#">
			</cfquery>
 		</cfif>
	</cfloop>
</cfif>

If the save button was clicked, I loop over the form. If the form field begins with art_, I can grab the primary key using a list function. Then I do an update.

That's it. Pretty simple, but hopefully it helps Pema and others who may be new to ColdFusion. I've included the complete template below.


<cfif structKeyExists(form, "save")>
	<cfloop item="field" collection="#form#">
		<cfif findNoCase("art_", field)>
			<cfset artid = listLast(field, "_")>
			<cfquery datasource="cfartgallery">
			update	art
			set		artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">
			where	artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#">
			</cfquery>
 		</cfif>
	</cfloop>
</cfif>

<cfquery name="getArt" datasource="cfartgallery" maxrows="10">
select	artid, artname
from	art
</cfquery>

<form action="test.cfm" method="post">
<table>
<cfloop query="getArt">
	<tr>
		<cfoutput><td><input type="text" name="art_#artid#" value="#artname#" /></td></cfoutput>
	</tr>
</cfloop>
</table>
<input type="submit" name="save" value="Save" />
</form>