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#">
Archived Comments
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!
Is it possible to do same in ColdFusion8? If so what do we need to change. Thanks
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.
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?
Not offhand - sorry. I've been avoiding using the built in grid and instead make use of jqGrid.
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.
For the most part I do not recommend folks use cfgrid anymore. Sorry that isn't helpful.