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 thoughts

  1. 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?

  2. 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!

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

  4. 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.

  5. 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>

  6. 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.

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

  8. 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.

  9. 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

  10. 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>

  11. 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>

  12. 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

  13. 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.

  14. 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

  15. 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-transitional.dtd"&gt;
    <html xmlns="http://www.w3.org/1999/xhtml"&gt;
    <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

  16. 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?

  17. 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.

  18. 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>

  19. 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. 😐

  20. 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.

  21. 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>

  22. 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?

  23. 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>

  24. 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>

  25. 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>

  26. 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>

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

  28. 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

  29. 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

  30. 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!

  31. 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>

  32. 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

  33. 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

  34. 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>

  35. 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

  36. 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.

  37. 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.

  38. 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>

Leave a Reply