Followup to CFGRID MultiRow Post

This post is more than 2 years old.

A few weeks back I blogged about how you would make use of ColdFusion 901's new ability to have a multi-select cfgrid. As described in that post you need to make use of JavaScript to get the values selected. A reader asked me if I could "complete" the demo a bit and show actually performing database updates. Here is what I came up with.

To begin, we are going to make use of the cfartgallery database and the art table. It contains records for each piece of art. One of the columns is an isSold column that is a simple boolean. Let's make use of that:

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

<script> function fixMe() { obj = ColdFusion.Grid.getSelectedRows('SelectStuff'); var selected = ""; for(var i=0; i<obj.length; i++) { if(selected == "") selected = obj[i].ARTID; else selected += "," + obj[i].ARTID; } document.getElementById('selected').value = selected; return true; } </script>

<cfform name="mytest" method="post" onSubmit="return fixMe()"> <cfgrid name="SelectStuff" query="getart" format="html" width="400" height="250" multirowselect="true"> <cfgridcolumn name="artid" display="false"> <cfgridcolumn name="artname" header="Name"> <cfgridcolumn name="issold" header="Sold" type="boolean"> </cfgrid> <input type="hidden" name="selected" id="selected"> <cfinput type="submit" name="submit" value="Mark Sold"> </cfform>

Compared to the last example, this one uses a real database query and also makes use of cfgridcolumn to nicely format the data. Make special note of the boolean type for issold. Here is the result.

Ok, so for the most part, that's the exact same code as the previous entry, now let's look at the processing of the data.

<cfif structKeyExists(form, "selected") and len(form.selected)> <cfquery datasource="cfartgallery"> update art set issold = 1 where artid in (<cfqueryparam cfsqltype="cf_sql_integer" value="#form.selected#" list="true">) </cfquery> </cfif>

Pretty simple, right? Because our data is a list, and because cfqueryparam works well with lits, we can easily toggle the issold property based on the values selected in the grid. Here is the complete demo.

<cfif structKeyExists(form, "selected") and len(form.selected)> <cfquery datasource="cfartgallery"> update art set issold = 1 where artid in (<cfqueryparam cfsqltype="cf_sql_integer" value="#form.selected#" list="true">) </cfquery> </cfif>

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

<script> function fixMe() { obj = ColdFusion.Grid.getSelectedRows('SelectStuff'); var selected = ""; for(var i=0; i<obj.length; i++) { if(selected == "") selected = obj[i].ARTID; else selected += "," + obj[i].ARTID; } document.getElementById('selected').value = selected; return true; } </script>

<cfform name="mytest" method="post" onSubmit="return fixMe()"> <cfgrid name="SelectStuff" query="getart" format="html" width="400" height="250" multirowselect="true"> <cfgridcolumn name="artid" display="false"> <cfgridcolumn name="artname" header="Name"> <cfgridcolumn name="issold" header="Sold" type="boolean"> </cfgrid> <input type="hidden" name="selected" id="selected"> <cfinput type="submit" name="submit" value="Mark Sold"> </cfform>

<cfdump var="#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 Seth posted on 9/17/2010 at 7:59 AM

Ray, great example! Perhaps you want to add a query to first udpate all values to null, then run your update of the 'selected' items? That way if they uncheck a box that was previously checked it will stay unchecked after the update. You know, those high end art sales fall through from time to time ... See you at RIAUnleashed!

Comment 2 by prims posted on 9/17/2010 at 7:33 PM

Is it possible to do same in ColdFusion8? If so what do we need to change. Thanks

Comment 3 by Raymond Camden posted on 9/17/2010 at 7:37 PM

ColdFusion provides a JS API to get the grid object. You may be able to get it and modify it. Personally I'd call that too much work and would just do the grid "by hand" - but that requires learning Ext.

Comment 4 by Robert posted on 10/5/2011 at 8:42 PM

I just tried using the script fix, but when I dump the form scope, it still comes back as empty. Any ideas on what to look for?

Comment 5 by Raymond Camden posted on 10/7/2011 at 6:41 AM

Not offhand - sorry. I've been avoiding using the built in grid and instead make use of jqGrid.

Comment 6 by Michael Appenzellar posted on 10/15/2013 at 11:35 PM

The question is...is there a way to actually grab the all of the checkboxes that are selected in the "Sold" column? For example if you unchecked a few.

Comment 7 by Raymond Camden posted on 10/15/2013 at 11:37 PM

For the most part I do not recommend folks use cfgrid anymore. Sorry that isn't helpful.