objRS.Filter problem

Discussion in 'ASP General' started by Edward Burns, May 29, 2005.

  1. Edward Burns

    Edward Burns Guest

    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

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Edward Burns, May 29, 2005
    #1
    1. Advertising

  2. Edward Burns wrote:
    > 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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], May 29, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dmitry Duginov

    GAC + Http module (ISAPI filter) problem

    Dmitry Duginov, Feb 25, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    732
    Dmitry Duginov
    Feb 26, 2005
  2. Replies:
    3
    Views:
    1,669
    Bruce Barker
    Mar 20, 2006
  3. javaguy44
    Replies:
    0
    Views:
    541
    javaguy44
    Apr 1, 2004
  4. javaguy44
    Replies:
    0
    Views:
    2,711
    javaguy44
    Apr 1, 2004
  5. zax75
    Replies:
    1
    Views:
    1,133
Loading...

Share This Page