ColdFusion Development: CFGrid Custom Date Renderer

CFGrid Custom Date Renderer

Posted At : November 27, 2007 5:28 PM | Posted By : Scott Bennett
Related Categories: JavaScript,ColdFusion,AJAX

One of the coolest things about the ColdFusion 8 implementation of the CFGrid tag is that you can do a lot of customization, if you know your way around the Ext objects. I have found several blog entries about using custom renderers with the CFGrid tag. However, could not find a working example of one for date fields, so I decided to build one.

This example uses the coupons table in the cfbookclub database that comes with ColdFusion. There are two files in this example, coupons.cfc contains the functions needed for the editable CFGrid, and CouponForm.cfm contains the CFGrid Code and the JavaScript needed to do the date rendering. You will notice I had to specifically import the /CFIDE/scripts/ajax/ext/package/date.js file because it is not automatically imported with the cfgrid functionality. If you don't include that script tag you will get the JavaScript error "v.dateFormat is not a function".

CouponForm.cfm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
   <title>Custom Date Renderer</title>
   
   <script src="/CFIDE/scripts/ajax/ext/package/date.js" type="text/javascript"></script>
   
   <script language="JavaScript" type="text/javascript">
   setDateRenderer = function(){
      mygrid = ColdFusion.Grid.getGridObject('CouponsGrid');
      cm = mygrid.getColumnModel();
      cm.setRenderer(3, Ext.util.Format.dateRenderer('m/d/Y'));
      mygrid.reconfigure(mygrid.getDataSource(),cm);
   }
   </script>
</head>

<body>

<cfform id="CouponForm" name="CouponForm">
<cfgrid name="CouponsGrid"
format="html"
pagesize="10"
striperows="yes"
selectmode="edit"
bind="cfc:coupons.getCoupons({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
onchange="cfc:coupons.editCoupon({cfgridaction},{cfgridrow},{cfgridchanged})">
<cfgridcolumn name="Couponid" display="false" />
<cfgridcolumn name="SPONSORID" header="Sponsor" width="100"/>
<cfgridcolumn name="COUPON" header="Coupon" width="100"/>
<cfgridcolumn name="EXPIRATIONDATE" header="Exp Date" width="200"/>
</cfgrid>
</cfform>


<cfset ajaxOnLoad("setDateRenderer")>
</body>
</html>

</html>

coupons.cfc

<cfcomponent output="false">

<cfset THIS.dsn="cfbookclub">


<cffunction name="getCoupons" 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="">

   
   <cfset var data="">

   
   <cfquery name="data" datasource="#THIS.dsn#">
   SELECT COUPONID, SPONSORID, COUPON, EXPIRATIONDATE
   FROM COUPONS
   </cfquery>

   <cfset qryFinalQuery = QueryNew("COUPONID, SPONSORID, COUPON, EXPIRATIONDATE","varchar,varchar,varchar,varchar") />
   <cfloop query="data">
      <cfset QueryAddRow(qryFinalQuery) />
      <cfset QuerySetCell(qryFinalQuery, "COUPONID", "#data.COUPONID#") />
      <cfset QuerySetCell(qryFinalQuery, "SPONSORID", "#data.SPONSORID#") />
      <cfset QuerySetCell(qryFinalQuery, "COUPON", "#data.COUPON#") />
      <cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE, 'mm/dd/yyyy')#") />
   </cfloop>
   
<cfreturn QueryConvertForGrid(qryFinalQuery,
ARGUMENTS.page,
ARGUMENTS.pageSize)>

</cffunction>


<cffunction name="editCoupon" access="remote">
<cfargument name="gridaction" type="string" required="yes">
<cfargument name="gridrow" type="struct" required="yes">
<cfargument name="gridchanged" type="struct" required="yes">


<cfset var colname="">
<cfset var value="">


<cfswitch expression="#ARGUMENTS.gridaction#">

<cfcase value="U">

<cfset colname=StructKeyList(ARGUMENTS.gridchanged)>
<cfset value=ARGUMENTS.gridchanged[colname]>

<cfquery datasource="#THIS.dsn#">
UPDATE COUPONS
SET <cfswitch expression="#colname#">
                <cfcase value="EXPIRATIONDATE">#colname# = #createodbcdatetime(value)#</cfcase>
               <cfdefaultcase>#colname# = '#value#'</cfdefaultcase>
            </cfswitch>

WHERE COUPONID = #ARGUMENTS.gridrow.COUPONID#
</cfquery>
</cfcase>


<cfquery datasource="#THIS.dsn#">
DELETE FROM COUPONS
WHERE contactid = #ARGUMENTS.gridrow.COUPONID#
</cfquery>
</cfcase> --->
</cfswitch>
</cffunction>

</cfcomponent>

Click Here to See a Working Example

You will also notice in the getCoupons function in coupons.cfc, that there is a bit of a work around going on there. The issue is that the QueryConvertForGrid function does not mesh well with date fields, it automatically puts them into an uncommon date format that most people wouldn't want to use. I found a post on Gary Gilbert's blog that shows how to resolve this problem by rebuilding the query and forcing the date field to be a varchar field with the formatting you want.

Comments (43) |

Download | 23819 Views

Related Blog Entries

CF8 AJAX JavaScript Functions: Getting Underlying JavaScript Objects (November 29, 2007)
CF8 AJAX Features: Validating CFGrid Data (November 28, 2007)
CF8 AJAX Features: Controlling CFGrid Data with JavaScript (November 2, 2007)

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment]

Just curious why you didn't use the Ext dateRenderer?

http://extjs.com/deploy/ext/docs/output/Ext.util.F...

# Posted By todd sharp | 11/27/07 5:58 PM

@Todd,

Indeed, I tried that first but I couldn't figure out how to get it to work. I had my setDateRenderer function set up like:

setDateRenderer = function(){
      mygrid = ColdFusion.Grid.getGridObject('CouponsGrid');
      cm = mygrid.getColumnModel();
      cm.setRenderer(3, Ext.util.Format.dateRenderer('m/d/Y'));
      mygrid.reconfigure(mygrid.getDataSource(),cm);
   }

But, I kept getting the error "v.dateFormat is not a function".

That's when I decided to build my own function for formatting the date. At first I built it using functions in another dateFormat.js file I have used in the past, but then I realized that the CFIDE folder probably had the EXT date.js file somewhere, so I found it and used that instead.

After your comment I looked at it further, and It turns out that all I really needed to do was add that script tag importing the date.js file, that is why I was geting the "dateFormat is not a function" error.

# Posted By Scott Bennett | 11/27/07 6:16 PM

I went ahead and modified the JavaScript on the CouponForm.cfm example so it uses the Ext.util.Format.dateRenderer instead of the little function I built, since that is the better way to do it. In case anyone cares, I originally posted the javascript like this:

<script language="JavaScript" type="text/javascript">
   formatDates = function(data,cellmd,record,row,col,store){
if(!data){
return "";
}
if(!(data instanceof Date)){
data = new Date(Date.parse(data));
}
return data.dateFormat("m/d/Y");
}
   setDateRenderer = function(){
      mygrid = ColdFusion.Grid.getGridObject('CouponsGrid');
      cm = mygrid.getColumnModel();
      cm.setRenderer(3, formatDates);
      mygrid.reconfigure(mygrid.getDataSource(),cm);
   }
</script>

# Posted By Scott Bennett | 11/27/07 6:29 PM

Another thing I just realized is that I could also have used:

<cfajaximport tags="cfinput-datefield">

to import the date.js file instead of:

<script src="/CFIDE/scripts/ajax/ext/package/date.js" type="text/javascript"></script>

but it works either way.

# Posted By Scott Bennett | 11/27/07 6:37 PM

Scott,
One thing I noticed using an editable grid is that the renderer doesn't seem to affect the format once I click on a cell in the grid to edit it - so that when editing it shows a data as July, 10 2007 00:00:00 but when not editing it shows it as 2007-07-10. Any idea how to change the format for that as well so that I can send the data back as it is shown when not editing? Thanks.

# Posted By Kevin | 12/12/07 1:04 AM

One other method that may help people out is described at http://www.garyrgilbert.com/blog/index.cfm/2007/7/... - that sends the data in whatever format you want using the CF dateFormat function so it gets to the grid they way you want it.

# Posted By Kevin | 12/12/07 1:27 AM

@Kevin,

If you read the last paragraph of myblog entry above you will see that I touched on the subject you are referring to and I referenced that same entry from Gary Gilbert's blog.

# Posted By Scott Bennett | 12/12/07 1:26 PM

has anyone tried displaying both date and time? in the cfc I have something like #DateFormat(data.DateEntered,"mm-dd-yyyy")# #TimeFormat(data.DateEntered)# but the cell renderer ignores all of that. I wonder what the format requirements and syntax is to get the grid to display the way I want

# Posted By Michael White | 1/4/08 11:15 AM

If you are using my example above you would change the coupon.cfc so that the line:

<cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE, 'mm/dd/yyyy')#") />

would be like:

<cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE,'mm-dd-yyyy')# #TimeFormat(data.EXPIRATIONDATE)#") />

then in CouponForm.cfm you would change the date format strings in the setDateRenderer function and the dataValidator function to use the format 'm/d/Y h:i A' instead of 'm/d/Y'.

For a complete list of format characters read this page:

http://extjs.com/deploy/ext/docs/output/Date.html

# Posted By Scott Bennett | 1/4/08 12:44 PM

Sorry, the example above does not have a "dataValidator" function. You would only need to modify the date format string for the setDateRenderer function. The "dataValidator" function is in another post I wrote that demonstrates how to create javascript validation for data submitted in a CFGrid.

# Posted By Scott Bennett | 1/4/08 12:57 PM

Thank you, exactly what I was looking for!

# Posted By Michael White | 1/4/08 1:00 PM

now that I'm past that problem, I'm getting NaNNaNNaN 12:NaN when the field is null. is there a syntax like if dateentered = null then dateentered = " "

# Posted By Michael White | 1/4/08 3:32 PM

So just to be clear... The date field in your grid is not requried and you want to allow null values to be entered without throwing any errors, correct?

# Posted By Scott Bennett | 1/4/08 5:02 PM

Yes, one field is date/time entered and another is date/time approved... you always have date entered, but initially not yet approved

# Posted By Michael White | 1/4/08 6:42 PM

In the example above you would change the line in the CFC that is setting the query cell for the unrequired datetime column so that it checks to see if there is a value before formatting it for the grid like this:

<cfif trim(data.EXPIRATIONDATE) neq "">
         <cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE,'mm-dd-yyyy')# #TimeFormat(data.EXPIRATIONDATE)#") />
      <cfelse>
         <cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "") />
      </cfif>

# Posted By Scott Bennett | 1/4/08 7:09 PM

Why is only the last column (the date field) editable. You can double click and edit it but not the others.. In the grid you have the selectmode set to edit. Just curious if this is intentional or a bug in the grid. I like the idea of only making certain fields editable but didn't know that was possible.

Here's another post of yours that shows the same behavior..

http://www.coldfusionguy.com/ColdFusion/blog/index...

# Posted By Matt Grimm | 1/23/08 5:37 PM

When I originally wrote it, all the columns were editable, however, someone with a little less maturity than average took it upon themselves to write some profane and indecent things in those fields. So I made them not editable as they were not needed for that examples.

# Posted By Scott Bennett | 1/23/08 5:47 PM

so how did you make only certain fields editable?

# Posted By Matt Grimm | 1/23/08 5:59 PM

@Matt,

There is an attribute called "select" in the cfgridcolumn tag, the default is "Yes". If you set it to "No", the field cannot be selected or edited. So I updated the sponsorid and coupon columns to be like this:

<cfgridcolumn name="SPONSORID" header="Sponsor" width="100" select="No"/>
<cfgridcolumn name="COUPON" header="Coupon" width="100" select="No"/>

# Posted By Scott Bennett | 1/23/08 6:13 PM

nice! Thanks.

# Posted By Matt Grimm | 1/23/08 6:35 PM

Have you guys tried to sort the date column with custom rendering?
It doesn't sort the date column properly. It sorts by the first character of the date value like the following.
01/20/2008
02/20/2007
03//20/2007
I tried to cast from cfquery in my function and custom render
by using Ext.util.Format.dateRenderer but no luck. Is there something that I am totally missing here?

Thanks in advance for anyone who figured this out.

# Posted By frank | 5/14/08 7:08 PM

Good news!

I found a way that can bypass the date formatting by queryConvertForGrid(), and also can use Ext.util.Format.dateRenderer()

in MS-SQL:

SELECT convert(varchar,NM.createDate, 1) + ' ' + convert(varchar,NM.createDate, 108) AS Date
FROM XXX

Since this is the format that Ext1.0 date object takes, (see: http://extjs.com/deploy/ext-1.0.1/docs/output/Date... ), we can use Ext.util.Format.dateRenderer() without any additional JS!!!

Below is a more complete example:

<cfsavecontent variable="head">
   <script src="/CFIDE/scripts/ajax/ext/package/date.js" type="text/javascript"></script>
   <script type="text/javascript">
   applyCustomRenderer = function() {
      grid = ColdFusion.Grid.getGridObject('newMeterGrid');
      cm = grid.getColumnModel();

      cm.setRenderer(0, Ext.util.Format.dateRenderer('Y-m-d'));
      // 0 is the first cfgridcolumn

      grid.reconfigure(grid.getDataSource(), cm);
   };
   </script>
</cfsavecontent>

<cfhtmlhead text="#head#">
<cfset ajaxOnLoad("applyCustomRenderer")>

# Posted By Henry Ho | 6/1/08 11:35 AM

How do i change the color of an entire row? Say i want certain rows to be in certain color...how to do that?

# Posted By sneha | 8/2/08 12:09 AM

HI, thanks for the post, works great and I was looking for that date format forever!! I was wondering, how do I pass a variable to a cfargument for use in a WHERE clause. For instance, if in the query "data" I wanted to add something specific from the user or a node value from a tree, how do I pass it to the argument. I know my query works, but when I try to pass the variable, it tells me it's not a numeric value?? Any hints?? thanks
- Dave

# Posted By Dave | 9/8/08 9:07 PM

@Dave,

In the CFC you would add an Argument like:
<cffunction name="getCoupons" 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="SponsorID" type="numeric" required="yes">...


then in the bind expression on the CFGrid you would just add that parameter like:
<cfgrid name="CouponsGrid"
   format="html"
   pagesize="10"
   striperows="yes"
   selectmode="edit"
   bind="cfc:coupons.getCoupons({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},#session.SponsorID#)"
   onchange="cfc:coupons.editCoupon({cfgridaction},{cfgridrow},{cfgridchanged})"...

How you enter the data in the bind expression depends on your page and where the data is comming from. If you wanted to get the value in the selected node of a cftree, it would look something like:

bind="cfc:coupons.getCoupons({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{SponsorTree.node})"

Then in the "data" query you would use:

<cfquery name="data" datasource="#THIS.dsn#">
SELECT COUPONID, SPONSORID, COUPON, EXPIRATIONDATE
FROM COUPONS
WHERE SponsorID = #arguments.sponsorid#
</cfquery>

# Posted By Scott Bennett | 9/8/08 9:32 PM

Thanks Scott, that did it. One more thing I noticed, I'm experiencing a brief lag rendering the grid. The whole form hangs for about five seconds, no Loading... preloader or anything. I'm ok with it for the most part but I'm concerned a user might think the site froze. Any idea what might be the cause? Thanks again, great site! - Dave

# Posted By Dave | 9/8/08 9:50 PM

@Dave,

There are many things that could cause it to run slow, I would start by using firebug (http://www.coldfusionguy.com/ColdFusion/blog/index...)
to monitor all the ajax requests that are occurring in the background and see if you can spot which process is slowing things down and go from there.

# Posted By Scott Bennett | 9/8/08 10:37 PM

Im getting this Script error: ColdFusion.Grid is null or not an object when i try to implement your code. Does anyone know why that should occur ?

# Posted By Wade | 10/15/08 2:41 PM

First thing I would check is to make sure that your /cfide/scripts/ directory is mapped correctly.

try navigating to

http://<yourdomainhere>/CFIDE/scripts/ajax/r...

and see if the loading.gif shows up. If it doesn't then you need to map the virtual directory to your cfide folder. None of the Ajax stuff will work without this mapping set up correctly.

# Posted By Scott Bennett | 10/15/08 3:12 PM

I have added a grid that is working perfectly with the exception of NaN/NaN/NaN being displayed for a string that is not empty. I have two other fields set up identically both in my CF code and my SQL database with everything displaying perfectly. This third field is where I get the NaN/NaN/NaN displayed. If I click on this field to edit the data, the correct information displays. The sort works correctly for this field as well. The onlyh issue is the NaN/NaN/NaN showing up instead of the data.Any help on this would be great appreciated.

# Posted By JC | 1/14/09 11:55 AM

@JC,

In the one that is not working, are you certain that in the cfc function that you have bound to the grid is looping through the data results and rebuilding the query so that the date column is set to the varchar datatype and the date is formatted properly? Here is the portion of my cfc example above that does this:

<cfset qryFinalQuery = QueryNew("COUPONID, SPONSORID, COUPON, EXPIRATIONDATE","varchar,varchar,varchar,varchar") />
<cfloop query="data">
<cfset QueryAddRow(qryFinalQuery) />
<cfset QuerySetCell(qryFinalQuery, "COUPONID", "#data.COUPONID#") />
<cfset QuerySetCell(qryFinalQuery, "SPONSORID", "#data.SPONSORID#") />
<cfset QuerySetCell(qryFinalQuery, "COUPON", "#data.COUPON#") />
<cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE, 'mm/dd/yyyy')#") />
</cfloop>

# Posted By Scott Bennett | 1/14/09 12:48 PM

Scott,

I did loop through the query and set everything to varchar for type. I don't have any dates to worry with. The plot thickens further. The fourth column is where the NaN/NaN/NaN dispalys regardless of datatype, field, etc. I can cut out however many columns I want and the NaN/NaN/NaN is what shows in the fourth column. I have corrected this for now by creating a cfgridcolumn in the fourth position and setting display="no". I have attached my grid and cfc code below for reference. HELP!!! And thanks.

CFGRID:
<cfgrid name="phoneresults" colheadertextcolor="##000099" format="html" height="270" pagesize="10" striperows="yes" selectmode = "edit" bind="cfc:cfcs.lk_employee.EmployeeSearch1({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},'#session.LastName#','#session.Name#','#session.Phone#','#session.Cell#','#session.Pager#','#session.MailCode#')" onchange = "cfc:cfcs.lk_employee.editArtist({cfgridaction},{cfgridrow},{cfgridchanged})">

<cfgridcolumn name="LastName" header="Last Name" width="70">
<cfgridcolumn name="FirstName" header="First Name" width="70">
<cfgridcolumn name="Middle" header="M." width="20">
<cfgridcolumn name="EmpID" display="no">
<cfgridcolumn name="Nickname" header="Alias" >
<cfgridcolumn name="Phone" header="Phone"width="60">
<cfgridcolumn name="Pager" header="Pager" width="48">
<cfgridcolumn name="Cell" header="Cell" width="80">
<cfgridcolumn name="Home" header="Emergency Phone" width="80">
<cfgridcolumn name="OtherPhone" header="Emergency Phone" width="80">
<cfgridcolumn name="Building" header="Locale" width="200" select="yes" values="#LocaleIDList#" valuesdisplay="#LocaleList#">
<cfgridcolumn name="SubDeptNum" header="Dept." select="yes" values="#DeptIDList#" valuesdisplay="#DeptNameList#" width="50">
<cfgridcolumn name="MailCode" header="MC" width="40" select="yes" values="#MCList#" valuesdisplay="#MCList#">
<cfgridcolumn name="emailadd" header="E-Mail" href="mailto:" hrefkey="emailadd" width="180">
<cfgridcolumn name="uupic" header="UUPIC" width="70">
<cfgridcolumn name="UserName" header="U Name" width="60">
</cfgrid>

CFC:
<cffunction name="EmployeeSearch1" output="false" access="remote" returntype="any" >
<cfargument name="page"
   required="yes">
<cfargument name="pageSize"
   required="yes">
<cfargument name="gridsortcolumn"
   required="yes">
<cfargument name="gridsortdirection"
   required="yes">
   <cfargument name="LastName"
   type="string"
required="no"
default="">
<cfargument name="Name"
   type="string"
required="no"
default="">
<cfargument name="Phone"
   type="string"
required="no"
default="">
<cfargument name="Pager"
   type="string"
required="no"
default="">
<cfargument name="Cell"
   type="string"
required="no"
default="">
<cfargument name="MailCode" type="string" required="no" default="">

<cfquery name="getEmpInfo" datasource="cfInternal">
      SELECT
EmpID, LastName, FirstName, Middle, Nickname, Phone, Pager, Cell, Home, OtherPhone, emailadd, BldgNum, RoomNumber, Room, RoomID, MailCode, Office, DeptSub, Affiliation, Position, Image, Contact, Gone, Pending, EditBY, EditDate, SubID, SubDeptNum, OfficeDept, BldgID, Area, 'Bldg.'+Building+', Rm.'+RoomNumber AS Building, AreaArea,EmpIDEditBy, LastEditBy, FirstEditBy, MiddleEditBy, NickEditBy, SubDept, Title, DeptName, NewContract, AreaID, Contract, ContractName, Abbr, PosTitle, OrderofPosition, DualAssign, SubPosition, DeptCategory, UserName, RoomID, OfficeID,uupic
FROM VIEW_WSTFDirectory
WHERE Gone <> 1 and EmpID <> 681
<cfif #Arguments.LastName# NEQ ''>
and LastName LIKE <cfqueryparam value='#Arguments.LastName#%' cfsqltype='cf_sql_varchar' >
</cfif>
<cfif Arguments.Name NEQ '' >
and (FirstName LIKE <cfqueryparam value='#Arguments.Name#%' cfsqltype='cf_sql_varchar'>
or Middle LIKE <cfqueryparam value='#Arguments.Name#%' cfsqltype='cf_sql_varchar'>
or Nickname LIKE <cfqueryparam value='#Arguments.Name#%' cfsqltype='cf_sql_varchar'>)
</cfif>
<cfif Arguments.Phone NEQ ''>
and Phone LIKE <cfqueryparam value='%#Arguments.Phone#%' cfsqltype='cf_sql_varchar'>
</cfif>
<cfif Arguments.Pager NEQ ''>
and Pager LIKE <cfqueryparam value='%#Arguments.Pager#%' cfsqltype='cf_sql_varchar'>
</cfif>
<cfif Arguments.Cell NEQ ''>
and Cell LIKE <cfqueryparam value='%#Arguments.Cell#%' cfsqltype='cf_sql_varchar'>
</cfif>
<cfif Arguments.MailCode NEQ ''>
and MailCode = <cfqueryparam value='#Arguments.MailCode#' cfsqltype='cf_sql_varchar'>
</cfif>

       <cfif gridsortcolumn neq ''>
          order by #gridsortcolumn# #gridsortdirection#
<cfelse>
Order by LastName, FirstName, Middle
       </cfif>
   </cfquery>

   <cfset qryFinalQuery = QueryNew("EmpID,LastName,FirstName,Middle,Nickname,Phone,Pager,Cell,Home,OtherPhone,Building,SubDeptNum,emailadd,uupic,UserName","integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar") />
<cfloop query="getEmpInfo">
<cfset QueryAddRow(qryFinalQuery) />
   <cfset QuerySetCell(qryFinalQuery, "EmpID", "#getEmpInfo.EmpID#") />
   <cfset QuerySetCell(qryFinalQuery, "LastName", "#getEmpInfo.LastName#") />
<cfset QuerySetCell(qryFinalQuery, "FirstName", "#getEmpInfo.FirstName#") />
<cfset QuerySetCell(qryFinalQuery, "Middle", "#getEmpInfo.Middle#") />
<cfset QuerySetCell(qryFinalQuery, "Nickname", "#getEmpInfo.Nickname#") />
<cfset QuerySetCell(qryFinalQuery, "Phone", "#getEmpInfo.Phone#") />
<cfset QuerySetCell(qryFinalQuery, "Pager", "#getEmpInfo.Pager#") />
<cfset QuerySetCell(qryFinalQuery, "Cell", "#getEmpInfo.Cell#") />
<cfset QuerySetCell(qryFinalQuery, "Home", "#getEmpInfo.Home#") />
<cfset QuerySetCell(qryFinalQuery, "OtherPhone", "#getEmpInfo.OtherPhone#") />
<cfset QuerySetCell(qryFinalQuery, "Building", "#getEmpInfo.Building#") />
<cfset QuerySetCell(qryFinalQuery, "SubDeptNum", "#getEmpInfo.SubDeptNum#") />
<cfset QuerySetCell(qryFinalQuery, "emailadd", "#getEmpInfo.emailadd#") />
<cfset QuerySetCell(qryFinalQuery, "uupic", "#getEmpInfo.uupic#") />
<cfset QuerySetCell(qryFinalQuery, "UserName", "#getEmpInfo.UserName#") />
</cfloop>

<cfreturn QueryConvertForGrid(qryFinalQuery,page,pagesize)>

</cffunction>

# Posted By JC | 1/14/09 1:45 PM

@JC,

The data you are displaying in this grid doesn't have a date field so you don't need the javascript that adds the custom renderer, just remove the script and the ajaxonload() code and the error will go away.

You also don't need that loop to convert the date column into the varchar datatype because none of the column are dates in your CFC.

# Posted By Scott Bennett | 1/14/09 1:54 PM

Scott,

I knew it had to be something simple. That did it. Thanks for this and all of your help. You have been a God Send.

# Posted By JC | 1/14/09 1:57 PM

Is there anyway to edit cftree bind to cfc? I would like the user to be able to edit the tree. Such as editing the text, adding a node and moving a node? Any ideas or examples?

# Posted By keelee | 1/16/09 3:48 PM

The cftree/cftreeitem tags do not give you the ability to directly edit the nodes like you are talking about. However, having editable nodes is possible with the underlying YAHOO.widget.TreeView object. Here is an example:

http://developer.yahoo.com/yui/examples/treeview/t...

You will have to read the documentation here:

http://developer.yahoo.com/yui/docs/YAHOO.widget.T...

You can also use functions mentioned on that page to add, and remove nodes. I personally would recommend that you bypass the cftree tag and just use the YAHOO.widget.TreeView object directly for this project if you know javascript well enough to do that.

When I have some free time I will look into seeing if there is an easy way to use the cftree tag as a base and write an javascript function that will reformat the cftree and add the listeners that would be needed to do what you want, but unfortunately I just don't have the time right now to get that deep into it.

# Posted By Scott Bennett | 1/16/09 4:42 PM

I copied your examples from the top of the page and noticed that the columns were not sorting. I added some sorting to the SQL code like this:

<cfif ARGUMENTS.gridsortcolumn NEQ ""
and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
</cfif>

Now the Sponsor and Exp Date columns sort but when I click the Coupon column I get an error invoking the CFC... Error Executing the Database Query.

I am new to CF. Can you help?

# Posted By TC | 7/16/09 7:25 AM

There should be more to that error message telling you the error message returned by the database. That will give you a better idea of which part of the SQL is incorrect. If you are not seeing anything besides "error executing database query" in your error message, then try wrapping the query with a cftry/cfcatch block and dump the whole error. Then look for the queryError structure key to find the error message returned by the database server, and look at the sql structure key to review the sql that was sent to the database server by CF to help you determine where the problem is in your sql.

Here is an example of the cftry/cfcatch block you should use:

<cftry>



<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>

# Posted By Scott Bennett | 7/16/09 11:59 AM

I wrapped the statement as you said:


<cftry>


<cfquery name="data" datasource="#THIS.dsn#">
SELECT COUPONID, SPONSORID, COUPON, EXPIRATIONDATE
FROM COUPONS
<cfif ARGUMENTS.gridsortcolumn NEQ ""
and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
</cfif>
</cfquery>

<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>

And the error message did change but still seems cryptic to me:

Error invoking CFC cfexercises/coupons.cfc: Attribute validation error for tag cfloop.

On another note, I took your example, along with the modified SQL code, and changed it to show a list of projects I had stored in a database and it works amazing. The date is formatted correctly and all the columns are sorting as they should.

Thanks so much for taking the time to respond.

# Posted By TC | 7/16/09 4:44 PM

WOW. Just want to say, thank you thank you thank you. You've saved my bacon! No more wrangling with the HTML CFGrid.

# Posted By ajw93 | 4/16/10 5:25 PM

+1. Great code. thank you.

# Posted By Andy | 8/26/10 6:05 PM

I've basically trying the same thing in my code and I'm getting 'setDateRenderer' is undefined. I've made sure the that the:

setDateRenderer = function() and the <cfset ajaxOnLoad("setDateRenderer")>

match but to no avail. Any ideas appreciated.

# Posted By Jonathan | 1/16/11 8:08 PM

@Jonathan,

are you on CF9 or CF8? This example was written in CF8 and they changed a lot of stuff in terms of how they implemented Ext in CF9, so some of my hacks like this don't work in CF9.

# Posted By Scott Bennett | 1/20/11 2:07 PM

[Add Comment]

Get Expert ColdFusion Help

If you are looking for an experienced, professional, and reliable ColdFusion development company, contact PALADEM today.

Archives By Subject

AJAX (24) [RSS]
BlogCFC (2) [RSS]
CFEclipse (4) [RSS]
ColdFusion (50) [RSS]
CSS (1) [RSS]
FireFox (1) [RSS]
Frameworks (1) [RSS]
Google API (1) [RSS]
JavaScript (25) [RSS]
Lucee (1) [RSS]
My Life (6) [RSS]
OCCFUG (2) [RSS]
PayPal (1) [RSS]
Regular Expressions (1) [RSS]
XML (4) [RSS]