AdobeStock_455007340

ColdFusion Ajax Tutorial 6: Editable Data Grids

Home » ColdFusion Ajax Tutorial 6: Editable Data Grids

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 , the new Ajax enabled HTML grid allows data to be updated right within the grid. When the is used in edit mode, column values may be edited as needed, and rows may be deleted. Unfortunately, the current implementation of the HTML 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 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 that supports data editing:










There are three changes in this (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 tags (or perform any other operations) to actually perform the updates. Here’s an example:

















UPDATE artists
SET #colname# = '#value#'
WHERE artistid = #ARGUMENTS.gridrow.artistid#






DELETE FROM artists
where artistid = #ARGUMENTS.gridrow.artistid#




The code uses a 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 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:












SELECT artistid, lastname, firstname, email
FROM artists

ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#






















UPDATE artists
SET #colname# = '#value#'
WHERE artistid = #ARGUMENTS.gridrow.artistid#






DELETE FROM artists
WHERE artistid = #ARGUMENTS.gridrow.artistid#





We’ll look at additional examples in the future.

38 responses to “ColdFusion Ajax Tutorial 6: Editable Data Grids”

  1. David Avatar
    David

    Great stuff – thanks Ben!

  2. Brett Avatar
    Brett

    Hi Ben,
    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}

  3. Alexander C. Tsoukias Avatar
    Alexander C. Tsoukias

    I am currently working on a cfgrid which contains comments of users to another user.
    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 😉

  4. Ashwin Avatar
    Ashwin

    Brett – we don’t support the double click event on the grid from within CF (though is something that we’ll consider for CF9). You can register a function to trigger on the double click event by getting the grid object using ColdFusion.Grid.getGridObject(<gridId>), which returns an object of type Ext.grid.Grid. See the documentation at http://extjs.com/deploy/ext/docs/index.html for details of how to register for the double click event on the grid – both row and cell double click events are supported.

  5. Ashwin Avatar
    Ashwin

    Alexander – we don’t provide a way for you to add more buttons to the grid. The easy way out would be to have regular HTML button outside the grid, which triggers a JavaScript function to do your approve logic. From within that function, you can get the value of columns in the selected grid row using ColdFusion.Bind.getElementValue(<gridId>, <columnName>).
    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.

  6. Alexander C. Tsoukias Avatar
    Alexander C. Tsoukias

    Thanks for your clarification, one more problem i am facing is with the related selects. I cannot keep the selected option selected after the form is submitted.
    <cfselect name="x_country_id" bind="cfc:cfc.register.getCountries()" bindonload="true" />

  7. bret Avatar
    bret

    Thanks Ashwin, I meant a single click sorry, I can’t seem to append the id to the url and can’t find any documentation on it?
    Many thanks,
    Brett

  8. Jeff Self Avatar
    Jeff Self

    Does CFGrid allow for selects inside the grid? I’m thinking about a grid that contains something like firstname, lastname, department where department would be a lookup to a department table.

  9. Christian Küpers Avatar
    Christian Küpers

    The data grid is very exiting; but how can I bind a grid to a cftree ?
    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.

  10. Mat Evans Avatar
    Mat Evans

    Loving the new CFGRID, although i’ve bumped into a little problem, and wondered if you had any insight into it?
    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

  11. Mat Evans Avatar
    Mat Evans

    Following on from my above post I have discovered a few things and developed a work around.
    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

  12. Peter Avatar
    Peter

    Really liking playing with the new grid.
    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

  13. Kruse Avatar
    Kruse

    What about an insert button?
    Is it possible to have an insert button in the grid just like the delete button?

  14. Keith Korry Avatar
    Keith Korry

    When I connect up my databas to the grid and I I cant get the grigd to page 10 per page. Am I missing somthing?, It works for your example though. heres the simple code Im using..
    <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>

  15. Jim Rising Avatar
    Jim Rising

    Ashwin,
    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/cfgrid.js
    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)

  16. Jim Rising Avatar
    Jim Rising

    as an adendum… if i remove the quotes from the variables for <gridid> and <column> (accountants, first_name) … i get:
    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

  17. Jim Rising Avatar
    Jim Rising

    Ray Camden confimed for me that it is in fact: ColdFusion.getElementValue(‘gridid’, ‘columnname’). Once I get it working, I’ll post.

  18. Jose Cabrita Avatar
    Jose Cabrita

    Hello,
    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.

  19. Jose Cabrita Avatar
    Jose Cabrita

    I am sorry, I got it. Application.cfc not configured properly.
    Thanks!
    Jose Cabrita.

  20. Christophe LIQUIERE Avatar
    Christophe LIQUIERE

    I have the same error than you :
    "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.

  21. Chris Rogers Avatar
    Chris Rogers

    Ben I found that the code as displayed didn’t work for me however when I modified the code example to add single quotations around the var in the WHERE clauses then the code work as described… was this a misprint in the example…???
    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>

  22. Jose Cabrita Avatar
    Jose Cabrita

    Regarding "widget: CFGRID: Response is empty" error:
    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.

  23. John Farrar Avatar
    John Farrar

    OK… another use asked here and the attributes appear to be in CFEclipse… where is the insert attribute examples? It seems looking at the docs this feature was overlooked.

  24. Matt Avatar
    Matt

    Hi Ben,
    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

  25. Carla Scepaniak Avatar
    Carla Scepaniak

    I know the article is old; however, I have a problem. I have an editable cfgrid (html format) which displays people; one column holds a rank, and that is an id column which ties to a lookup table that holds the corresponding rank names. The grid calls a cfc for the query. In the rank column, I want to display a select list which shows all available ranks, but I want the rank for each individual preselected in that drop-down listing–so the user can select/edit a person’s rank by its wording, but the rankid number is actually the thing which gets passed.
    So, has anyone figured out how to have a query-populated grid which includes a select list column based upon a lookup query? I really thought I saw somewhere one time where a guy figured it out, but now I can’t locate it anymore. Thanks if anyone can help!

  26. Landon Avatar
    Landon

    Sorry to bump an oooold article, but I was hoping someone could help me figure out why this code is not working on my system. I’m not getting any errors, and the AJAX debug window is showing everything as kosher as well, including showing the records that should be returned by the getArtists method. However, the CFGRID in the CFWINDOW never returns any records. Any idea what could be the issue here?

  27. Landon Avatar
    Landon

    Nevermind. Just found the problem. A: use both FireFox and IE when trying to debug your CFGRID. You might get different results. Doing so, in IE, I found I was getting the "window:global: Exception thrown and not caught" error. Turns out this was due to our Application.cfm having a single html comment at the top of the file. Pulling that out, the bind works fine, and thus the example code you provided, Ben, works fine.

  28. Jennifer Avatar
    Jennifer

    If you’re getting CFGRID: Response is empty, and I know this sounds like a no-brainer, check your SQL statement carefully. That was my issue.

  29. dino Avatar
    dino

    This is a great example. Everything work! If I want to change the edit button to other text, how can I do it? Right now the edit button is just a circuling star.

  30. Elizabeth Avatar
    Elizabeth

    As always your blog was very helpful. I am running CF9 and LOVE the new features for cfgrid! There isn’t much out there regarding cfgrid for Coldfusion 9 yet….wondering if it is possible to add log in/log out capability within cfgrid using CF9?

  31. Francesco Avatar
    Francesco

    How can I update a checkbox? I have a cfgridcolumn set to type= boolean, but the component gives me error when update…

  32. Francesco Avatar
    Francesco

    How to update cfgridcolumn type boolean?

  33. Adam Cameron Avatar
    Adam Cameron

    Indeed, yes: how about checkboxes?
    The only way I’ve been able to get a boolean field to display a checkbox is to make the grid editable, and even then it still displays "true" or "false" until one clicks on the cell twice.
    Am I doing something wrong, or is this a bug?

    Adam

  34. Melvin Thompson Avatar
    Melvin Thompson

    I setup my editable grid to accept updates just as setup
    here and I’m getting a database error: "Error
    Executing database query"
    Just not working, but the delete works with no problem.
    mt

  35. KJ Avatar
    KJ

    I have an bindable cfgrid that updates like it should (I see the red triangle). I need to know how to refresh the grid to show updated information based on the update.

  36. KJ Avatar
    KJ

    Love the example but how would you refresh the grid or re render the grid after the update? I know the changes are automatic after the edit but if the edit affects other columns how would you refresh the grid after the update?

  37. Zach L. Avatar
    Zach L.

    I know the topic is a bit old, but I had to delve into the
    ColdFusion.getElementValue(‘gridid’,’columnname’) issue mentioned
    in some of the above comments.
    Looking at the getElementValue function, it has 3 parameters, not two.
    From the ColdFusion 8 Documentation:
    ColdFusion.getElementValue(elementId [, formId, attributeName])
    I found that adding the ‘formId’ parameter solved a problem that
    I was having when targeting a cell of the cfgrid. So try:
    ColdFusion.getElementValue(‘gridName’, ‘formName’, ‘columnName’);
    Hope that saved somebody some digging.

  38. jack Avatar
    jack

    Hi Ben, I need to create a purchase order
    that gives the user the ability to add multiple items to it, The user enters the data, clicks on the "Add Another" Button, or "enter"
    , and a new row is added to the form and the previous data entered is kept intact, how can do this?

Leave a Reply