Blog

31May
2007
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 <CFGRID> of old, pass it a query and it displays the data. Here is an example (which uses the example tables that come with ColdFusion):

view plain print about
1<cfquery name="artists" datasource="cfartgallery">
2SELECT artistid, lastname, firstname, email
3FROM artists
4ORDER BY lastname, firstname
5</cfquery>
6
7<cfform>
8    <cfgrid name="artists"
9            format="html"
10            striperows="yes"
11            query="artists">

12        <cfgridcolumn name="lastname" header="Last Name" width="100"/>
13        <cfgridcolumn name="firstname" header="First Name" width="100"/>
14        <cfgridcolumn name="email" header="E-Mail" width="200"/>
15    </cfgrid>
16</cfform>

Here a <CFQUERY> retrieves data, and <CFGRID> 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 <CFGRID> also supports Ajax type interaction, where data is not pre-populated, but is asynchronously loaded as needed. Here is a sample data grid:

view plain print about
1<cfwindow initshow="true" center="true"
2            width="430" height="340" title="Artists">

3
4<cfform>
5    <cfgrid name="artists"
6            format="html"
7            pagesize="10"
8            striperows="yes"
9            bind="cfc:artists.getArtists({cfgridpage},
10                                        {cfgridpagesize},
11                                        {cfgridsortcolumn},
12                                        {cfgridsortdirection})"
>

13        <cfgridcolumn name="lastname" header="Last Name" width="100"/>
14        <cfgridcolumn name="firstname" header="First Name" width="100"/>
15        <cfgridcolumn name="email" header="E-Mail" width="200"/>
16    </cfgrid>
17</cfform>
18
19</cfwindow>

This data grid is displayed in a window created using the new <CFWINDOW> 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). <CFGRID> thus requests data, and simply displays whatever ColdFusion returns, automatically supporting paging (assuming there are enough rows to so warrant).

Now for the CFC:

view plain print about
1<cfcomponent output="false">
2
3    <cfset THIS.dsn="cfartgallery">
4
5    <!--- Get artists --->
6    <cffunction name="getArtists" access="remote" returntype="struct">
7        <cfargument name="page" type="numeric" required="yes">
8        <cfargument name="pageSize" type="numeric" required="yes">
9        <cfargument name="gridsortcolumn" type="string" required="no" default="">
10        <cfargument name="gridsortdir" type="string" required="no" default="">
11
12        <!--- Local variables --->
13        <cfset var artists="">
14
15        <!--- Get data --->
16        <cfquery name="artists" datasource="#THIS.dsn#">
17        SELECT artistid, lastname, firstname, email
18        FROM artists
19        <cfif ARGUMENTS.gridsortcolumn NEQ ""
20            and ARGUMENTS.gridsortdir NEQ "">
21            ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
22        </cfif>
23        </cfquery>
24
25        <!--- And return it as a grid structure --->
26        <cfreturn QueryConvertForGrid(artists,
27                            ARGUMENTS.page,
28                            ARGUMENTS.pageSize)>

29    </cffunction>
30
31</cfcomponent>

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 "". <CFQUERY> 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.

Related Blog Entries

Comments (56)



  • Clint Miller

    When you use the built-in QueryConvertForGrid in the CFC's getArtists method, does ColdFusion load the entire recordset into server memory before paginating based on the ARGUMENTS.page and ARGUMENTS.pageSize variables?

    #1Posted by Clint Miller | May 31, 2007, 06:23 PM
  • Ben Forta

    Clint, it uses whatever query you pass to it, and extracts a subset of the data.

    --- Ben

    #2Posted by Ben Forta | May 31, 2007, 08:02 PM
  • William from Lagos

    Simply wonderful. This brings AJAX to the masses!

  • Andre Audet

    So far, I'm loving CF8...

    Quick question.. In this example with the HTML CFGRID, is there a way to change the background color of a row dynamically?

    For example, if I had a list of tasks, I'd like to change the background color if the task is urgent...

    Thanks for the quick and easy CF8 tutorials, Ben... much appreciated!

    #4Posted by Andre Audet | Jun 13, 2007, 03:17 PM
  • Oliver

    Any idea why the source cannot be execute correctly under IE but can work on Firefox.

    Thanks,
    Oliver

    #5Posted by Oliver | Jun 20, 2007, 12:35 AM
  • I love my free PS3

    Has this been fixed for IE yet? Gawd, I hate cross-browser incompatibilities.

  • Charles Sheehan-Miles

    Hi Ben,

    This is good stuff. What I'm trying to figure out is how to add a link to the grid, so that when the user clicks on a record, it takes them to the appropriate page. I've been digging through the docs and google, but no luck so far.

  • Keith Korry

    My pagesize attribute doesnt work, any suggestions?

    <cfquery name="R1" datasource="canadadb" maxrows="200">
    SELECT *
    FROM dbo.cities
    </cfquery>
    <cfform>
    <cfgrid query="R1"
    name="R1"
    format="html"
    pagesize="10"
    striperows="yes">
    <cfgridcolumn name="id" header="First Name"/>
    <cfgridcolumn name="weather" header="weather"/>
    <cfgridcolumn name="cities" header="cities"/>
    <cfgridcolumn name="province" header="province"/>
    </cfgrid>
    </cfform>

    #8Posted by Keith Korry | Aug 11, 2007, 03:47 PM
  • Hal Helms

    Charles, you can add a link to the grid using the "href" attribute in the appropriate "cfgridcolumn" tag: just point that to where you want to drill down to. The "hrefkey" attribute will append a column value to the URL.

    #9Posted by Hal Helms | Aug 15, 2007, 09:56 AM
  • hans

    when i try ajax grids,it has some wrong information:
    "Attribute validation error for tag CFGRID.
    The tag does not allow the attribute(s) HEADER. The valid attribute(s) are "
    What is the reason for this?

    thanks

    #10Posted by hans | Aug 15, 2007, 04:10 PM
  • hans

    oh, i'm so sorry.
    i found the wrong reasons for the last aid.
    I have written 'cfgridcolumn ' misuse of the 'cfgrid'.
    so slack~

    BTW, i like your articles.

    #11Posted by hans | Aug 15, 2007, 04:18 PM
  • Jaseowns

    Awesome Grid Tutorial, I just started working with this myself and was wondering if there is a way to Autoformat the WIDTH of cfgridcolumn to the size of ethier the column header or column value.

    Double clicking the Grid line formats the width based on value, I was wondering if you know of a dynamic way to do this?

    Thanks,
    Jason

    #12Posted by Jaseowns | Oct 15, 2007, 05:31 PM
  • Cheryl

    Hi,

    I've got a select that filters a grid. If I check items without changing the filter I can post the form and get the results. However, when I change the filter I don't get anything when I post.

    How can I fix this?

    ========================================
    <cfscript>
    rs = queryNew("id,checked,fname,lname,playerposition,salary");
    addRow(rs, '','Chester','Taylor','Running Backs','$3,500,000');
    addRow(rs, 'true','Mewelde','Moore','Running Backs','$1,750,000');
    addRow(rs, '','Tavaris','Jackson','Quarterbacks','$1,250,000');
    addRow(rs, '','Bobby','Wade','Receivers','$2,250,000');
    function addRow( qry, checked, fname, lname, playerposition, salary){
    queryAddRow(rs);
    querySetCell(rs,'checked',checked);
    querySetCell(rs,'fname',fname);
    querySetCell(rs,'lname',lname);
    querySetCell(rs,'playerposition',playerposition);
    querySetCell(rs,'salary',salary);
    }
    </cfscript>

    <cfquery dbtype="query" name="getPos">
    SELECT distinct playerposition
    FROM rs
    ORDER BY playerposition
    </cfquery>
    <cfquery dbtype="query" name="MemberList">
    SELECT * from rs
    ORDER BY playerposition
    </cfquery>

    <cfsavecontent variable="astest">
    if(_global.arrMembers == undefined) _global.arrMembers = data.dataProvider.slice(0);

    var arrMembers = _global.arrMembers;
    var arrDisplay:Array = [];

    for(var i = 0; i < arrMembers.length; i++)
    {
    if(arrMembers[i].playerposition == myselect1.value || myselect1.value == 'All')
    {
    arrDisplay.push(arrMembers[i]);
    }
    }
    data.dataProvider = arrDisplay;
    </cfsavecontent>

    <cfform name="myForm" format="flash" width="400" height="300" action="#cgi.script_name#" method="playerpositiont">
    <cfformgroup type="panel" label="Search">
    <cfselect query="getPos" queryposition="below" label="Position" name="myselect1" value="playerposition" display="playerposition" width="200" onChange="#astest#">
    <option value="All">All</option>
    </cfselect>
    <cfgrid name="data" query="MemberList" selectmode="edit" >
    <cfgridcolumn header="Select" name="checked" type="boolean" headeralign="center" dataalign="center" />
    <cfgridcolumn header="First Name" name="fname" />
    <cfgridcolumn header="Last Name" name="lname" />
    <cfgridcolumn header="salary" name="salary" />
    <cfgridcolumn header="Position" name="playerposition" display="false" />
    </cfgrid>
    <cfinput type="submit" name="submitBtn" style="cornerRadius:1;" value="Submit"/>
    </cfformgroup>
    </cfform>


    <p><cfdump var=#form#><P>

    #13Posted by Cheryl | Oct 17, 2007, 09:11 PM
  • RG

    I copied the code, and changed it for my datasource, and it is coming up with an error invoking the CFC, I have the CFC in the same directory as the page calling the bind attribute, where is the problem.

    CFC:

    <cfcomponent output="false">

    <cfset THIS.dsn="dsPlanningApps">

    <!--- Get Items --->
    <cffunction name="LookupApps" 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 rsLookup="">

    <!--- Get data --->
    <cfquery name="rsLookup" datasource="#THIS.dsn#">
    SELECT [File Nbr] AS FileNbr, Applicant, Planner
    FROM dbo.PlanningZoningApplications
    <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(rsLookup,
    ARGUMENTS.page,
    ARGUMENTS.pageSize)>
    </cffunction>

    </cfcomponent>


    HERE IS THE FORM WITH THE GRID:

    <cfform>
    <cfgrid name="artists"
    format="html"
    pagesize="10"
    striperows="yes"
    bind="cfc:member.PlanningLookup.LookupApps({cfgridpage},
    {cfgridpagesize},
    {cfgridsortcolumn},
    {cfgridsortdirection})">
    <cfgridcolumn name="FileNbr" header="File No." width="100"/>
    <cfgridcolumn name="Applicant" header="Applicant" width="100"/>
    <cfgridcolumn name="Planner" header="Planner" width="200"/>
    </cfgrid>
    </cfform>

    #14Posted by RG | Oct 18, 2007, 12:40 PM
  • Bruce Van Horn

    How would I bind the CFC if it were in a different directory. This example works great when everything is in the same folder, but if I move the artists.cfc to a mapped folder, it fails.

    Here's how I'm calling it: bind="cfc:CobeTemplates.manager.artists.getArtists(..."

    I have a Mapping called "CobeTemplates" with a sub folder called manager. I have moved artists.cfc to that folder.

    When I run it, I get a javascript error (Line 767, Error: Exception thrown and not caught) and the grid never displays.

    I'm storing it in a mapped folder because I want to be able to call this CFC from several different applications. Any ideas?

    Thanks,
    Bruce

    #15Posted by Bruce Van Horn | Oct 18, 2007, 02:24 PM
  • Scott C

    Am I correct in saying that when you click to sort on a column it will go back to the .cfc, rerun the query, sorting it on the column? Wouldn't this be absolutely horrible if your initial query took say 20-30 seconds to complete?

    If this is the case is there anyway to have it sort on the client?

    Also is there anyway to get rid of pagination and just let it use a scrollbar? It see examples on extjs.com that seem to provide this.

    #16Posted by Scott C | Oct 19, 2007, 07:04 PM
  • Reg

    Hi,

    I have modified the code to populate the grid from a table and added a hard coded argument which is passed to the cfc and used in the modified select with a where clause to get selected data. All is working but my question is what would I add if I wanted to be able to be able to select the argument to use in the where clause from a drop list to repolulate the grid with this new data. I'm a bit stuck on how to do this so please help if you can.

    Thanks,
    Reg

    #17Posted by Reg | Oct 24, 2007, 02:28 PM
  • Reg

    This is the cfm page
    <cfparam name="user_id" default="1">
    <cfparam name="month" default="1">
    <cfparam name="year" default="2007">

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitiona...;
    <html xmlns="http://www.w3.org/1999/xhtml">;
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    <title>Untitled Document</title>
    </head>

    <body>
    <cfwindow initshow="true" center="true"
    width="480" height="340" title="Blog Entries" draggable="false" closable="false" resizable="false">

    <cfform>
    <cfgrid name="entrylinks"
    format="html"
    pagesize="10"
    striperows="yes"
    bind="cfc:getentrylinks.getEntryLinks({cfgridpage},
    {cfgridpagesize},
    {cfgridsortcolumn},
    {cfgridsortdirection},'#user_id#','#month#','#year#')">
    <cfgridcolumn name="Date_Created" header="Date_Created" width="150"/>
    <cfgridcolumn name="Title" header="Title" width="200"/>
    <cfgridcolumn name="Counter" header="Counter" width="100"/>
    </cfgrid>

    </cfform>

    </cfwindow>
    </body>
    </html>

    As you can see three arguments are passed. How can I put a drop list populated with months 1,2,3,4 etc in the cfwindow and have the selection used repolulate the grid with the new data obtained from the cfc.

    Thanks,
    Reg

    #18Posted by Reg | Oct 24, 2007, 02:54 PM
  • Michael Lato

    I do like the new cfgrid controls but I am pulling out my hair trying to work with them. Specifically, I want to use a grid within a tab. Selecting a row in the grid should open a new detail page within the tab. I can make the links work using the href and hrefkey commands but this always opens a new page. Am I doing something wrong?

    #19Posted by Michael Lato | Nov 5, 2007, 10:08 PM
  • Reg

    Try using target="_top" like this
    <cfgridcolumn name="Title" header="Title" width="220" target="_top" href="http://www.yourdomain.com/yourpage.cfm?id=#user_id...; hrefKey="Counter"/>

    #20Posted by Reg | Nov 6, 2007, 02:59 AM
  • Reg

    <cfgridcolumn name="Title" header="Title" width="220" target="_top" href="http://www.cascobayblog.com/blog.cfm?id=#user_id#&...; hrefKey="Counter"/>
    where Counter is a Database field. The previous comment shows #user_id... this should have the closing hash symbol and closing quote it's a mystery why it showed it as it did . Anyway hope this helps

    #21Posted by Reg | Nov 6, 2007, 03:07 AM
  • Tom Barr

    The new enhancement to CFGRID is classic ColdFusion beauty. Neatly package a lot of AJAX wiring in a few custom tags and attributes. It took me less than a couple hours late at night to implement an AJAX based data bound grid on SalaryMap.com at http://www.salarymap.com/salary-finder.cfm.

    #22Posted by Tom Barr | Nov 8, 2007, 05:23 PM
  • Print

    I have facing one problem with cfgird

    Let i explaing i have two field client and memer and i bind grid with client and member and serch according this two field.

    I am also using one more featured in this member is depend upon client. when i select any client at that time member drop -down filed automatic
    here client and member both are drop-down field
    My problem is when i change client name grid refresh two time. because when i chenged client at that time meber drop -down change and cfgrid bind on client and meber so it is referesh to time.
    Can you please tell us how to stop cfgrid bind two time . cfgrid should be refresh when i click on search button not on bind.

    #23Posted by Print | Jan 2, 2008, 05:37 AM
  • Anusha

    I am using the cf8 html grid. Anyone know how to remove the "-" that appears on blank rows when pagination is allowed on the cfgrid? I have one column on the grid linking to a different URL. When the record exists (row is not blank) it works fine. Anyone come across this issue. Code below:
    <cfgrid name = "ViewedReports" format="html" height="170" width="780" pageSize=5 sort=true selectcolor="##CCCCCC" selectmode="single" selectOnLoad="false"
    bind="cfc:ViewReportsByInv.getViewedReportsByInv({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
    <cfgridcolumn name = "REP_REPORT_ID" display="no">
    <cfgridcolumn width="110" name = "VIEWREPORT" header="Report" href="ViewReport.cfm?status=viewed" hrefkey="REP_REPORT_ID" target="_blank"> </cfgrid>
    </cfform>

    #24Posted by Anusha | Jan 14, 2008, 12:50 PM
  • Francois-Yanick Bourassa

    Hi Ben,

    what if I need to return a selected columname from the CFGRID in the CFWINDOW in order to affect a selectIndex of a specific CFSELECT on the main (caller) page. I cannot find the way to transmit the value to the main page from the CFWINDOW. :-|

  • Derrick

    Has anyone been able to get this to work:

    <cfgridcolumn name="questionText" header="Question" width="345" href="javascript:TestFunction()" hrefkey="questionUUID" />

    I can easily pass in a url but when i try to pass in a javascript funtion it does not work.

    #26Posted by Derrick | Feb 21, 2008, 02:05 PM
  • Jonathan

    How can I pass URL variables into the BIND CFC to filter the SQL query there in the WHERE clause?

    Ive tried to make the URL var a hidden form variable passed in the BIND attribute, with an ARGUMENT tag on the CFC, but it just sits there thinking eternally.

    <CFFORM>
       <CFINPUT TYPE="HIDDEN" NAME="equitytype" VALUE="#url.equitytype#">
       <CFGRID NAME="GetMemberDataForDirectory"
          FORMAT="HTML"
    PAGESIZE="#URL.SHOWROWS#"
    STRIPEROWS="YES"
    SELECTONLOAD="FALSE"
    APPENDKEY="YES"
    HREF="/index.cfm?fuseaction=members.emailmember"
    HREFKEY="ENCRYPTARCODE"
    WIDTH="100%"
       BIND="cfc:memberDirectory.getMembers(
                   {cfgridpage},
    {cfgridpagesize},
    {cfgridsortcolumn},
    {cfgridsortdirection},
    {equitytype}
    )">
    <CFGRIDCOLUMN NAME="lastname" HEADER="Last Name" WIDTH="100"/>
    <CFGRIDCOLUMN NAME="firstname" HEADER="First Name" WIDTH="100"/>
    <CFGRIDCOLUMN NAME="City" HEADER="Home City" WIDTH="125">
    <CFGRIDCOLUMN NAME="State" HEADER="State" WIDTH="50">
    <CFGRIDCOLUMN NAME="equitytype" HEADER="Type" WIDTH="50">
    <CFGRIDCOLUMN NAME="misc1" HEADER="misc1" WIDTH="50">
    <CFGRIDCOLUMN NAME="emailme" HEADER="CONTACT">
    <CFGRIDCOLUMN NAME="ENCRYPTARCODE" DISPLAY="NO" HEADER="ENCRYPTARCODE">
    </CFGRID>
    </CFFORM>


    CFC:
    <CFARGUMENT NAME="equitytype" TYPE="string" REQUIRED="no" DEFAULT="">

    <!--- Local variables --->
    <CFSET var GetMemberDataForDirectory="">

    <!--- Get data --->
    <CFQUERY NAME="GetMemberDataForDirectory" DATASOURCE="ORCIntranet">
    SELECT ARCODE,Lastname, Firstname, City, State, equitytype, MISC1, EMAIL, CASE WHEN EMAIL LIKE ('%@%') THEN 'EMAIL'+' '+Firstname ELSE 'NO EMAIL' END AS EMAILME, CONVERT(VARCHAR, (ARCODE * 37))+CONVERT(VARCHAR, (FAMNUM * 37)) AS ENCRYPTARCODE
    FROM view_familyinfo_all_activemembers_withcontactinfo
        WHERE FAMNUM <= '2' AND MISC1 <> '' and MISC1 BETWEEN '100' and '599'
    <CFIF arguments.equitytype EQ 'equity'>AND MISC1 IN ('110')</CFIF>
    <CFIF ARGUMENTS.gridsortcolumn NEQ "" and ARGUMENTS.gridsortdir NEQ "">
    ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
    </cfif>
    </CFQUERY>

    #27Posted by Jonathan | Mar 6, 2008, 02:16 PM
  • Dan K

    I am having problems getting the CFGRID to display any data at all when used with components and QueryConvertForGrid

    If I copy the examples from this and other sites and create the CFM and CFC pages using the databases built into ColdFusion I do not get any data at all displayed in the grid. The grid is there with the headers and grayed out page controls, and no CF errors are thrown, but there is no data either.

    This is the case no matter if I use IE or Firefox to display the CFM page with the CFGRID in it.

    I'm using the latest version of CF with the built in web server.

    Any ideas?

    #28Posted by Dan K | Mar 25, 2008, 09:40 PM
  • Jonathan

    I was able to begin passing URL variables into the grid (with help from others!) using the following code (this answers my posting above):
    here's the final code for posterity and for others.
    they keys i was missing were
    1. the <CFINPUT tag type=hidden> that defined the form variable,
    2. the BIND attribute

    FORM PAGE:
    <CFFORM>
       <CFINPUT NAME="equitytype" TYPE="HIDDEN" VALUE="#url.equitytype#">
       <CFINPUT NAME="state" TYPE="HIDDEN" VALUE="#url.state#">
       <CFINPUT NAME="initial" TYPE="HIDDEN" VALUE="#url.initial#">
       <CFGRID NAME="GetMemberDataForDirectory"
          FORMAT="HTML"
    PAGESIZE="#URL.SHOWROWS#"
    STRIPEROWS="YES"
    SELECTONLOAD="FALSE"
    APPENDKEY="YES"
    HREF="/index.cfm?fuseaction=members.emailmember"
    HREFKEY="ENCRYPTARCODE"
    WIDTH="100%"
       BIND="cfc:memberDirectory.getMembers(
                   {cfgridpage},
    {cfgridpagesize},
    {cfgridsortcolumn},
    {cfgridsortdirection},
    '#URL.equitytype#',
    '#URL.state#',
    '#URL.initial#'
    )">
    <CFGRIDCOLUMN NAME="lastname" HEADER="Last Name" WIDTH="100"/>
    <CFGRIDCOLUMN NAME="firstname" HEADER="First Name" WIDTH="100"/>
    <CFGRIDCOLUMN NAME="City" HEADER="Home City" WIDTH="125">
    <CFGRIDCOLUMN NAME="State" HEADER="State" WIDTH="50">
    <CFGRIDCOLUMN NAME="equitytype" HEADER="Type" WIDTH="50">
    <!---<CFGRIDCOLUMN NAME="misc1" HEADER="misc1" WIDTH="50">--->
    <CFGRIDCOLUMN NAME="emailme" HEADER="CONTACT">
    <CFGRIDCOLUMN NAME="ENCRYPTARCODE" DISPLAY="NO" HEADER="ENCRYPTARCODE">
    </CFGRID>
    </CFFORM>


    CFC:
    <CFCOMPONENT OUTPUT="false">
    <!--- Get Members --->
    <CFFUNCTION NAME="getMembers" 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="">
    <CFARGUMENT NAME="equitytype" TYPE="string" REQUIRED="no" DEFAULT="">
    <CFARGUMENT NAME="state" TYPE="string" REQUIRED="no" DEFAULT="">
    <CFARGUMENT NAME="initial" TYPE="string" REQUIRED="no" DEFAULT="">

    <!--- Local variables --->
    <CFSET var GetMemberDataForDirectory="">

    <!--- Get data --->
    <CFQUERY NAME="GetMemberDataForDirectory" DATASOURCE="ORCIntranet">
    SELECT ARCODE,Lastname, Firstname, City, State, equitytype, MISC1, EMAIL, CASE WHEN EMAIL LIKE ('%@%') THEN 'EMAIL'+' '+Firstname ELSE 'NO EMAIL' END AS EMAILME, CONVERT(VARCHAR, (ARCODE * 37))+CONVERT(VARCHAR, (FAMNUM * 37)) AS ENCRYPTARCODE
    FROM view_familyinfo_all_activemembers_withcontactinfo
        WHERE FAMNUM <= '2' AND MISC1 <> '' and MISC1 BETWEEN '100' and '599'
    <CFIF arguments.equitytype EQ 'equity'>AND MISC1 IN ('400','500')</CFIF>
    <CFIF arguments.equitytype EQ 'social'>AND MISC1 IN ('100','109','110','150','159','160','300','360')</CFIF>
    <CFIF arguments.equitytype EQ 'legacy'>AND MISC1 IN ('130','420','430','520','530')</CFIF>
    <CFIF arguments.initial NEQ ''>AND LEFT(Lastname,1) = '#arguments.initial#'</CFIF>
    <CFIF arguments.state NEQ ''>AND STATE = '#arguments.state#'</CFIF>
    <CFIF ARGUMENTS.gridsortcolumn NEQ "" and ARGUMENTS.gridsortdir NEQ "">
    ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
    <CFELSE>
    ORDER BY Lastname,ARCODE,FAMNUM
    </cfif>
    </CFQUERY>

    <!--- And return it as a grid structure --->
    <CFRETURN QueryConvertForGrid(GetMemberDataForDirectory,
    ARGUMENTS.page,
    ARGUMENTS.pageSize)>
    </CFFUNCTION>

    </CFCOMPONENT>

    #29Posted by Jonathan | Mar 26, 2008, 09:18 AM
  • Paul

    My grid looks like this. It's in a Mach-II app. Everything displays beautifully. However, as you see, I'm trying to pass each record's Record_Id in the href parameter of the first column. However, I get a Mach-II exception: "Variable RECORD_ID is undefined." Can anyone see what i've done wrong?

    <cfform>
    <cfgrid name="cars"
    format="html"
    pagesize="10"
    striperows="yes"
    bind="cfc:cars4grid.viewAllCars({cfgridpage},
    {cfgridpagesize},
    {cfgridsortcolumn},
    {cfgridsortdirection})">
    <cfgridcolumn name="make" header="Make" width="75" target="_top" href="index.cfm?event=editCar&id=#Record_Id#" hrefKey="Counter" />
    <cfgridcolumn name="Record_Id" header="ID" width="20"/>
    <cfgridcolumn name="model" header="Model" width="120"/>
    <cfgridcolumn name="year" header="Year" width="60"/>
    <cfgridcolumn name="ccRating" header="CCs" width="60"/>
    <cfgridcolumn name="enginetype" header="Engine" width="60"/>
    <cfgridcolumn name="transmission" header="Transmission" width="120"/>
    </cfgrid>
    </cfform>

    #30Posted by Paul | Apr 18, 2008, 06:24 AM
  • Paul

    don't worry...got it sorted.

    #31Posted by Paul | Apr 18, 2008, 09:57 PM
  • vaughn

    How can you condition a cfgridcolumn based on another column in the query (without getting into extensions)?

    <cfgrid format="html" name="grid_Companies" bind=   "url:dspSearchResults_2.cfm?page={cfgridpage}&pageSize={cfgridpagesize}&sortCol={cfgridsortcolumn}&sortDir={cfgridsortdirection}&name2={name2@keyup}&name3={name3@keyup}" bindonload="yes" width="780" selectonload="no" pagesize="40" selectmode="row" autowidth="yes" colheaderalign="center" colheaders="yes" griddataalign="left" >
    <cfgridcolumn name="ID" display="Yes" dataalign="left" width="12" href="index.cfm?fa=viewComp"/>
    <cfgridcolumn name="DateViewed" display="no" />
    <cfgridcolumn name="WhoViewed" display="no" />
    <cfif #Gcomps.dateviewed# eq #today# and #Gcomps.whoviewed# eq "test">
    <cfgridcolumn name="Name" header="Name" dataalign="left" width="40" bgcolor="green" />
    <cfelse>
    <cfgridcolumn name="Name" header="Name" dataalign="left" width="40" />
    </cfif>
    <cfgridcolumn name="PhoneNumber" header="Phone" dataalign="left" width="14" />
    <cfgridcolumn name="CityState" header="City State" dataalign="left" width="20" />
    <cfgridcolumn name="Type_Description" header="Type" dataalign="left" width="20" />

    </cfgrid>

    #32Posted by vaughn | May 2, 2008, 11:01 AM
  • Brian

    I'm having a couple problems with cfgrid although for the most part it is working - very cool:
    1) while the same cfgrid with format=flash using cfform format=flash I get the ability to format my dates using the mask attribute. However since I did not want to go the flex route yet, but wanted the assynchronous data binding it appeared after many tests and web searches that my only choice is this AJAX approach. I saw some references to people customizing the grid renders, but is there another way? Will the next CF update fix this issue?
    2) I'm trying to pass a Where clause to my cfc and have tried the above mentioned technique, but it just adds the text to a text field diadocsubject in the grid. Also if I use the clause commented out below, it blows up even though i handle the single quote issue in my CFC. What am I doing wrong here on passing a string and also how could I pass the sting with single quotes in it?

    On my CFM page:
    ...
    <!--- <cfset vp_WhereClause = "diadocument.diadocsubject = 'CODR'" /> --->
    <cfset vp_WhereClause = "CODR" />
    <cfinput name="vp_WhereClause" type="hidden" value="#vp_WhereClause#" />
    <cfgrid name="searchGrid" format="html" bind="cfc:#strcomponentpath#readdiadocument({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection}, '#vp_WhereClause#')">
    <cfgridcolumn name="diadocumentkey" header="diadocumentkey" display="false" />
    <cfgridcolumn name="diadocno" header="docno" dataalign="right" width="70" />
    <cfgridcolumn name="diadocsubject" header="subject" type="string_nocase" dataalign="left" width="80" />
    <cfgridcolumn name="diadeliverydate" header="doc date" type="string_nocase" mask="mm/dd/yyyy" dataalign="right" width="80" />
    </cfgrid>
    ...

    In my CFC:
    ...
    <cffunction name="readdiadocument" returntype="struct" output="false" access="remote">
    <cfargument name="cfgridpage" required="false" >
    <cfargument name="cfgridpageSize" required="false" >
    <cfargument name="cfgridsortcolumn" required="false" />
    <cfargument name="cfgridsortdirection" required="false" />
    <cfargument name="vp_WhereClause" required="false" type="string" default="0=0 ">
    <cfquery name="qRead" datasource="DocumentImaging">
    SELECT Top 100 diadocumentkey, diadocno, diadocsubject, diadeliverydate
    FROM diadocument
    <!--- WHERE #PreserveSingleQuotes(vp_WhereClause)# --->
    WHERE diadocument.diadocsubject = '#vp_WhereClause#'
    <cfif len(arguments.cfgridsortcolumn) and len(arguments.cfgridsortdirection)>
        ORDER BY #arguments.cfgridsortcolumn# #arguments.cfgridsortdirection#
    <cfelse>
        ORDER BY diadocno Asc
    </cfif>
    </cfquery>
    <cfreturn queryConvertForGrid(qRead, cfgridpage, cfgridpageSize) />
    </cffunction>
    ...

    #33Posted by Brian | May 8, 2008, 01:44 PM
  • Brian

    Actually #2 worked, I misinterpreted the data, but how could I pass the string with single quotes in it?

    #34Posted by Brian | May 9, 2008, 05:23 PM
  • Ryan

    I am having the same problem as Dan above. I've recreated everything the same as the example and the form loads, no errors, but the cfgrid always loads empty. The little dial is spinning as if it were waiting for data, but it never populates. Is there a way to debug this? with it using the bind feature, it appears that coldfusion isn't actually running the query when the page is run, its not showing up in the debugging information, is that normal?

    Thanks in advance,
    Ryan

    #35Posted by Ryan | Jul 26, 2008, 01:18 AM
  • Paul

    Anyone who needs to use the editable html grid should be aware of this bug which Ray Camden kindly helped me find a fix for: http://www.coldfusionjedi.com/index.cfm/2008/8/5/A...

    #36Posted by Paul | Aug 6, 2008, 03:15 PM
  • Sam

    Ben,

    I am using a cf8 cfgrid type=html. I want to know how can use the herf attribute an only link to something when a link is in the db. Example If I have 10 names of schools in the DB and only 8 have links I only want the links for those schools 2 show.

    <cfgridcolumn href="link"name="school" header="School" width="0" />

    School is the name of the school and link is the link to the school stored in the db

    #37Posted by Sam | Sep 4, 2008, 11:42 PM
  • Erik Tsomik

    Great tutorial. How would i add switch to the next row and enter the value manually.

    #38Posted by Erik Tsomik | Sep 26, 2008, 05:27 PM
  • Haja

    Hi Ben

    I have been trying to add a HREF to a CFGRIDCOLUMN in a cfform format = "flash". But it doesn't seem to work. Please do help me to get rid of this.

    <cfgrid target="dictionary.cfm" selectmode="row" format="html" name="WordList" query="query1" rowheaders="no" maxrows="15" height="372">
    <cfgridcolumn name="dict_word" header="Dictionary Words" href="javascript:open();" hrefKey="dict_id" />
    </cfgrid>

    Thanks a bunch!

    #39Posted by Haja | Jan 15, 2009, 07:06 AM
  • Haja

    I also changed the link to Href="dictionary.cfm" , but no use. Please do help me.

    Thanks!

    #40Posted by Haja | Jan 15, 2009, 07:08 AM
  • Kathmandu

    Greetings,

    I am a new CF programmer seeking to resolve a problem similar to previous post. I am attaching a working code snippet below that I plagiarized to explain my problem with cfgrid --> cfgridcolumn and href property. I would like to place an edit/ delete hyperlink that calls a javascript function on a column in the cfgrid but with no avail. Please review the code below and advise. Thanks.

    <cfscript>
    rs = queryNew("id,fname,lname,dept,email");
    addRow(rs, 'simeon','bateman','IT','simeon@eee.net');
    addRow(rs, 'Mr','Man','HR','theMan@eee.com');
    addRow(rs, 'Cool','Guy','HR','coolGuy@eee.com');
    addRow(rs, 'Another','test','Customer Service','another@teeeest.com');
    function addRow( qry, fname, lname, dept, email){
    queryAddRow(rs);
    querySetCell(rs,'fname',fname);
    querySetCell(rs,'lname',lname);
    querySetCell(rs,'dept',dept);
    querySetCell(rs,'email',email);
    }
    </cfscript>

    <cfquery dbtype="query" name="getDepts">
    SELECT distinct dept
    FROM rs
    ORDER BY dept
    </cfquery>
    <cfquery dbtype="query" name="MemberList">
    SELECT * from rs
    ORDER BY dept
    </cfquery>

    <cfsavecontent variable="astest">
    if(_global.arrMembers == undefined) _global.arrMembers = data.dataProvider.slice(0);

    var arrMembers = _global.arrMembers;
    var arrDisplay:Array = [];

    for(var i = 0; i < arrMembers.length; i++)
    {
    if(arrMembers[i].dept == myselect1.value || myselect1.value == 'All')
    {
    arrDisplay.push(arrMembers[i]);
    }
    }
    data.dataProvider = arrDisplay;
    </cfsavecontent>

    <cfform name="myForm" format="flash" width="500" height="250">
       <cfformgroup type="tabnavigator" height="200">
          <cfformgroup type="page" label="Employee">
             <cfgrid name="data" query="MemberList" >
                <cfgridcolumn header="First Name" name="fname" />
                <cfgridcolumn header="Last Name" name="lname" />
                <cfgridcolumn header="email" name="email" />
                <cfgridcolumn header="Department" name="dept" display="false" />
                <!---unable to pass the parameter [id] to the href below--->
                <cfgridcolumn header="Edit" name="id" href="javascript:alert(id);" hrefkey="id" />
             </cfgrid>
          </cfformgroup>
    </cfformgroup>
    </cfform>

    #41Posted by Kathmandu | Feb 3, 2009, 01:24 PM
  • Steve

    I have over 2000 records in the database table. Is there a way in which I could call a max record count (say 25 records) and then have the next 25 (next page) called from the database when the next page button is clicked? The overhead of loading over 2000 records is just too much. Thanks!

    -Steve

    #42Posted by Steve | Mar 4, 2009, 01:45 PM
  • Randy Jensen

    I am using the ajax HTML grid wit hCF8 and am trying to display dates using a mask="mm/dd/yyyy" format but it gives me the dates as in this sample format: July, 23 2009 00:00:00. The dates are coming from an access database date field formatted for a short date. What do I need to do to get the format changed or at least get rid of the timestamp? thanks

    #43Posted by Randy Jensen | May 21, 2009, 04:55 PM
  • Brian O

    After your database call, loop over your query results (qread below) and format the dates before returning them to the grid.

    <!--- Reformat all dates above --->
    <cfloop query="qRead">
    <cfset qRead.diaentrydatetime=DateFormat(diaentrydatetime,"mm/dd/yyyy") />
    <cfset qRead.dialastmodifieddatetime=DateFormat(dialastmodifieddatetime,"mm/dd/yyyy") />
    </cfloop>

    #44Posted by Brian O | May 21, 2009, 05:11 PM
  • R Jensen

    That worked! thanks for the quick response.

    #45Posted by R Jensen | May 22, 2009, 09:56 AM
  • Willis

    Anyone know how I can pass all the form variables in a structure through the bind clause?

    #46Posted by Willis | Jun 11, 2009, 05:35 PM
  • Jason

    I added a
    <cfgridcolumn name="Checked" header="Delete" type="boolean" width="46" display="yes"/> to my cfgrid to add a
    checkbox. My cfgrid's format is format="HTML". What
    happens is that there will be a column that is
    created, but is blank. I would have to click on the cell
    to have the checkbox display. After I focus on a
    different cell, it would display true or false,
    depending on whether the checkbox is checked or not.

    I was wondering how can I make the checkbox
    display right when the grid is displayed? Currently I
    have to click on the cell to make the checkbox appear.

    Jason

    #47Posted by Jason | Aug 14, 2009, 10:04 PM
  • Michael Appenzellar

    Is there any way to bind to a CFC so that I can sort, but ALSO use the groupby attribute? This seems simple but I get an error.

    #48Posted by Michael Appenzellar | Jul 7, 2010, 08:56 AM
  • Michael Appenzellar

    Is there any way to bind to a CFC so that I can sort, but ALSO use the groupby attribute? This seems simple but I get an error.

    #49Posted by Michael Appenzellar | Jul 7, 2010, 08:56 AM
  • 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>

    #50Posted by Chad Fraser | Oct 29, 2010, 04:21 PM
  • 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>

    #51Posted by Chad Fraser | Oct 29, 2010, 04:22 PM
  • 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>

    #52Posted by Chad Fraser | Oct 29, 2010, 04:24 PM
  • Fabio

    What do you think about that?

    http://forums.adobe.com/message/3247507

    #53Posted by Fabio | Nov 2, 2010, 05:27 AM
  • 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> .

    #54Posted by egemen | Oct 31, 2011, 09:19 AM
  • 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!

    #55Posted by Michael | Dec 29, 2011, 04:56 AM
  • 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!!!!

    #56Posted by Wendy | Jan 11, 2012, 12:24 PM