CF Grid help

Two questions here on my CFGrid.

1st. How do I format the date colum to read mm/dd/yy
2nd. How do I add a link to the businessName Column


my CFC Component

<cffunction name="getParks" access="remote" returntype="struct">
	<cfargument name="page" required="true" />
	<cfargument name="pageSize" required="true" />
	<cfargument name="gridsortcolumn" required="true" />
	<cfargument name="gridsortdirection" required="true" />
	
	<cfif arguments.gridsortcolumn eq "">
		<cfset arguments.gridsortcolumn = "businessName" />
		<cfset arguments.gridsortdirection = "asc" />
	</cfif>
    
	<cfset todayDate = Now()>
	<cfquery name="ListCoups" datasource="DbBusiness">
		Select * from coupon,member
		Where coupon.memberid=member.id and coupon.expdate>#todayDate#
		Order by coupon.expdate asc
	</cfquery>

	<cfreturn queryconvertforgrid(listCoups, page, pagesize) />
</cffunction>

BELOW IS THE .CFM FILE

<cfform name="CouponForm">
	<cfgrid format="html" name="parkGrid" pagesize="10" selectmode="row" bind="cfc:listCoups.getParks({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})">
		<cfgridcolumn name="businessName" width="300" header="Business Name" />
		<cfgridcolumn name="cDescription" width="180" header="Coupon" />
		<cfgridcolumn name="city" width="120" header="City" />
		<cfgridcolumn name="expDate" width="60" header="Exp Date" />
	</cfgrid>
</cfform>

Open in new window

sonicimpulseAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:

SELECT *, DATE_FORMAT('expdate', '%M %d %Y')

A)  Get rid of the quotes around 'expdate' so mySQL knows it's a column, not a string
B)  You're missing the alias for that column.  So cfgrid can't use the result.
C)  Name the column something different than "expdate".  Having 2 columns named "ExpDate" might cause problems.

ie
SELECT  *, DATE_FORMAT(expdate, '%M %d %Y') AS NewExpDate
FROM     coupon,member
WHERE  coupon.memberid=member.id and coupon.expdate>#todayDate#
ORDER BY coupon.expdate asc

....
<cfgridcolumn name="NewExpDate" width="60" header="Exp Date" />

Though it's better *not* to use SELECT *
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

1st. How do I format the date colum to read mm/dd/yy

I suggest you to change in query as its more easy and more actual....else we need to lot of formatting in cf for setting  proper dd/mm/yy.

In your query

<cfquery name="ListCoups" datasource="DbBusiness">
                Select *,convert(varchar(10),coupon.expdate,1)expDate_change from coupon,member
                Where coupon.memberid=member.id and coupon.expdate>#todayDate#
                Order by coupon.expdate asc
        </cfquery>
0
 
Bhavesh ShahLead AnalysistCommented:
check out this link for link in cfgrid.

http://www.tek-tips.com/viewthread.cfm?qid=485425

you can retrieve value using cfgridkey
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
sonicimpulseAuthor Commented:
I got an error that says. "Error invoking component System Error"
0
 
RickEpnetCommented:
What database server software are you using MS SQL or MySQL
0
 
Bhavesh ShahLead AnalysistCommented:
which database using???
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

You can use

#dateformat(expdate,'mm/dd/yy')#
0
 
RickEpnetCommented:
Brichsoft that will not work in an HTML cfgrid.
0
 
sonicimpulseAuthor Commented:
I'm using mySQL 5
0
 
sonicimpulseAuthor Commented:
This is how it outputs the date

January, 14 2011 00:00:00

If I could just get rid of the 00:00:00 I would be fine with that.
0
 
RickEpnetCommented:
Like this

SELECT DATE_FORMAT('1999-01-01', '%X %V')
From database

Referance here for more options
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
0
 
RickEpnetCommented:
Acally more like this I did not test but shodul work.

SELECT DATE_FORMAT('1999-01-01', '%M %d %Y')
From database
0
 
sonicimpulseAuthor Commented:
The SQL statement is pulling all the records.  How would I use the SELECT if every date is different.
0
 
RickEpnetCommented:
I thought you would do somethign liek this no


SELECT DATE_FORMAT('1999-01-01', '%M %d %Y')
from coupon,member
Where coupon.memberid=member.id and coupon.expdate>#todayDate#
Order by coupon.expdate asc
0
 
sonicimpulseAuthor Commented:
RickEpnet your example is not giving me any errors.  Its pulls the correct number of records but all the row are empty in the CFGrid.  Any idea what we be causing this.
0
 
sonicimpulseAuthor Commented:
Here is my code below.  I added the * to populate my CFGrid.  Date is still not formated.  


<cfquery name="ListCoups" datasource="security">
     SELECT *, DATE_FORMAT('expdate', '%M %d %Y')
     FROM coupon,member
     WHERE coupon.memberid=member.id and coupon.expdate>#todayDate#
     ORDER BY coupon.expdate asc
</cfquery>

Open in new window

0
 
sonicimpulseAuthor Commented:
This worked thanks.
0
 
_agx_Commented:
sonicimpulse,

While I appreciated the points :), my comment was only a small addition. The real solution to the problem was pointed out by the other experts. Brichsoft pointed out the formatting should be done in the query (main problem), and RickEpnet provided the mySQL example.  I just added a small syntax correction.  So IMO points should definitely go to them :)

If you agree,  you can use the "Request Attention" link in the 1st post. Just ask the moderators if they could reopen the question.
0
 
Bhavesh ShahLead AnalysistCommented:
Agx sir,

sometime small addition makes biiiiiiiiiig difference.
0
 
RickEpnetCommented:
I as far as I am concerned you can leave them the way they are. agx is an excellent CF programmer. I have all the points I need this month anyway.  
0
 
_agx_Commented:
Thanks guys. I'll leave it up to the asker. But IMO this one was a collaborative effort :)
0
 
Bhavesh ShahLead AnalysistCommented:

Agree with agx....

And You were leading us......... ;-)
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.