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
Solved

CF Grid help

Posted on 2010-11-29
22
285 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
The viewer will learn how to count occurrences of each item in an array.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

839 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