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:
<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><cfquery name="getArt" datasource="cfartgallery"> select artid, artname, issold from art </cfquery>
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.
<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#"><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>