• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Compairing a db record date with a dynamic calendar to make date into a hyperlink problem. Please help

Participant ,
May 19, 2009 May 19, 2009

Copy link to clipboard

Copied

Hello;

I need help. I have a dynamic calendar I have been writting. (I realize there are lots of free ones online but I needed one to do something else. this is only a part of the whole.I have everyhting working fine, it advances to the next month and looks really nice.I can't get my query to work. I had it throwing an error, but now it is just spitting out data I didn't want.I will post the code that is the problem, then the whole code for the calendar.

This is the problem code:

<cfset firstOfTheMonth = createDate(year(now()), month(now()), 1)>
<cfset endOfTheMonth = createdatetime(year(now()), month(now()), #daysinmonth(firstOfTheMonth)#, 23, 59, 59)>
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
Where eventDate Between #firstOfTheMonth# and #endOfTheMonth#
</cfquery>

<cfoutput>
<cfif thisday is day(now())>
        <b>#ThisDay#</b>
    <cfelse>
        <cfif listFind(days,thisday)>
            <a href="##"><b>thisday</b></a>
        <cfelse>
            #thisday#
        </cfif>
    </cfif>
</cfoutput> 

this is the whole code for the calendar part:

<!--- Declaration of the variables --->
       <cfparam name = "month" default = "#DatePart('m', Now())#">
<cfparam name = "year" default = "#DatePart('yyyy', Now())#">
<cfparam name = "currentday" default = "#DatePart('d', Now())#">
<cfparam name = "startmonth" default = "#DatePart('m', Now())#">
<cfparam name = "startyear" default = "#DatePart('yyyy', Now())#">


<!--- Set a requested (or current) month/year date and determine the number of days in the month. --->

<cfset ThisMonthYear = CreateDate(year, month, '1')>
<cfset Days = DaysInMonth(ThisMonthYear)>
<!--- Set the values for the previous and next months for the back/next links.--->
<cfset LastMonthYear = DateAdd('m', -1, ThisMonthYear)>
<cfset LastMonth = DatePart('m', LastMonthYear)>
<cfset LastYear = DatePart('yyyy', LastMonthYear)>
<cfset NextMonthYear = DateAdd('m', 1, ThisMonthYear)>
<cfset NextMonth = DatePart('m', NextMonthYear)>
<cfset NextYear = DatePart('yyyy', NextMonthYear)>


<cfset PreviousDay = DateAdd('d', -1, ThisMonthYear)>
<cfset CurrentYear = DatePart('yyyy', Now())>

       <table border="0" width="100%" bgcolor ="#ffffff">
            <tr>
                <td align = "center" valign="top">
                    <table border="0" width="100%" height="100%">
   <tr>
      <th align="center" colspan="7" bgcolor="#2b4e6e">
   <cfoutput>
   <table width="100%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="15%" align="left" valign="middle">
  <cfif (LastYear lt CurrentYear) OR (LastYear lte CurrentYear AND LastMonth lt startmonth)>
<cfelse>
  <a href ="index.cfm?month=#LastMonth#&year=#LastYear#" class="calNav">Prev</a></cfif></td>
        <td width="72%" align="center" valign="middle"><FONT SIZE="3" face="Arial, Helvetica, sans-serif" color="##ffffff">#MonthAsString(month)# #year#</FONT></td>
        <td width="13%" align="right" valign="middle">
  <cfif (NextYear lt CurrentYear) OR (NextYear lte CurrentYear AND NextMonth lt startmonth)>
<cfelse>
<a href = "index.cfm?month=#NextMonth#&year=#NextYear#" class="calNav">Next</a>  </cfif></td>
      </tr>
    </table></cfoutput></th>
   </tr>
   <tr>
  
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Sun</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Mon</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Tue</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Wed</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Thu</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Fri</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Sat</FONT></td>

   </tr>
<cfset firstOfTheMonth = createDate(year(now()), month(now()), 1)>
<cfset endOfTheMonth = createdatetime(year(now()), month(now()), #daysinmonth(firstOfTheMonth)#, 23, 59, 59)>
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
Where eventDate Between #firstOfTheMonth# and #endOfTheMonth#
</cfquery>
<cfset ThisDay = 0>
<cfloop condition = "ThisDay LTE Days">
<tr class = "calendar">
                           
<cfloop from = "1" to = "7" index = "LoopDay">
<!--- This turns each day into a hyperlink if it is a current or future date --->
<cfoutput>
<cfif thisday is day(now())>
        <b>#ThisDay#</b>
    <cfelse>
        <cfif listFind(days,thisday)>
            <a href="##"><b>thisday</b></a>
        <cfelse>
            #thisday#
        </cfif>
    </cfif>
</cfoutput>                              
<cfif ThisDay IS 0>
<cfif DayOfWeek(ThisMonthYear) IS LoopDay>
<cfset ThisDay = 1>
</cfif>
</cfif>
<!---If the ThisDay value is still 0, or is greater than the number of days in the month, display nothing in the column. Otherwise, dispplay the day of the mnth and increment the value.--->
<cfif (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
<cfoutput>
<!--- I choose to highlight the current day of the year using an IF-ELSE. --->
<cfif (#ThisDay# EQ #currentday#) AND (#month# EQ #startmonth#) AND (#year# EQ #startyear#)>
<td align = "center" bgcolor="##FFFFCC">
<cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
<font face="Arial, Helvetica, sans-serif" color="##000000" size="2">#ThisDay#</font>
</td>
<cfelse>
<td align = "center">
<cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
<font face="Arial, Helvetica, sans-serif" color="##000000" size="2">#ThisDay#</font>
</td>
</cfif>
</cfoutput>
<cfset ThisDay = ThisDay + 1>
<cfelse>
<td></td>
</cfif>
</cfloop>
</tr>
</cfloop>
</table>
</td>
</tr>
</table> </td>
  </tr>
</table>

Can anyone help me with this? It is the last thing I need to get working and it is driving me crazy. Right now, this code does this:0

0

0

0

1

1

2

2

3

3

and then at the bottom of 32 numbers and doubles of numbers, is the calendar. So my llink code is making it do this.

Thank you!
I have to point out, it's pretty bad when Adobe is using jsp instead of coldfusion to run their forums.???????

TOPICS
Advanced techniques

Views

1.5K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 19, 2009 May 19, 2009

Copy link to clipboard

Copied

You posted more code than I'm willing to read, so let's focus on the part that you said was a problem.  You have a query and some if/else stuff.  To troubleshoot, look at your data.

After your query, cfdump it.  Do you see what you expect to see?

For your if else stuff, before you run it, display the values of day(now()), thisday, and days.   Then put together your if/else tags, but instead of outputting the value of a variable, output some string that tells you what tags returned true.  This should help you figure it out.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

I have been doing a lot of reading, and my code seems to be right accept for my where statement on my query that is matching the db date with the calendar date. (I think) This is what I did, and right now I get an error. I also dumped it and my date is posted at the bottom of this message in it's format.

This is my error:

Parameter validation error for the CREATEODBCDATE function.

The value of parameter 1, which is currently mm/dd/yyyy, must be a class java.util.Date value.

The error occurred in C:\website\event\index.cfm: line 157
155 : SELECT events.eventDate, events.ID AS ID 
156 : FROM events 
157 : WHERE eventDate = #CreateODBCDate("mm/dd/yyyy")#
158 : </cfquery>

ok this is now my code: (I realize it is long, I tried to make it as easy to follow as possible)

<!--- Declaration of the variables --->
       <cfparam name = "month" default = "#DatePart('m', Now())#">
<cfparam name = "year" default = "#DatePart('yyyy', Now())#">
<cfparam name = "currentday" default = "#DatePart('d', Now())#">
<cfparam name = "startmonth" default = "#DatePart('m', Now())#">
<cfparam name = "startyear" default = "#DatePart('yyyy', Now())#">


<!--- Set a requested (or current) month/year date and determine the number of days in the month. --->

<cfset ThisMonthYear = CreateDate(year, month, '1')>
<cfset Days = DaysInMonth(ThisMonthYear)>
<!--- Set the values for the previous and next months for the back/next links.--->
<cfset LastMonthYear = DateAdd('m', -1, ThisMonthYear)>
<cfset LastMonth = DatePart('m', LastMonthYear)>
<cfset LastYear = DatePart('yyyy', LastMonthYear)>
<cfset NextMonthYear = DateAdd('m', 1, ThisMonthYear)>
<cfset NextMonth = DatePart('m', NextMonthYear)>
<cfset NextYear = DatePart('yyyy', NextMonthYear)>


<cfset PreviousDay = DateAdd('d', -1, ThisMonthYear)>
<cfset CurrentYear = DatePart('yyyy', Now())>
       
<table border="0" width="100%" bgcolor ="#ffffff">
            <tr>
                <td align = "center" valign="top">
                    <table border="0" width="100%" height="100%">
   <tr>
      <th align="center" colspan="7" bgcolor="#2b4e6e">
   <cfoutput>
   <table width="100%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="15%" align="left" valign="middle">
  <cfif (LastYear lt CurrentYear) OR (LastYear lte CurrentYear AND LastMonth lt startmonth)>
<cfelse>
  <a href ="index.cfm?month=#LastMonth#&year=#LastYear#" class="calNav">Prev</a></cfif></td>
        <td width="72%" align="center" valign="middle"><FONT SIZE="3" face="Arial, Helvetica, sans-serif" color="##ffffff">#MonthAsString(month)# #year#</FONT></td>
        <td width="13%" align="right" valign="middle">
  <cfif (NextYear lt CurrentYear) OR (NextYear lte CurrentYear AND NextMonth lt startmonth)>
<cfelse>
<a href = "index.cfm?month=#NextMonth#&year=#NextYear#" class="calNav">Next</a>  </cfif></td>
      </tr>
    </table></cfoutput></th>
   </tr>
   <tr>
  
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Sun</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Mon</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Tue</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Wed</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Thu</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Fri</FONT></td>
    <td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Sat</FONT></td>

   </tr><br><br>
<!--- this is my problem query here--->

<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE eventDate = #CreateODBCDate("mm/dd/yyyy")#
</cfquery>
<!--- end query--->

                       <!--- Set the ThisDay variable to 0. This value will remain 0 until the day of the week on which the first day of the month falls on is reached. --->
                        <cfset ThisDay = 0>
                        <!--- Loop through until the number of days in the month is reached. --->
                        <cfloop condition = "ThisDay LTE Days">
                            <tr>
                           
                            <!--- Loop though each day of the week. --->
                            <cfloop from = "1" to = "7" index = "LoopDay">
                            <!--- This turns each day into a hyperlink if it is a current or future date --->
                                <cfif #PreviousDay# EQ Now() - 1>
                                    <cfoutput query="CaleventRec">
                                        <a href = "cal_day.cfm">#Days#</a>
                                    </cfoutput>
                                </cfif>
                               
                            <!---
                                If ThisDay is still 0, check to see if the current day of the week in the loop matches the day of the week for the first day of the month.
                                If the values match, set ThisDay to 1.
                                Otherwise, the value will remain 0 until the correct day of the week is found.
                            --->
                                <cfif ThisDay IS 0>
                                    <cfif DayOfWeek(ThisMonthYear) IS LoopDay>
                                        <cfset ThisDay = 1>
                                    </cfif>
                                </cfif>
                            <!---
                                If the ThisDay value is still 0, or is greater than the number of days in the month, display nothing in the column. Otherwise, dispplay
                                the day of the mnth and increment the value.
                            --->
                                <cfif (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
                                    <cfoutput>
                                    <!--- I choose to highlight the current day of the year using an IF-ELSE. --->
                                        <cfif (#ThisDay# EQ #currentday#) AND (#month# EQ #startmonth#) AND (#year# EQ #startyear#)>
                                            <td align = "center">
                                                <cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
                                                <font class = "calendartoday">#ThisDay#</font>
                                            </td>
                                        <cfelse>
                                            <td align = "center">
                                                <cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
                                                <font class = "calendar">#ThisDay#</font>
                                            </td>
                                        </cfif>
                                    </cfoutput>
                                   
                                    <cfset ThisDay = ThisDay + 1>
                                    <cfelse>
                                        <td></td>
                                </cfif>
                               
                               
                            </cfloop>
                            </tr>
                    </cfloop>
                    </table>
                </td>
            </tr>
        </table>

My date inthe database is like this = 1/1/2009

So my date is in teh DB with no time on it, I used code in the admin section to force an event date to only be allowed to be in that format.
How can I make this work? Is it only my where statement?

Thanks for the help so far.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

Read your error message.

Then look at line 157 of your code.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

That would be this:
<cfset ThisDay = 0>

my error says:
The value of parameter 1, which is currently mm/dd/yyyy, must be a class java.util.Date value.

what do I change this too? That is where I am lost

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

What line is this on?

<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE eventDate = #CreateODBCDate("mm/dd/yyyy")#
</cfquery>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

it is the where statement

WHERE eventDate = #CreateODBCDate("mm/dd/yyyy")#

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

And you don't see anything wrong with that line?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

yes, but what do I change it to? If I knew, I wouldn't be asking for help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

Change it to a date.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 21, 2009 May 21, 2009

Copy link to clipboard

Copied

I changed it to this:

WHERE eventDate = #CreateODBCDate("07/12/2005")#

but there is no link going into my calendar, but there is also no errors.

this is the code for the link:

<cfset PreviousDay = DateAdd('d', -1, ThisMonthYear)>
<cfset CurrentYear = DatePart('yyyy', Now())>

<cfif #PreviousDay# EQ Now() - 1>
                                    <cfoutput query="CaleventRec">
                                        <a href = "cal_day.cfm">#Days#</a>
                                    </cfoutput>
                                </cfif>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 22, 2009 May 22, 2009

Copy link to clipboard

Copied

There ya go, progress.  Now it's some if/else troubleshooting.  I do that like this.

<cfif something>

yes

<cfelse>

look at all relevent relevent data.  In this case, Now() - 1, and PreviousDay would be good candidates

</cfif>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 22, 2009 May 22, 2009

Copy link to clipboard

Copied

LATEST

When I put in teh date, I started messing with the if statement. I tried putting in a number of veriables and also tried to dump them, but the query doesn't seem to be grabbing what I need. Should we make the where statement compair the previous day and event date, then do the if? or just change the if?I also tried to combine the if state for the link, and the if statement that turns the current date yellow, if else it is normal.I was thinking, if I combined them, it might work, But that didn't work out too well. This is what I tried

<cfif #previousday# EQ #eventdate#>
you get a link
<cfelse>

it stays normal
</cfif>

I also tried a number of other ways to write this and they didn't want to take.So what combination will work, or do we need to change the where in teh query to match the record to the actual date?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation