Ask a Jedi: Updating multiple records at once

This post is more than 2 years old.

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>
Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Tony Nelson posted on 2/11/2009 at 9:19 AM

I think it's worth mentioning Brian Kotek's Form Utilities CFC for those that haven't checked it out yet.

http://formutils.riaforge.org/

Comment 2 by Tom K posted on 2/11/2009 at 2:43 PM

Is there a reason you didn't put the loop within the CFQuery tag?

Presumably your current code will hit the database per round on the loop, whereas I tend to loop within the INSERT/UPDATE statement, which would hit the database only once.

(or was just to a) keep it simple, b) Access probably doesn't support it or something?)

Comment 3 by Joel Cox posted on 2/11/2009 at 7:12 PM

I do this kind of thing in much the same way as you have described. I ran into a situation where two users had the same form open and both updated at different times. Obviously, the second user wins. This can occur on a single-row edit, too, of course, but is more likely when multiple updates are done.

I generate a current timestamp in the query that creates the form and add it to each row in a hidden field. (When a record is updated, I update the timestamp column for that row.) Before updating I compare the timestamp in the row to be updated with the timestamp sent with the form data. If the timestamp in the database table is newer than that passed in with the form data, then another user has updated the data behind your back. In this case, I refuse the update, save the key value in a struct, then highlight the form rows in red (based upon the key) that could not be updated.

This has saved me from receiving confused-user phone calls about why the data they had entered "came out wrong".

Comment 4 by Raymond Camden posted on 2/11/2009 at 7:15 PM

@TomK: Yes, just trying to keep it as simple as possible.

Comment 5 by Joe DuVall posted on 2/11/2009 at 7:22 PM

Since you're running a bunch of cfquery blocks, wouldn't it be a good idea to wrap a cftransaction tag around the loop? :)

Nice example, though. I wouldn't have thought to do it that way initially.

Also, thanks for the link, Tony. I didn't know that utility existed.

Comment 6 by JC posted on 2/11/2009 at 8:00 PM

Depending on what you're actually doing, the easiest way to do this could just be cfgrid & cfgridupdate.

Comment 7 by Raymond Camden posted on 2/11/2009 at 8:01 PM

I'd disagree - at least for the newbie user.

Comment 8 by JC posted on 2/11/2009 at 8:48 PM

*shrug*
cfgridupdate grid="artGrid" datasource="cfartgallery" tablename="art"

One line to update any number of rows, as long as you're sticking to a single table. And the syntax for CFGRID itself isn't that bad. It looks cluttered typed out because there are so many options, but once you've done one of them you can pretty much copy/paste them for any simple CRUD you need, just adding cfgridcolumn tags as need

I use it for stuff like updating tables of interest rates/terms, stock & weather lookups and custom links on our intranet; I also use cfgrid for a fairly complicated payroll pre-processing system, but not cfgridupdate there, because the data has to have lots of validation run against it and then it's inserted into multiple tables.

Comment 9 by Raymond Camden posted on 2/11/2009 at 8:50 PM

Heh, would you believe I forgot about cfgridupdate? I get a bit worried when the SQL is done all 'automagically', but yeah, that would be pretty easy!

Comment 10 by Pema posted on 2/11/2009 at 9:48 PM

Thanks Raymond. It is awesome.

Comment 11 by Chris posted on 2/26/2009 at 8:40 PM

What about updating multiple records with multiple fields, like ARTNAME and DESCRIPTION?

Comment 12 by Raymond Camden posted on 2/27/2009 at 9:09 PM

You would need a second set of form fields for description. This would make the code a bit more complex. You couldn't just loop over each field since now you have 'sets' of fields for each record. Would you like an example of this?

Comment 13 by Chris posted on 2/27/2009 at 9:34 PM

Yeah, I figured that's how you would have to do it. If you don't mind giving an example, that would be great! Thanks Ray!

Comment 14 by Raymond Camden posted on 2/27/2009 at 9:37 PM

I've added it to my list of blog ideas. So it is recorded and will be eventually written.... one day.

Comment 15 by PC posted on 9/28/2009 at 9:22 PM

I wouldn't mind seeing the code for updating one record with multiple fields (ie. artname, description, date). I been trying to use your code for this but alas , days later I'm still struggling....any help would be fantastic!

Comment 16 by Raymond Camden posted on 9/28/2009 at 9:48 PM

Here is the followup: http://tinyurl.com/yb34z3o

Comment 17 by PC posted on 9/30/2009 at 8:42 PM

So how would I update my table with the current date if I use:
RegDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Now()#" /> (it outputs current date in every record)
OR
<cfset form.RegDate to #Now()#>
<cfset RegDate = form["RegDate_" & ID]>
RegDate = <cfqueryparam cfsqltype="cf_sql_date" value="#RegDate#" /> (I get error "Element RegDate_24 is undefined in a Java object of type class coldfusion.filter.FormScope referenced as ....")

Not sure how to handle dates in your example.

Thanks for any help you can give.

Comment 18 by Raymond Camden posted on 10/1/2009 at 5:26 AM

I'd have to see your code. I assume you made a new INPUT field with the name regdate_#id#.

Comment 19 by PC posted on 10/2/2009 at 12:18 AM

Here's my code:

<cfif structKeyExists(form, "submit")>
<cfloop item="field" collection="#form#">
<cfif findNoCase("Name_", field)>
<cfset ID = listLast(field, "_")>
<cfset Phone = form["Phone_" & ID]>
<!--- <cfset RegDate = form["RegDate_" & ID]> --->
<cfquery datasource="#application.dsn#">
Update EventRegistration
Set Name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">,
Phone = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Phone#">
<cfif #form[field]# is not ''>
,RegDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Now()#" />
</cfif>
Where ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ID#">
</cfquery>
</cfif>
</cfloop>
</cfif>

<cfquery name="getRecords" datasource="#application.dsn#" maxrows="32">
select *
from EventRegistration
</cfquery>

<form action="" method="post">
<input type="hidden" name="RegDate">
<TABLE style="font-family: Verdana, Arial, sans-serif; font-size: 12px;" align="center" border="0" cols="5">
<h1>Event Registration</h1>
<TR style="color: darkblue;" VALIGN="top" ALIGN="left">
<TH bgcolor="#FFFFCC" width="10%">Event Date</TH>
<TH bgcolor="#FFFFCC" width="10%">Event Time</TH>
<TH bgcolor="#FFFFCC" width="10%">Name</TH>
<TH bgcolor="#FFFFCC" width="10%">Phone</TH>
<TH bgcolor="#FFFFCC" width="10%">Registration Date</TH>
</TR>

<cfloop query="getRecords">
<tr valign="top">
<cfoutput group="EventDate">
<TR style="color: darkblue;" VALIGN="top" ALIGN="left">
<TD width="10%">#GetRecords.EventDate#</TD>
<TD width="10%">#GetRecords.EventTime#</TD>
<TD width="10%"><input type="text" id="Name_#ID#" name="Name_#ID#" value="#Name#" <cfif Name is not ''> disabled </cfif> /></TD>
<TD width="10%"><input type="text" id="Phone_#ID#" name="Phone_#ID#" value="#Phone#" <cfif Phone is not ''> disabled </cfif> /></TD>
<TD width="10%">#DateFormat(GetRecords.RegDate,"dd-mmm-yyyy")#</TD>
</tr>
<tr><td colspan="5"> </td></tr>
</cfoutput>
</tr>
</cfloop>
</table>
<input align="middle" type="submit" name="submit" value=" Submit " /> <input type="button" value=" Cancel " onClick="window.opener=null; window.close(); return false" />
</form>

Comment 20 by Raymond Camden posted on 10/2/2009 at 1:57 AM

Please do not post entire files. :) Next time, use Pastebin.

So I do not see a form field named regdate_ID. Do you? Notice that you have one for name_id, and phone_id. You _display_ the date but it's not a form input.

Comment 21 by PC posted on 10/3/2009 at 12:04 AM

Sorry for posting the entire code.

I've tried:
<input type="hidden" name="RegDate">
<input type="hidden" name="RegDate_ID">
<input type="hidden" name="RegDate_#ID#">

Still get error message....

Thanks

Comment 22 by Raymond Camden posted on 10/3/2009 at 12:17 AM

You definitely want RegDate_#ID#. This marks the field as being related to an ID. Then this should work:

<cfset RegDate = form["RegDate_" & ID]>

Please also read my followup linked to above under Related Blog Entries.

Comment 23 by Jim H posted on 10/24/2009 at 9:46 PM

Thank you Ray! This just saved me hours....

Comment 24 by Marc Cerabona posted on 8/12/2011 at 12:04 AM

I am trying to use this code on my site. I think that I have copied it over, but something does not seem to be working. anyone see what I am missing.

<cfif structKeyExists(form, "save")>
<cfloop item="field" collection="#form#">
<cfif findNoCase("lab_", field)>
<cfset volactionnumber = listLast(field, "_")>
<cfquery datasource="fieldtool">
update volaction
Set volaction_label = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">
Where volactionnumber = <cfqueryparam cfsqltype="cf_sql_integer" value="#volactionnumber#">
</cfquery>
</cfif>
</cfloop>
</cfif>
<cflocation url="metrics.cfm">

Comment 25 by Marc Cerabona posted on 8/12/2011 at 12:36 AM

Please disregard the question above. Problem between the keyboard and the chair.

Comment 26 by Ty Whalin posted on 1/18/2013 at 4:49 AM

Okay, this is a good one. Found out how I should send the entire file pertaining to the other post surrounding the cffeed conversation we have been having. This one is for updating the same looped fields in a form to the SQL update. Very tricky, going to try out some of these ideals. THX Ray

Comment 27 by Cassi Bassolino posted on 12/4/2013 at 12:02 AM

Newbie here.
This form is perfect for me, however I can't get it to work. Please advise what I've missed in my code:

<cfif structKeyExists(form, "save")>
<cfloop item="field" collection="#form#">
<cfif findNoCase("members_", field)>
<cfset id = listLast(field, "_")>
<cfquery datasource="#datasource#">
update members
set tee = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">
where id = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
</cfquery>
</cfif>
</cfloop>
</cfif>

Comment 28 by Cassi Bassolino posted on 12/4/2013 at 12:09 AM

On second thought, maybe I should include all three blocks of code. Please advise and thanks in advance.

<cfif structKeyExists(form, "save")>
<cfloop item="field" collection="#form#">
<cfif findNoCase("members_", field)>
<cfset id = listLast(field, "_")>
<cfquery datasource="#datasource#">
update members
set tee = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">
where id = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
</cfquery>
</cfif>
</cfloop>
</cfif>

<cfquery name="gettees" datasource="#datasource#">
SELECT id,tee
FROM members
WHERE tee <> 0
</cfquery>

<form action="teeassignments1.cfm" method="post">
<cfloop query="gettees">
<cfoutput><input type="hidden" name="members_#id#" value="#tee#" /></cfoutput>
</cfloop>
<input type="submit" name="save" value="Clear ALL Tee Assignments" />
</form>

Comment 29 by Raymond Camden posted on 12/4/2013 at 3:12 AM

You didn't say *how* it failed.

Comment 30 by Cassi Bassolino posted on 12/4/2013 at 9:11 AM

Oh, well, no errors were displayed - the page just refreshed on submission but with no change to the database. webpage source shows the records displaying properly prior to submission. Anything else I can tell you that might help? thanks so much - really appreciate the help.

Comment 31 by Raymond Camden posted on 12/4/2013 at 4:11 PM

I'd begin by debugging the clause on top. For example, after this:

<cfif findNoCase("members", field)>

add:

Yes, running for #field#<br/>

And confirm it is being displayed.

Comment 32 by Cassi Bassolino posted on 12/5/2013 at 7:55 AM

got this: Variable FIELD is undefined. So I changed all instances of "field" to "tee" and got the same error.

This is what I have now:
<cfif structKeyExists(form, "save")>
<cfloop item="tee" collection="#form#">
<cfif findNoCase("members_", tee)>
<cfset id = listLast(tee, "_")>
<cfquery datasource="#datasource#">
update members
set tee = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[tee]#">
where id = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
</cfquery>
</cfif>
</cfloop>
</cfif>

Comment 33 by Raymond Camden posted on 12/5/2013 at 8:39 AM

Where is the error being thrown? What line exactly.

Comment 34 by Cassi Bassolino posted on 12/6/2013 at 8:12 AM

Yes, running for [error]

Comment 35 by Cassi Bassolino posted on 12/6/2013 at 8:21 AM

here's my code at that point of the error:
Yes, running for <cfoutput>#field#</cfoutput>

Comment 36 by Cassi Bassolino posted on 12/6/2013 at 8:33 AM

It just occurred to me that I have another form running on this page. If I name this one clear_tees can that be used to differentiate from the other form?

<form name="clear_tees" action="teeassignments1.cfm" method="post">

Comment 37 by Raymond Camden posted on 12/6/2013 at 6:21 PM

field won't work anymore because you changed the cfloop. Do you know what this code does?

<cfloop item="tee" collection="#form#">

It says - for every key in Form, assign it to the variable tee and loop.

So when you changed my code from item="field" to item="tee", it means "field" as a variable no longer exists. My debug code should be changed to output tee.

To your final comment, the name of the form has nothing to do with how CF processes stuff.

Comment 38 by Cassi Bassolino posted on 12/9/2013 at 7:29 AM

I changed back to exactly what I had in my post above on 12-03-2013 at 1:09 PM and I still get the error "Variable FIELD is undefined" at the debug code.

Comment 39 by Cassi Bassolino posted on 12/9/2013 at 7:34 AM

Wait - that was my mistake. Now the debug codes gives me this:
Yes, running for FIELDNAMES
and the database does not update.

Comment 40 by Cassi Bassolino posted on 12/9/2013 at 7:39 AM

I submitted the form again and got this:
MEMBERS_119 - each time I submit it alternates between "fieldnames" and Members_(some id). Please advise.

Comment 41 by Cassi Bassolino posted on 12/9/2013 at 7:44 AM

Wait, I'm sorry - it's alternating between the records that are looped in the form - the 2 records that still have not cleared. Here's the html source - but the records do not return the value to "0" which is what I need.

<form name="cleartees" action="teeassignments1.cfm" method="post">

<input type="hidden" name="members_117" value="1" />

<input type="hidden" name="members_119" value="1a" />

<input type="submit" name="save" value="Clear ALL Tee Assignments" />
</form>

Comment 42 by Cassi Bassolino posted on 12/9/2013 at 7:50 AM

Wait (again) - I figured it out. sheesh. thanks for hanging in there with me.

Comment 43 by Raymond Camden posted on 12/10/2013 at 2:03 AM

Glad you got it working.

Comment 44 by john spellman posted on 7/26/2015 at 2:00 PM

Thanks for the information and sample. One more questiion if you dont mind. What about multiple fields in same table row?

Comment 45 (In reply to #44) by Raymond Camden posted on 7/27/2015 at 1:35 PM

That's possible too. You would just add the additional fields and update the code. Give it a shot first, and if you have trouble, share the code (via a Gist) along with any error you got.

Comment 46 (In reply to #14) by Dan Gregorio posted on 12/31/2015 at 1:45 PM

Hi Ray I seem to have an issue when updating 100 or over form fields. I get a 500 error. Works fine if less than 100 fields. I read a blog that said ColdFusion added a hot fix pin cf9 and above to accept up to 100 fields only. This is stored in the cal files in the library. Is there any solution without touching the xml file since i do not have access

Dsn

Comment 47 (In reply to #46) by Raymond Camden posted on 12/31/2015 at 3:23 PM

Not as far as I know. If you don't have access to disable that setting than you need to change hosts to one that will modify it for you or let you do it.

Comment 48 (In reply to #47) by Dan Gregorio posted on 12/31/2015 at 4:25 PM

Thanks Ray. Too bad they took away this ability. Do you think using cfgrid would have the same issue?

Comment 49 (In reply to #48) by Raymond Camden posted on 12/31/2015 at 4:30 PM

Well technically, I think it is assumed that most folks are using CF where they have access to the Admin. I think folks on hosts are becoming more and more rare. But thats just my 2 cents. ;)

I would avoid CFGRID like the plague. I've explained why in the past but let me know if you need a link. :)

Comment 50 by Jonathan Ikechukwu posted on 5/1/2018 at 11:27 AM

Thanks really helped me solved a complex task.

Comment 51 (In reply to #50) by Raymond Camden posted on 5/1/2018 at 1:25 PM

I'm glad this old post helped. I'm going to fix the formatting right now.