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>
Archived Comments
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/
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?)
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".
@TomK: Yes, just trying to keep it as simple as possible.
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.
Depending on what you're actually doing, the easiest way to do this could just be cfgrid & cfgridupdate.
I'd disagree - at least for the newbie user.
*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.
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!
Thanks Raymond. It is awesome.
What about updating multiple records with multiple fields, like ARTNAME and DESCRIPTION?
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?
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!
I've added it to my list of blog ideas. So it is recorded and will be eventually written.... one day.
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!
Here is the followup: http://tinyurl.com/yb34z3o
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.
I'd have to see your code. I assume you made a new INPUT field with the name regdate_#id#.
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>
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.
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
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.
Thank you Ray! This just saved me hours....
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">
Please disregard the question above. Problem between the keyboard and the chair.
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
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>
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>
You didn't say *how* it failed.
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.
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.
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>
Where is the error being thrown? What line exactly.
Yes, running for [error]
here's my code at that point of the error:
Yes, running for <cfoutput>#field#</cfoutput>
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">
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.
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.
Wait - that was my mistake. Now the debug codes gives me this:
Yes, running for FIELDNAMES
and the database does not update.
I submitted the form again and got this:
MEMBERS_119 - each time I submit it alternates between "fieldnames" and Members_(some id). Please advise.
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>
Wait (again) - I figured it out. sheesh. thanks for hanging in there with me.
Glad you got it working.
Thanks for the information and sample. One more questiion if you dont mind. What about multiple fields in same table row?
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.
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
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.
Thanks Ray. Too bad they took away this ability. Do you think using cfgrid would have the same issue?
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. :)
Thanks really helped me solved a complex task.
I'm glad this old post helped. I'm going to fix the formatting right now.