Ben Forta

AdobeStock_455007340

ColdFusion Ajax Tutorial 3: Live Data Grids

The data grid is critical to all sorts of development on all sorts of platforms and in all sorts of languages. ColdFusion has supported data grids since ColdFusion 2 – first a Java applet, then a Flash control, and in ColdFusion 8 we’ve added an HTML data grid that can be pre-populated with data, or which can be used to display live data loaded asynchronously.
The basic pre-populated data grid functions much like the of old, pass it a query and it displays the data. Here is an example (which uses the example tables that come with ColdFusion):

SELECT artistid, lastname, firstname, email
FROM artists
ORDER BY lastname, firstname








Here a retrieves data, and displays the results. The grid is straight client side HTML with CSS and JavaScript. You have control over look and feel including size, columns, colors, and fonts. And users can sort up and down, resize columns, and more.
The new also supports Ajax type interaction, where data is not pre-populated, but is asynchronously loaded as needed. Here is a sample data grid:









This data grid is displayed in a window created using the new tag (for no good reason other than I like the look of it). The data grid itself has no passed query, instead, it is bound to artists.cfc. When the data grid needs data it fires an asynchronous call to the getArtists() method in artists.cfc, and passes it four pieces of information: the current page, the page size (specified previously in the pagesize attribute), and the column being sorted on and sort direction (if the user opts to sort data). thus requests data, and simply displays whatever ColdFusion returns, automatically supporting paging (assuming there are enough rows to so warrant).
Now for the CFC:












SELECT artistid, lastname, firstname, email
FROM artists

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






The getArtists returns a structure (containing data in the format required by the data grid), and accepts four arguments, the same four arguments passed in the client side bind attribute. The first two are always passed by the client, and so they are required. The latter two are only passed if the user clicks on a column header to sort the data, and so those arguments are not required and default to “”. performs the actual data retrieval, conditionally sorting the data if sorting is required. And finally, the new QueryConvertForGrid() function extracts the desire data subset (using the passed page and pagesize values) and formats it as a structure which is returned to the data grid.
This is a basic example, and we’ll look at additional functionality in future posts.

56 responses to “ColdFusion Ajax Tutorial 3: Live Data Grids”

  1. Chad Fraser Avatar
    Chad Fraser

    For those of you that want to use cfgrid and pass a complex string of URL parameters to use with hrefkey.
    In this example I am quering a department table in Oracle. I found that if you contruct your URL parameters in the SQL you can typically accomplish the goal of passing a complex URL string.
    See the cfquery in the component below:
    <!—-report.cfc—>
    <cfcomponent>
    <cffunction name="getDepartment" access="remote" returntype="any">
    <cfargument name="page" type="numeric" required="yes">
    <cfargument name="pageSize" type="numeric" required="yes">
    <cfargument name="gridsortcolumn" type="string" required="no" default="">
    <cfargument name="gridsortdirection" type="string" required="no" default="">
    <cfset rsData = "">
    <cfif ARGUMENTS.gridsortcolumn EQ "">
    <cfset ARGUMENTS.gridsortcolumn = "DEPTNO" />
    <cfset ARGUMENTS.gridsortdirection = "ASC" />
    </cfif>
    <!—Queries to be found in I:Dashboard.—>
    <cfquery name="rsData" datasource="#dsn#">
    SELECT deptName, deptNo ,
    <!—Create URL String in SQL (Oracle Example).—>
    TO_CHAR(‘&deptNo=’ || deptNo || ‘&deptName=’ || deptName) AS hrefKey
    <1—End.—>
    FROM swweb.tbl_department
    <cfif ARGUMENTS.gridsortcolumn NEQ "" OR ARGUMENTS.gridsortdirection NEQ "">
    ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdirection#
    </cfif>
    </cfquery>
    <cfif ARGUMENTS.export EQ "">
    <cfif rsData.RecordCount NEQ 0>
    <cfreturn QueryConvertForGrid(rsData, ARGUMENTS.page, ARGUMENTS.pageSize)>
    <cfelse>
    <cfreturn QueryConvertForGrid(rsData, 0, 0)>
    </cfif>
    <cfelse>
    <cfreturn rsData>
    </cfif>
    </cffunction>
    </cfcomponent>
    <!—test.cfm—>
    <cfparam name="URL.deptNo" default="0">
    <cfparam name="URL.deptName" default="None">
    <cfparam name="FORM.pageDept" default="1">
    <cfparam name="FORM.pageSizeDept" default="7">
    <cfset VAR.gridWidth = 800>
    <cfset VAR.gridcolBGColor = ‘##CCCCCC’>
    <cfoutput>#URL.deptNo# – #URL.deptName#</cfoutput><br /><br />
    <div id="reportTitle">Departments</div>
    <div id="reportContainer">
    <cfform>
    <cfgrid name="sales_dept_vs_prior" format="html" pagesize="#FORM.pageSizeDept#" width="#VAR.gridWidth#" bind="cfc:cfc.report.getDepartment({cfgridpage}, {cfgridpagesize}, {cfgridsortcolumn}, {cfgridsortdirection})" striperows="yes" selectonload="false" bindonload="yes">
    <!—Must include hrefKey column like this.—>
    <cfgridcolumn name="hrefKey" display="no">
    <!—Add href & hrefKey below.—>
    <cfgridcolumn name="DEPTNO" header="Dept. No" width="50" href="#CGI.SCRIPT_NAME#?taskID=HELLOWORLD" hrefkey="hrefKey">
    <cfgridcolumn name="DEPTNAME" header="Name" width="70" bgcolor="#VAR.gridcolBGColor#">
    </cfgrid>
    </cfform>
    </div>

    Reply
  2. Chad Fraser Avatar
    Chad Fraser

    For those of you that want to use cfgrid and pass a complex string of URL parameters to use with hrefkey.
    In this example I am quering a department table in Oracle. I found that if you contruct your URL parameters in the SQL you can typically accomplish the goal of passing a complex URL string.
    See the cfquery in the component below:
    <!—-report.cfc—>
    <cfcomponent>
    <cffunction name="getDepartment" access="remote" returntype="any">
    <cfargument name="page" type="numeric" required="yes">
    <cfargument name="pageSize" type="numeric" required="yes">
    <cfargument name="gridsortcolumn" type="string" required="no" default="">
    <cfargument name="gridsortdirection" type="string" required="no" default="">
    <cfset rsData = "">
    <cfif ARGUMENTS.gridsortcolumn EQ "">
    <cfset ARGUMENTS.gridsortcolumn = "DEPTNO" />
    <cfset ARGUMENTS.gridsortdirection = "ASC" />
    </cfif>
    <!—Queries to be found in I:Dashboard.—>
    <cfquery name="rsData" datasource="#dsn#">
    SELECT deptName, deptNo ,
    <!—Create URL String in SQL (Oracle Example).—>
    TO_CHAR(‘&deptNo=’ || deptNo || ‘&deptName=’ || deptName) AS hrefKey
    <1—End.—>
    FROM swweb.tbl_department
    <cfif ARGUMENTS.gridsortcolumn NEQ "" OR ARGUMENTS.gridsortdirection NEQ "">
    ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdirection#
    </cfif>
    </cfquery>
    <cfif ARGUMENTS.export EQ "">
    <cfif rsData.RecordCount NEQ 0>
    <cfreturn QueryConvertForGrid(rsData, ARGUMENTS.page, ARGUMENTS.pageSize)>
    <cfelse>
    <cfreturn QueryConvertForGrid(rsData, 0, 0)>
    </cfif>
    <cfelse>
    <cfreturn rsData>
    </cfif>
    </cffunction>
    </cfcomponent>
    <!—test.cfm—>
    <cfparam name="URL.deptNo" default="0">
    <cfparam name="URL.deptName" default="None">
    <cfparam name="FORM.pageDept" default="1">
    <cfparam name="FORM.pageSizeDept" default="7">
    <cfset VAR.gridWidth = 800>
    <cfset VAR.gridcolBGColor = ‘##CCCCCC’>
    <cfoutput>#URL.deptNo# – #URL.deptName#</cfoutput><br /><br />
    <div id="reportTitle">Departments</div>
    <div id="reportContainer">
    <cfform>
    <cfgrid name="sales_dept_vs_prior" format="html" pagesize="#FORM.pageSizeDept#" width="#VAR.gridWidth#" bind="cfc:cfc.report.getDepartment({cfgridpage}, {cfgridpagesize}, {cfgridsortcolumn}, {cfgridsortdirection})" striperows="yes" selectonload="false" bindonload="yes">
    <!—Must include hrefKey column like this.—>
    <cfgridcolumn name="hrefKey" display="no">
    <!—Add href & hrefKey below.—>
    <cfgridcolumn name="DEPTNO" header="Dept. No" width="50" href="#CGI.SCRIPT_NAME#?taskID=HELLOWORLD" hrefkey="hrefKey">
    <cfgridcolumn name="DEPTNAME" header="Name" width="70" bgcolor="#VAR.gridcolBGColor#">
    </cfgrid>
    </cfform>
    </div>

    Reply
  3. Fabio Avatar
    Fabio

    What do you think about that?
    http://forums.adobe.com/message/3247507

    Reply
  4. egemen Avatar
    egemen

    hello, i have some issue about coldfusion grid,i try grid example but cold fusion given this error "The tag handler grid does not have a setter for the attribute checkBoxSelection specified in the Tag Library Descriptor.",please can you help me?
    my code is very simple
    <cfquery name = "GetCourses" dataSource = "calisma">
    SELECT id, name, surname,email
    FROM egemen_deneme
    </cfquery>
    <h3>cfgrid Example</h3>
    <i>Currently available courses</i>
    <!— cfgrid must be inside a cfform tag. —>
    <cfform>
    <cfgrid name = "FirstGrid" format="html"
    height="320" width="580"
    font="Tahoma" fontsize="12"
    query = "GetCourses">
    </cfgrid>
    </cfform> .

    Reply
  5. Michael Avatar
    Michael

    Please, dooes anyone know whether it is possible to extent the context menu of the coloums with a filter in Coldfusion 9 like in this example: http://ccinct.com/lab/filter-grid/
    Any help would be very much appreciated!

    Reply
  6. Wendy Avatar
    Wendy

    Does anyone know why my cfgrid will not display?
    I am attempting to prepare a CFGRID that will allow for Add and Delete through the use of
    popup windows. I have used ideas from several websites that showed how to acheive this
    through the use of CFC that uses a QueryConvertForGrid function to populate the grid. The
    page loads and the headers are displayed for the CFGRID, but I receive an error that indicates
    an exception was thrown and not caught. It points to Line 798 in cfajax.js that contains
    syntaxerror("parseJSON"). I have scoured the internet to see if I could find a solution, but
    have not been successful.
    Does anyone have any ideas on what to look for or what may be causing this error?
    Here is the CFC:
    <cfcomponent>
    <cffunction name="getWork" access="remote" output="false" returntype="any">
    <cfargument name="page" required="yes"/>
    <cfargument name="pageSize" required="yes"/>
    <cfargument name="gridsortcolumn" required="yes"/>
    <cfargument name="gridsortdirection" required="yes"/>
    <cfif arguments.gridsortcolumn eq "">
    <cfset arguments.gridsortcolumn = "WorkCompleted" />
    <cfset arguments.gridsortdirection = "asc" />
    </cfif>
    <cftry>
    <cfquery name="getWrk" datasource="IBIS">
    SELECT *
    FROM PTS_Work
    WHERE PersonnelID = #PID# AND
    ProjectID = #URL.PROJECTID#
    <cfif ARGUMENTS.gridsortcolumn neq "" or ARGUMENTS.gridsortdirection neq "">
    order by #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdirection#
    </cfif>
    </cfquery>
    <cfreturn QueryConvertForGrid(getWrk, ARGUMENTS.page, ARGUMENTS.pageSize)>
    <cfcatch>
    <cfreturn cfcatch.detail>
    </cfcatch>
    </cftry>
    </cffunction>
    <cffunction name="editWork" access="remote" output="false" returntype="any">
    <cfargument name="gridaction">
    <cfargument name="gridrow">
    <cfargument name="gridchanged">
    <cfif isStruct(ARGUMENTS.gridrow) and isStruct(ARGUMENTS.gridchanged)>
    <cfif ARGUMENTS.gridaction eq "U">
    <cfset colname=structkeylist(ARGUMENTS.gridchanged)>
    <cfset value=structfind(ARGUMENTS.gridchanged,#colname#)>
    <cfquery name="updateWork" datasource="IBIS">
    update PTS_Work set <cfoutput>#colname#</cfoutput> =
    ‘<cfoutput>#value#</cfoutput>’
    where workid = <cfoutput>#ARGUMENTS.gridrow.workid#</cfoutput>
    </cfquery>
    <cfelse>
    <cfquery name="delWork" datasource="IBIS">
    delete from PTS_Work where workid = <cfoutput>#ARGUMENTS.gridrow.workid#
    </cfoutput>
    </cfquery>
    </cfif>
    </cfif>
    </cffunction>
    <cffunction name="addNewWork" access="remote">
    <cfargument name="hours" type="string" required="true">
    <cfargument name="date1" type="string" required="true">
    <cfargument name="workcompleted" type="string" required="true">
    <cftry>
    <cfquery datasource="IBIS">
    INSERT INTO PTS_Work
    (
    projectid,
    hours,
    date1,
    workcompleted,
    personnelid
    )
    VALUES
    (
    #URL.ProjectID#,
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.hours#">
    ,#now()#
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.workcompleted#">
    ,#pid#
    )
    </cfquery>
    <cfreturn "Work Item Added">
    <cfcatch>
    <cfreturn cfcatch.detail>
    </cfcatch>
    </cftry>
    </cffunction>
    <cffunction name="deleteArtist" access="remote">
    <cfargument name="workid" required="true" type="string">
    <cftry>
    <cfquery datasource="IBIS">
    DELETE FROM PTS_Work
    WHERE
    workid = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.workid#">
    </cfquery>
    <cfreturn "Work Item Deleted">
    <cfcatch>
    <cfreturn cfcatch.detail>
    </cfcatch>
    </cftry>
    </cffunction>
    </cfcomponent>
    Here is the load of the CFGRID:
    <cfform>
    <cfgrid name="Work_Grid" format="html" bind = "cfc:workdata.getWork({cfgridpage},{cfgridpagesize},
    {cfgridsortcolumn},{cfgridsortdirection})" selectmode="edit"
    onchange="cfc:workdata.editWork({cfgridaction},{cfgridrow},{cfgridchanged})" selectcolor="##D9E8FB">
    <cfgridcolumn name="ProjectID" header="Project" display="no">
    <cfgridcolumn name="Hours" header="Hours" display="yes">
    <cfgridcolumn name="WorkCompleted" header="Work Completed" width="200" display="yes">
    <cfgridcolumn name="Date1" header="Date Entered" headeralign="left" dataalign="left" width="100"
    bold="no" italic="no" select="no" display="yes" headerbold="no" headeritalic="no">
    <cfgridcolumn name="PersonnelID" display="no">
    <cfgridcolumn name="WorkID" display="no">
    </cfgrid>
    </cfform>
    Any help would be greatly appreciated!!!!

    Reply

Leave a Reply

Older Comments
1 2