Solved

CF Grid help

Posted on 2010-11-29
22
266 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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This video teaches users how to migrate an existing Wordpress website to a new domain.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now