Solved

CF Grid help

Posted on 2010-11-29
22
302 Views
Last Modified: 2013-11-19
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

0
Comment
Question by:sonicimpulse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 6
  • +1
22 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34231116
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34231134
check out this link for link in cfgrid.

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

you can retrieve value using cfgridkey
0
 

Author Comment

by:sonicimpulse
ID: 34231141
I got an error that says. "Error invoking component System Error"
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 14

Expert Comment

by:RickEpnet
ID: 34231201
What database server software are you using MS SQL or MySQL
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34231202
which database using???
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34231219
Hi,

You can use

#dateformat(expdate,'mm/dd/yy')#
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 34231389
Brichsoft that will not work in an HTML cfgrid.
0
 

Author Comment

by:sonicimpulse
ID: 34231541
I'm using mySQL 5
0
 

Author Comment

by:sonicimpulse
ID: 34231963
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
 
LVL 14

Expert Comment

by:RickEpnet
ID: 34232275
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
 
LVL 14

Expert Comment

by:RickEpnet
ID: 34232294
Acally more like this I did not test but shodul work.

SELECT DATE_FORMAT('1999-01-01', '%M %d %Y')
From database
0
 

Author Comment

by:sonicimpulse
ID: 34232314
The SQL statement is pulling all the records.  How would I use the SELECT if every date is different.
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 34233462
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
 

Author Comment

by:sonicimpulse
ID: 34233851
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
 

Author Comment

by:sonicimpulse
ID: 34234220
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 34234344

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
 

Author Closing Comment

by:sonicimpulse
ID: 34234405
This worked thanks.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34234503
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34236437
Agx sir,

sometime small addition makes biiiiiiiiiig difference.
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 34236542
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34239530
Thanks guys. I'll leave it up to the asker. But IMO this one was a collaborative effort :)
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34245914

Agree with agx....

And You were leading us......... ;-)
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This video teaches users how to migrate an existing Wordpress website to a new domain.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question