Solved

CF Grid help

Posted on 2010-11-29
22
251 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

707 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

19 Experts available now in Live!

Get 1:1 Help Now