objRS.Filter problem

[Edward Burns]

I am trying to create an events calendar with a complete month view. I
want to be able to get all the events for a particular month, using only
one recordset on the page then be able to loop through each day of that
month and output the events for a particular day for each day without
having to open up 28 to 31 different recordsets.

The problem that I am having is with the objRS.Filter on line #12 of the
asp code below. I can not figure out how to write it so that it finds
events like "Event 4", in the *** SQL SERVER RESULTS ***, which is an
event that spans multiple days, so it should appear on the calendar on
the following days: 11, 12, 13, 14, 15. As the objRS.Filter is written
now the event will only appear on the 11th and the 15th. Another problem
I see with the way the filter is that events like "Event 7" will not
appear properly on the calendar. Event 7 starts on May 29th and ends on
June 3, so how do I tell the filter to ignore May 3 and just display
records for the 29th, 30th and 31st?

=========================
Here is my SQL Statement:
=========================
SELECT EventId, dtStartDate, dtEndDate, datepart(d, dtStartDate) as
nDayStart, datepart(d, dtEndDate) as nDayEnd, EventTitle
FROM Events
WHERE (dtStartDate BETWEEN '5/1/2005' AND '5/31/2005') or (dtEndDate
BETWEEN '5/1/2005' AND '5/31/2005')
ORDER BY datepart(d, dtStartDate), datepart(d, dtEndDate),
CAST(dtStartTime as smalldatetime);


**** SQL SERVER RESULTS ****

EventId dtDate dtEndDate nDayStart
nDayEnd EventTitle
-------- ---------------------- --------------------- ----------
-------- -----------
134 2005-05-05 00:00:00 2005-05-05 00:00:00 5 5
Event 1
207 2005-05-07 00:00:00 2005-05-07 00:00:00 7 7
Event 2
163 2005-05-10 00:00:00 2005-05-10 00:00:00 10 10
Event 3
172 2005-05-11 00:00:00 2005-05-15 00:00:00 11 15
Event 4
185 2005-05-12 00:00:00 2005-05-12 00:00:00 12 12
Event 5
152 2005-05-25 00:00:00 2005-05-25 00:00:00 25 25
Event 6
120 2005-05-29 00:00:00 2005-06-03 00:00:00 29 3
Event 7

(7 row(s) affected)


=========================
Here is my ASP Statement
that builds the month view
of the calendar:
=========================
dtDate = Now()

1. set objRS = objConn.execute(strSQL)
2. do until (Month(dtDate) <> CInt(nMonth))
3. if Weekday(dtDate) = 1 or Weekday(dtDate) = 7 then
4. 'Sunday or Saturday so color them differently than the regular
weekdays
5. Response.Write "<td width='" & CellWidth & "' class='weekenddays'
valign='top'>"
6. else 'weekday
7. Response.Write "<td width='" & CellWidth & "' class='days'
valign='top'>"
8. end if
9. Response.Write "<font class='daynumbers'><b>"& Day(dtDate) &
"</b></font><br>"
10.
11. if not objRS.EOF then
12. objRS.Filter = "(nDay = " & DatePart("d", dtDate) & ") OR
(nDayEnd = " & DatePart("d", dtDate) & ")"
13.
14. if not objRS.EOF then
15. do while not objRS.EOF
16. Response.Write "<a href='" & SiteURL &
"/events.asp?EventId=" & objRS("EventId") & "&nDate=" &
server.urlencode(month(dtDate) & "/" & day(dtDate) & "/" & year(dtDate))
& "' class='CalEventLinks'>" & revnewstr(objRS("EventTitle")) &
"</a><br><br>"
17. objRS.MoveNext
18. loop
19. else
20. response.write("<img src='" & SiteURL & "/images/spacer.gif'
height='50' width='25' border=0>")
21. end if
22. objRS.Filter = ""
23. else
24. response.write("<img src='" & SiteURL & "/images/spacer.gif'
height='50' width='25' border=0>")
25. end if
26.
27. Response.Write "</td>" & vbCR
28.
29. if WeekDay(dtDate) = 7 then
30. Response.Write "</tr>" & vbCrLf & vbCRLF & "<tr>"
31. end if
32. 'add one day for next time around in loop
33. dtDate = DateAdd("d", 1, dtDate)
34. dtFullDate = DateAdd("d", 1, dtFullDate)
35. loop
36. objRS.close
37. Set objRS = Nothing

Any input is greatly appreciate!

Thanks,

Ed

 

[Bob Barrows [MVP]]

I am trying to create an events calendar with a complete month view. I
want to be able to get all the events for a particular month, using
only one recordset on the page then be able to loop through each day
of that month and output the events for a particular day for each day
without having to open up 28 to 31 different recordsets.

First of all, create and populate a Calendar table:

create table Calendar
(calDate datetime primary key clustered)
declare @i int
set @i = 1
while @i < 366
begin
insert into calendar values (dateadd(d,@i,'20041231'))
set @i = @i + 1
end

You will find other uses for this table, believe me (such as adding a column
to indicate whether or not the day is a working day, or a column to indicate
the week_of_month number, in the case of a fiscal period calendar).

Then, generate the events for the month:

select caldate,datepart(dw,caldate) DayOfWeek,
eventid,eventtitle
from events e right join calendar c
on c.caldate between e.dtStartdate and e.dtEndDate
where caldate between '20050501' and '20050531'
order by caldate, eventid

Looping through these results should be a little easier for you. I don't
really have time to analyze your code to figure out how you want the results
to look, so i can't get too specific here.

Bob Barrows