The Largest Repository of ColdFusion Knowledge in The World for More Than 12 Years

ColdFusion on Ulitzer

Subscribe to ColdFusion on Ulitzer: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get ColdFusion on Ulitzer: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


CFDJ Authors: AppDynamics Blog, Michael Kopp, Tad Anderson, Bob Gourley, Jayaram Krishnaswamy

Related Topics: RIA Developer's Journal, ColdFusion on Ulitzer

RIA & Ajax: Article

ColdFusion AJAX Tutorial 6: Editable Data Grids

Like the previous incarnations of, the new AJAX enabled HTML grid allows data to be updated right within the grid

Ben Forta's Blog

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.

Comments (0) | Trackbacks (0) | Print | Send | del.icio.us | Linking Blogs

More Stories By Ben Forta

Ben Forta is Adobe's Senior Technical Evangelist. In that capacity he spends a considerable amount of time talking and writing about Adobe products (with an emphasis on ColdFusion and Flex), and providing feedback to help shape the future direction of the products. By the way, if you are not yet a ColdFusion user, you should be. It is an incredible product, and is truly deserving of all the praise it has been receiving. In a prior life he was a ColdFusion customer (he wrote one of the first large high visibility web sites using the product) and was so impressed he ended up working for the company that created it (Allaire). Ben is also the author of books on ColdFusion, SQL, Windows 2000, JSP, WAP, Regular Expressions, and more. Before joining Adobe (well, Allaire actually, and then Macromedia and Allaire merged, and then Adobe bought Macromedia) he helped found a company called Car.com which provides automotive services (buy a car, sell a car, etc) over the Web. Car.com (including Stoneage) is one of the largest automotive web sites out there, was written entirely in ColdFusion, and is now owned by Auto-By-Tel.

Comments (2)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.