Tuesday, January 15, 2008

Getting dates to appear correctly in an HTML cfgrid

A little while ago, I posted this to my blog, however I didn't give much context, I just dumped a pair of links to fairly dense articles. What do these links tell you? They tell you how to have SQL Server format things for you (in this case dates), similar to using the dateFormat function in ColdFusion. This can come in very handy when working with cfgrids.

Although the Adobe livedocs say that you can mask / format values that go into a grid, I haven't had much success with getting it to work; especially in the case of HTML grids.

A great work-around is just to use the SQL Server CONVERT function. How? If you plug a date/time/timestamp column name into CONVERT, and tell it what you want to convert it to, it will change the data-type of the date/time column (just in the resulting recordset, not in the actual table) to the data-type of your choice, and format the date for you to one of many predefined formats. Lots of examples of this and other techniques here:

http://www.oreilly.com/news/sqlnut_1200.html

For a list of predefined formats, and some more (not very useful) examples, look here:

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

So, if you need to use a date/time column in a cfgrid, just preformat it in the actual query like so:

SELECT CONVERT(VARCHAR(11), date_column, 102) AS formatted_date
FROM table
...etc.

I hope that makes more sense.

Wednesday, January 09, 2008

Binding to an HTML cfgrid

Wanna bind to an HTML cfgrid, and having trouble finding the correct syntax? Here it is:

bind="{gridName.columnName}"

I searched for a couple of hours, and could only find the flash forms syntax. Then I found it documented here:

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=ajaxui_5.html#1137151


Here is an example using cfinput to bind to the cfgrid :

<cfgrid name="user_grid" query="get_users">
<cfgridcolumn name="last_name">
<cfgridcolumn name="first_name">
<cfgridcolumn name="age">
</cfgridcolumn>

<cfinput type="text" name="the_last_name" bind="{user_grid.last_name}">

There you go. Enjoy.