Sunday, August 3, 2008

Hacking SQL to Save CFGRID: Image as column value, HREF link with RECORD ID

This is only part of solving a larger problem. The problem is using the HREF on CFGRID underlines every damn thing and I couldn't figure out how to get rid of it. I did find extensive notes on CSS styling for the grid, down to every single cell, but nobody crying about it on the internet had gotten any answer on how to get rid of the underline.

So I took the HREF parameter off my CFGRID because the underline looked so horrible and made it so unreadable I wouldn't even use the grid if I had to suffer that. Which left me with the following dilemma: since the grid no longer links to something semi-automatically (posts to a form, click chooses a whole row, you can pass the ID value you need), I would have to link to it manually.

As part of doing this I decided that much like the simple HTML table I've been using prior to trying to implement CFGRID, I would use a little icon that would intuitively tell people (a) to click that for a link, and (b) that it would open in a new window. So I had three things to figure out:

1. How to get an image as a column value, because CFGRID only lets you insert a query column name, not html, and

2. How to get an html HREF link into a column value, for the same reason, and

3. How to get the unique record ID into that link.

To get a single (just one, same image on every record) into a column value, I did the below. Note that if you need a different image for each record, and you can build that filename/loc from whatever values are in the query, you could do that too. Use the logic of the Record ID link below, and modify that.

SQL Server uses + to concatenate strings and columns. Other DBs may use things like CONCAT function. You use single quotes around every string, and no quotes around actual column names.

You have to render, aka CAST or CONVERT, integer fields to strings before you can concatenate them.

'<a target="_blank" href="myactionfile.cfm?id=' + CAST(p.myid AS varchar(12)) + '">' + '<img src="../shared/icons/external2.gif" border="0" /></a>' as 'See',

then in CFGRID, I just did

<cfgridcolumn name="See" width="35" display="yes" >

and that's it! That changed my ID to a string, and it tied it into a link and an image, so now in my column it looks like this:

You click on the little image and it opens that record in a new page.

1 comment:

shushant said...

how to have text inside hyperlink in cfquery.
My requirement is to display a column in cfgrid which have hyperlink (hyperlink text is column value ) and
on right click open in new tab for that i need to have a href tag in cfquery but how to append hyperlink text there"
selevct &#39< a href="url?id=&#39+columnid+&#39">&#39+columnid+&#39<a>&#39 as columnid from tablename...
this is not working....give ora01722 error......"