Previously we looked at the new
ColdFusion 8 data grid and how to populate that control using asynchronous calls back to a ColdFusion Component. In that example the CFC contained a single method that returned a page of data as requested by the data grid.
Like the previous incarnations of <CFGRID>, the new Ajax enabled HTML grid allows data to be updated right within the grid. When the <CFGRID> is used in edit mode, column values may be edited as needed, and rows may be deleted. Unfortunately, the current implementation of the HTML <CFGRID> does not support inserting new rows. This is a pretty serious limitation, and one that we'll hopefully address in the future - for now you'll need to use another form to add new rows.
You will recall that <CFGRID> requests data as needed by making calls to a CFC method specified in the bind attribute. To process edits a second CFC method is needed, and it must be passed to the onchange attribute. Here is a modified <CFGRID> that supports data editing:
<cfwindow initshow="true" center="true"
width="430" height="340" title="Artists">
<cfform>
<cfgrid name="artists"
format="html"
pagesize="10"
striperows="yes"
selectmode="edit"
delete="yes"
bind="cfc:artists.getArtists({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection})"
onchange="cfc:artists.editArtist({cfgridaction},
{cfgridrow},
{cfgridchanged})">
<cfgridcolumn name="is" display="false" />
<cfgridcolumn name="lastname" header="Last Name" width="100"/>
<cfgridcolumn name="firstname" header="First Name" width="100"/>
<cfgridcolumn name="email" header="E-Mail" width="200"/>
</cfgrid>
</cfform>
</cfwindow>
There are three changes in this <CFGRID> (compared to the grid created previously). First of all, selectmode="edit" puts the data grid in edit mode. This allows editing, but not deleting. To allow rows to be deleted, delete="yes" is also specified. And finally, a CFC method is specified in the onchange attribute. When invoked (upon an edit or a delete) three arguments will be passed, the action (U for update or D for delete), the row being changed, and the changes (only populated for updates, and not for deletes).
The specified CFC has to accept these three arguments, and returns no data. Within the CFC you can use <CFQUERY> tags (or perform any other operations) to actually perform the updates. Here's an example:
<!--- Edit an artist --->
<cffunction name="editArtist" access="remote">
<cfargument name="gridaction" type="string" required="yes">
<cfargument name="gridrow" type="struct" required="yes">
<cfargument name="gridchanged" type="struct" required="yes">
<!--- Local variables --->
<cfset var colname="">
<cfset var value="">
<!--- Process gridaction --->
<cfswitch expression="#ARGUMENTS.gridaction#">
<!--- Process updates --->
<cfcase value="U">
<!--- Get column name and value --->
<cfset colname=StructKeyList(ARGUMENTS.gridchanged)>
<cfset value=ARGUMENTS.gridchanged[colname]>
<!--- Perform actual update --->
<cfquery datasource="#THIS.dsn#">
UPDATE artists
SET #colname# = '#value#'
WHERE artistid = #ARGUMENTS.gridrow.artistid#
</cfquery>
</cfcase>
<!--- Process deletes --->
<cfcase value="D">
<!--- Perform actual delete --->
<cfquery datasource="#THIS.dsn#">
DELETE FROM artists
where artistid = #ARGUMENTS.gridrow.artistid#
</cfquery>
</cfcase>
</cfswitch>
</cffunction>
The code uses a <CFSWITCH> to process a gridaction of U (update) or D (delete). For updates, argument gridchanged will be a structure containing an element for each column changed, the element name is the column name and the element value is the new value. Each column is updated individually, if a user makes three edits to the same row in the data grid the this method will be called three times, once for each row. As such, for updates, gridchanged only ever contains a single element, and so the code extracts the column name and value and saves them to local variables. These variables are then used in a <CFQUERY> to perform the actual update, using the primary key in the passed row (ARGUMENTS.gridrow) for the SQL WHERE clause. Deletes are processed similarly, with only the primary key needed.
Here is the complete artists.cfc, with both the bind and onchange methods:
<cfcomponent output="false">
<cfset THIS.dsn="cfartgallery">
<!--- Get artists --->
<cffunction name="getArtists" access="remote" returntype="struct">
<cfargument name="page" type="numeric" required="yes">
<cfargument name="pageSize" type="numeric" required="yes">
<cfargument name="gridsortcolumn" type="string" required="no" default="">
<cfargument name="gridsortdir" type="string" required="no" default="">
<!--- Local variables --->
<cfset var artists="">
<!--- Get data --->
<cfquery name="artists" datasource="#THIS.dsn#">
SELECT artistid, lastname, firstname, email
FROM artists
<cfif ARGUMENTS.gridsortcolumn NEQ ""
and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
</cfif>
</cfquery>
<!--- And return it as a grid structure --->
<cfreturn QueryConvertForGrid(artists,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</cffunction>
<!--- Edit an artist --->
<cffunction name="editArtist" access="remote">
<cfargument name="gridaction" type="string" required="yes">
<cfargument name="gridrow" type="struct" required="yes">
<cfargument name="gridchanged" type="struct" required="yes">
<!--- Local variables --->
<cfset var colname="">
<cfset var value="">
<!--- Process gridaction --->
<cfswitch expression="#ARGUMENTS.gridaction#">
<!--- Process updates --->
<cfcase value="U">
<!--- Get column name and value --->
<cfset colname=StructKeyList(ARGUMENTS.gridchanged)>
<cfset value=ARGUMENTS.gridchanged[colname]>
<!--- Perform actual update --->
<cfquery datasource="#THIS.dsn#">
UPDATE artists
SET #colname# = '#value#'
WHERE artistid = #ARGUMENTS.gridrow.artistid#
</cfquery>
</cfcase>
<!--- Process deletes --->
<cfcase value="D">
<!--- Perform actual delete --->
<cfquery datasource="#THIS.dsn#">
DELETE FROM artists
WHERE artistid = #ARGUMENTS.gridrow.artistid#
</cfquery>
</cfcase>
</cfswitch>
</cffunction>
</cfcomponent>
We'll look at additional <CFGRID> examples in the future.
With the columns, how do I do it so if I double click on one it opens up a new edit page with a primary key in the url?
e.g
double-click = http://mysite/editrecord?id={id}
The delete button is great, but i am in need of another button named: Approve.
My question therefore is... am I able to add custom buttons? Specifically to the CFC it will be great cause we can specify which letter to send for our custom button, instead of just having U and D.
I know we're asking for much some times, so let me just say this version of coldfusion can make us proud when arguing ;-)
Alternatively, though this will be more work, you can get the underlying grid object, as I've outlined in the last comment, and add buttons to it's toolbar. See the Ext API (referenced above) documentation for details.
<cfselect name="x_country_id" bind="cfc:cfc.register.getCountries()" bindonload="true" />
Many thanks,
Brett
I want to expand the example 5 to a tree combined with a grid to display the items.
I can´t find any information on that. Where do others look after such thing. The adobe or MM - docs are often very small.
I have a grid with over 20 columns, some editable, some not. The grid being this wide, puts some of the columns off the visible screen when rendered. Data all loads fine, and all text fields that are editable are all perfect. The problem appears when you try to edit a drop down box field that was rendered in an area off the visible screen when the grid first appears. In IE7, it places the drop down options in a position to the left of the actual cell, and in FF it scrolls you back to the far left of the screen and no edit features appear.
I just wondered if you had seen this behaviour before, or if there is something in our page that is causing the problem. I'm also currently posting in the EXT forums to see if this is a problem they have.
Any help much appreciated.
Mat Evans
It seems the Ext grid is designed to work within it's own scrolling div, with the class .x-grid-container, and a div under that with the class .x-grid .
In CF, this div is spread out accross the whole page, whereas is should really be the width of the viewable screen. To get round the problem of breaking the drop downs, you have to add some class info right at the end of your header.
.x-grid-container {
max-width:1000px;
width:1000px !important;
}
.x-grid {
width:1000px;
}
These seem to solve the problem I have been having by setting the size of the viewable section of the grid to the above values. The !important is for IE6.
Hope that helps for anyone who finds themselves in the same situation.
Mat Evans
One strange thing is that I can't get the pagesize working? Doesn't appear in the suggest or livedocs any more and when i copy the code above I just get a normal grid with no pagination?
Any ideas - this is my first few tries so maybe I'm missing something
Cheers
Peter
Is it possible to have an insert button in the grid just like the delete button?
<cfform>
<cfinput name="searchString" />
<cfinput type="button" name="searchBtn" value="Search" onclick="" />
<cfgrid query="R1"
name="artGrid"
format="html"
pagesize="10"
striperows="yes"
selectmode="edit"
highlighthref="yes"
delete="yes"
sort="true">
<cfgridcolumn name="id" header="First Name" width="100" display="no"/>
<cfgridcolumn name="weather" header="weather" width="100"/>
<cfgridcolumn name="cities" header="cities" width="100"/>
<cfgridcolumn name="province" header="province" width="100"/>
</cfgrid>
</cfform>
you had said to use ColdFusion.Bind.getElementValue(<gridid>, <column>) in order to add external (html/javascript) buttons to the grid, but i was unable to get it to work using this... did you mean ColdFusion.Bind.getBindElementValue() ? or ColdFusion.getElementValue()? i found both of these in the DOM, but was unable to get either to work using this:
<script type="text/javascript" language="javascript">
function myFunction()
{
var myVar = ColdFusion.Bind.getBindElementValue('accountants', 'first_name')
alert(myVar)
}
</script>
<cfgrid name="accountants" format="html" pagesize="5" striperows="true" bind="cfc:removed.to.protect.the.innocent" delete="true" selectmode="edit" onchange="cfc:removed.to.protect.the.innocent">
<!--- <cfgridcolumn name="checked" header="Delete" type="boolean"> --->
<cfgridcolumn name="app_advisorInfo_id" display="no" >
<cfgridcolumn name="app_advisorType_id" >
<cfgridcolumn name="first_name">
<cfgridcolumn name="last_name">
<!---
<cfgridcolumn name="Relationship">
<cfgridcolumn name="Specialty">
<cfgridcolumn name="Agency">
<cfgridcolumn name="address">
<cfgridcolumn name="address2">
<cfgridcolumn name="City">
<cfgridcolumn name="State">
<cfgridcolumn name="zipcode">
<cfgridcolumn name="phone">
<cfgridcolumn name="country">
<cfgridcolumn name="fax">
<cfgridcolumn name="notes">
<cfgridcolumn name="fiduciaryType">
<cfgridcolumn name="cs_user_id">
<cfgridcolumn name="isPrimary">
--->
</cfgrid>
<input type="button" name="approve" value="approve" onclick="myFunction()">
this is the error i get:
_6b has no properties
http://127.0.0.1:8500/CFIDE/scripts/ajax/package/c...
Line 218
_6b has no properties
_cf_getAttribute(undefined)cfgrid.js (line 218)
getBindElementValue("accountants", "first_name", undefined, undefined, undefined)cfajax.js (line 334)
myFunction()index.cfm (line 277)
onclick(click clientX=0, clientY=0)
accountants is not defined
myFunction()index.cfm (line 277)
onclick(click clientX=0, clientY=0)
it seems that the grid ID is some random ID every time? how do i obtain that?
-jim
Excellent blog, I love Ajax.
Now, I am trying to test your code but I am getting "CFGRID: Response is empty" .
Here is Ajax logger:
error:widget: CFGRID: Response is empty
info:http: CFC invocation response:
info:widget: Creating window: cf_window1187741966251
info:widget: Created grid, id: artists
info:http: HTTP GET /rootH111689/test/artists.cfc?method=getArtists&returnFormat=json&argumentCollection=%7B%22page%22%3A1%2C%22pageSize%22%3A10%2C%22gridsortcolumn%22%3A%22%22%2C%22gridsortdir%22%3A%22%22%7D&_cf_nodebug=true&_cf_nocache=true&_cf_clientid=15B305EB130F54D0CEC3251443274B76&_cf_rc=0
info:http: Invoking CFC: /rootH111689/test/artists.cfc , function: getArtists , arguments: {"page":1,"pageSize":10,"gridsortcolumn":"","gridsortdir":""}
info:LogReader: LogReader initialized
info:global: Logger initialized
Thanks!
Jose.
Thanks!
Jose Cabrita.
"widget: CFGRID: Response is empty"
You say you've got problems with your Application.cfc, could you explain me what you've done for correcting this error?
Thanks.
Modified example below...
<!--- Process gridaction --->
<cfswitch expression="#ARGUMENTS.gridaction#">
<!--- Process updates --->
<cfcase value="U">
<!--- Get column name and value --->
<cfset colname=StructKeyList(ARGUMENTS.gridchanged)>
<cfset value=ARGUMENTS.gridchanged[colname]>
<!--- Perform actual update --->
<cfquery datasource="#THIS.dsn#">
UPDATE artists
SET #colname# = '#value#'
WHERE artistid = '#ARGUMENTS.gridrow.artistid#' <!--- Added Single Quotes around var --->
</cfquery>
</cfcase>
<!--- Process deletes --->
<cfcase value="D">
<!--- Perform actual delete --->
<cfquery datasource="#THIS.dsn#">
DELETE FROM artists
where artistid = '#ARGUMENTS.gridrow.artistid#' <!--- Added Single Quotes around var --->
</cfquery>
</cfcase>
</cfswitch>
</cffunction>
For some reason OnRequest function and ajax or remoting services don't work together, so you have to block on request. In my case, I am calling methods from CFC's so I include this lines at the end of onRequestStart in Application.cfc:
<cffunction name = "onRequestStart">
<cfparam name="CGI.REMOTE_ADDR" default="">
.
.
.
<cfif CGI.SCRIPT_NAME Contains "cfc">
<cfset temp = structDelete(this,'onRequest')>
</cfif>
<cfreturn true>
</cffunction>
I hope this will help you.
Jose.
Great article on CFGRID. I was testing out some theories using a drop down box. I changed one line to:
<cfgridcolumn name="email" header="E-Mail" values="Force,Yes,No"/>
I can go in and edit using the drop down. when I select Force and update, everything works fine. When I select Yes, and update, it returns true. When I select No, and update, it returns false. Is there anything I can do to get around this?
Thanks! Matt