cfgrid format="html" numbers sorted as text?

I am using CF9 and cfgrid, with cfgrid format="html".  

The cfgrid sorts simple numbers in text order, not number order:

0
1
10
101
11
12
2
21

How do I fix this?
The grids sorts correctly if format="flash", but I want to use format="html".

Here is my code:
<!--- cfgrid.cfc --->
<cffunction name="getLogins" returnType="query" access="remote" hint="Retrieves logins">
<cfset var data="">
<cfquery name="data" datasource="dsn">
SELECT PreferredName, TotalLogins FROM cfgriddata
</cfquery>
<cfreturn data>
</cffunction>

Open in new window

and
<!--- test.cfm --->
<cfinvoke component="cfgrid" method="getLogins" returnvariable="logins">
</cfinvoke>
<cfform action="" method="post" id="form1">
 <cfgrid name="data" width="260" query="logins" colheaderbold="Yes" font="Tahoma" fontsize="14" rowHeaders="No" height="600" format="html"> 
	<cfgridcolumn name="PreferredName" header="Name" width="200"> 
	<cfgridcolumn name="TotalLogins" type="numeric" header="Logins" width="60" dataalign="right"> 
</cfgrid>
</cfform>

Open in new window

The mysql table is:
-- ----------------------------
--  Table structure for `cfgriddata`
-- ----------------------------
DROP TABLE IF EXISTS `cfgriddata`;
CREATE TABLE `cfgriddata` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `PreferredName` varchar(24) NOT NULL DEFAULT '',
  `TotalLogins` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=851 DEFAULT CHARSET=utf8 PACK_KEYS=1;

-- ----------------------------
--  Records of `cfgriddata`
-- ----------------------------
BEGIN;
INSERT INTO `cfgriddata` VALUES ('19', 'Carol', '11'), ('33', 'Mary', '1'), ('98', 'Lucille', '0'), ('413', 'Max', '21'), ('679', 'Kate', '10'), ('708', 'Julie', '101'), ('728', 'William', '3'), ('748', 'Dawn', '12'), ('785', 'Judy', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Open in new window

ShanghaiDAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ShanghaiDConnect With a Mentor Author Commented:
I've found a fix by changing from "query" to "bind" and now cfgrid sorts correctly with format="html" (which I want instead of format="flash" so I can use Raymond Camden's neat formatting tricks).

Exctracted revisions to my code are below (in case anyone is interested):
<cffunction name="logins"
access="remote"
returntype="struct">
....
....
<cfreturn QueryConvertForGrid(data,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</cffunction>


<cfgrid
....
....
bind="cfc:cfgrid.getLogins({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection})">

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

did you try to put order by within query.


<cfquery name="data" datasource="dsn">
SELECT PreferredName, TotalLogins FROM cfgriddata order by totallogins
</cfquery>

Open in new window

0
 
ShanghaiDAuthor Commented:
Yes, it sorts correctly then (when the grid first loads the query result).  However, the sort order reverts to text order when the cfgrid sorts each column (PreferredName by person name -- correclty -- and TotalLogins by number -- incorrectly -- reverts to text sort order).
0
 
ShanghaiDAuthor Commented:
Alternative solution found.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.