Ask a Jedi followup - updating multiple records at once

This post is more than 2 years old.

Here is a quick follow up to a blog entry I did back in February: Ask a Jedi: Updating multiple records at once. I had more than one person ask me to modify the code from that example to handle multiple fields per record. The template I had created only allowed you to edit the name. Here is a simple example that builds upon the first version and allows you to edit the description and price as well.

First, let's modify the display a bit to show the additional fields:

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

<form action="test2.cfm" method="post"> <table> <tr> <th>Name</th> <th>Description</th> <th>Price</th> </tr> <cfloop query="getArt"> <tr valign="top"> <cfoutput> <td><input type="text" name="art_#artid#" value="#artname#" /></td> <td><textarea name="desc_#artid#">#description#</textarea></td> <td><input type="text" name="price_#artid#" value="#price#"></td> </cfoutput> </tr> </cfloop> </table> <input type="submit" name="save" value="Save" /> </form>

Compared to the first version, this one gets additional columns in the query and displays them in the table. Notice that I used a similar naming scheme for my two new columns: desc_X and price_X. X represents the ID (primary key) of the art record.

Saving these new columns just requires a bit more code:

<cfif structKeyExists(form, "save")> <cfloop item="field" collection="#form#"> <cfif findNoCase("art_", field)> <cfset artid = listLast(field, "_")> <cfset desc = form["desc_" & artid]> <cfset price = form["price_" & artid]> <cfquery datasource="cfartgallery"> update art set artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">, description = <cfqueryparam cfsqltype="cf_sql_longvar" value="#desc#">, price = <cfqueryparam cfsqltype="cf_sql_numeric" value="#price#" scale="2"> where artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#"> </cfquery> </cfif> </cfloop> </cfif>

Since I already grabbed the ID record from the art_X field, I could then use that to grab both the description and price values. I then just added those two fields to the query. The code could use a bit of validation. In the past when I've done something like this I've used an array of results. So for each update I could display which saved ok and which did not, or perhaps just output the rows that didn't update properly. You could also add jQuery validation to the form in about five minutes as well.

Shoot - I just tried and it took me 6 minutes. Dang. Here is the full version with jQuery validation added.

<cfif structKeyExists(form, "save")> <cfloop item="field" collection="#form#"> <cfif findNoCase("art_", field)> <cfset artid = listLast(field, "_")> <cfset desc = form["desc_" & artid]> <cfset price = form["price_" & artid]> <cfquery datasource="cfartgallery"> update art set artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">, description = <cfqueryparam cfsqltype="cf_sql_longvar" value="#desc#">, price = <cfqueryparam cfsqltype="cf_sql_numeric" value="#price#" scale="2"> where artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#"> </cfquery> </cfif> </cfloop> </cfif>

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

<html>

<head> <script src="/jquery/jquery.js"></script> <script src="/jquery/jquery.validate.js"></script> <script> $(document).ready(function(){ $("#myForm").validate({ rules: { <cfoutput query="getArt"> art_#artid#:"required", desc_#artid#:"required", price_#artid#: { required:true, number:true, min:0 }, </cfoutput> } }); }); </script> </head>

<body>

<form action="test2.cfm" method="post" id="myForm"> <table> <tr> <th>Name</th> <th>Description</th> <th>Price</th> </tr> <cfloop query="getArt"> <tr valign="top"> <cfoutput> <td><input type="text" id="art_#artid#" name="art_#artid#" value="#artname#" /></td> <td><textarea id="desc_#artid#" name="desc_#artid#">#description#</textarea></td> <td><input type="text" id="price_#artid#" name="price_#artid#" value="#price#"></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 Henry Ho posted on 9/28/2009 at 10:56 PM

Isn't cfloop inside cfquery more efficient?

Comment 2 by Raymond Camden posted on 9/28/2009 at 11:03 PM

Not that I'm aware of.

Comment 3 by Henry Ho posted on 9/28/2009 at 11:05 PM
Comment 4 by Raymond Camden posted on 9/28/2009 at 11:09 PM

Oh. Well sure. It really isn't pertinent to this blog entry though. ;) And frankly, it would only help in a case where you had so many inserts that you probably wouldn't want to be editing them on one page anyway, right?

Comment 5 by PC posted on 9/29/2009 at 7:51 PM

Excellent...this did the trick....thank you so much for the code and the explanation.

Comment 6 by Rick Clark posted on 10/26/2009 at 12:01 AM

Hi Ray, I enjoy reading your blogs and have picked up several tips. I tried using this blog to update my rows. Unfortunately, I am using access (I know - at max I probably have only thirty rows), and your code is for SQL. How hard is it to convert this to work on access.

Comment 7 by Raymond Camden posted on 10/26/2009 at 12:02 AM

Did you try it? It should work fine w/ Access.

Comment 8 by Rick Clark posted on 10/26/2009 at 12:34 AM

I get the following error:

Element points_2 is undefined in a Java object of type class coldfusion.filter.FormScope.

Comment 9 by Raymond Camden posted on 10/26/2009 at 12:36 AM

That's not an Access error, but a CF error. I assume you modified my code a bit?

Comment 10 by Rick Clark posted on 10/26/2009 at 1:11 AM

Here is the code in question:

<cfif structKeyExists(form, "save")>
<cfloop item="field" collection="#form#">
<cfif findNoCase("art_", field)>
<cfset artid = listLast(field, "_")>
<cfset points = form["points_" & artid]>
<cfquery datasource="artgallery">
UPDATE art SET
artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">,
points = <cfqueryparam cfsqltype="cf_sql_varchar" value="#points#">'
where artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#">
</cfquery>
</cfif>
</cfloop>
</cfif>

<cfquery name="getArt" datasource="artgallery">
select artid, artname, points
from art
</cfquery>

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

Comment 11 by Raymond Camden posted on 10/26/2009 at 1:17 AM

Looks fine to me. You could add a cfdump of the form scope to see if anything is amiss.

Comment 12 by Rick Clark posted on 10/26/2009 at 1:48 AM

Found it, I had a misplaced ' behind a bracket.

Thanks,
Rick

Comment 13 by Chris posted on 2/18/2011 at 11:43 PM

Anyone done this with cfscript (scripted query)?

Comment 14 by Chris posted on 2/19/2011 at 1:43 AM

Nm, I figured it out...stupid mistake.

Comment 15 by instinct posted on 10/5/2011 at 8:10 PM

Hi Ray, Please could you help on multi-data insert example?

Comment 16 by Raymond Camden posted on 10/7/2011 at 8:24 PM

Help how?

Comment 17 by Misty posted on 10/22/2012 at 5:31 PM

Hi ray,Working with Multiple updates, I encoutered a checkbox, so using ur code and i tried little modifications but t does seems to like the checkbox if it not defined, it updates all records to 1 or sometimes it does not update the checkbox code

please check the code

http://pastebin.com/bNwju7bG

Comment 18 by Raymond Camden posted on 10/22/2012 at 6:51 PM

Misty, I'd be ok with helping you fix this in general, but looking over your code and calling out the specific issue is a bit much for this thread here.

When working with checkboxes, you need to use the ColdFusion isDefined function. If it isn't defined, it means the checkbox wasn't checked. If it is, then, well, it was.