Help with a date loop & error '80020009'

[mtarby]

I'm new here and hoping for a bit of help - I've got an table that has event name, start date, end date. For instance:
EventID: 1
MEETING_NAME: My Event
START_DATE: 1/1/2007
END_DATE: 1/4/2007

Right now we just display the name, start date through end date (if there's a different end date). So the above looks like:
My Event
1/1/2007 - 1/4/2007

Now instead, they want to have something like the following:

My Event 1/1/2007
My Event 1/2/2007
My Event 1/3/2007
My Event 1/4/2007

So I'm trying to write some code that loops through the range using dateadd:

Do While NOT rs.EOF 
sDate = rs("START_DATE") 
eDate = rs("END_DATE") 
thisdate = sDate 
do while thisdate <= eDate  
Response.Write "<tr><td><strong>" 
** Line 25 **  Response.Write thisdate & "</strong><br />"& rs("start_time") & " - " & rs("end_time") &  "</td><td>" 
Response.Write rs("MEETING_NAME") & "</td></tr>"  
 
thisdate = dateadd("d",1,sDate) 
rs.MoveNext  
loop 
             
Response.Write "</table>" 
loop 
rs.Close

I'm doing something wrong because the first record will display, then I get error '80020009'
/it/test.asp, line 25 (where line 25 is indicated above)

Can someone point me in the right direction? Thanks in advance!

[davidcairns]

remove rs.MoveNext from the inner loop and put it in the outer one. Also push the /table to the end as well

Do While NOT rs.EOF
sDate = rs("START_DATE")
eDate = rs("END_DATE")
thisdate = sDate
do while thisdate <= eDate
Response.Write "<tr><td><strong>"
** Line 25 ** Response.Write thisdate & "</strong><br />"& rs("start_time") & " - " & rs("end_time") & "</td><td>"
Response.Write rs("MEETING_NAME") & "</td></tr>"

thisdate = dateadd("d",1,sDate)
loop

rs.MoveNext

loop
Response.Write "</table>"

rs.Close

[mtarby]

Thanks for the help, but I'm sorry to say I'm still stuck.

When I try that, I get:
Response object error 'ASP 0251 : 80004005'
Response Buffer Limit Exceeded

So after poking around, I was wondering if a movenext would be needed for thisdate. I tried that and that gives me

Microsoft VBScript runtime error '800a01a8'
Object required: '4/21/2007'

So I'm not sure where to go next...

[davidcairns]

Change this line

thisdate = dateadd("d",1,sDate)

to this

thisdate = dateadd("d",1,thisdate)

[mtarby]

Thanks - I ended up going a different route, which works but revealed another problem. Now I'm seeing multiple dates, but my sort order gets messed up because the recurring events are kept together.

<%
Dim astrastart, astraend
astrastart = request.Form("startmonth")& "/"  & request.Form("startday") &  "/" & request.Form("startYear")
astraend = request.Form("endmonth")& "/"  & request.Form("endday") &  "/" & request.Form("endYear")
Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT  astra.EVNTINST.START_DATE, astra.EVNTINST.ID, astra.EVNTINST.START_TIME, astra.EVNTINST.NAME as [MEETING NAME], astra.EVNTINST.END_DATE, astra.EVNTINST.END_TIME, astra.EVNTINST.BUILDING, astra.EVNTINST.ROOM,  astra.EVENTREC.EVENT_NAME, astra.EVENTREC.EVENT_STATUS, astra.EVENTREC.EVENT_TYPE, astra.EVENTREC.CONTACT  FROM  astra.EVNTINST INNER JOIN astra.EVENTREC ON astra.EVNTINST.GROUPID = astra.EVENTREC.ID  WHERE astra.EVNTINST.BUILDING <> 'vans' AND astra.EVENTREC.EVENT_TYPE <> 'Exam' and astra.EVENTREC.EVENT_TYPE <> 'Review class' and astra.EVENTREC.EVENT_TYPE <> 'test' and convert(datetime, astra.EVNTINST.START_DATE)  >= '" & astrastart & "' AND convert(datetime, astra.EVNTINST.END_DATE) <= '" & astraend & "' order by convert(datetime, astra.EVNTINST.START_DATE), convert(datetime, astra.EVNTINST.start_time), astra.EVNTINST.id desc"
 
cn.Open ("Provider=sqloledb;Data Source=mssql;Initial Catalog=xxxxx;User Id=xxxxx;Password=xxxxx;")
rs.Open sql, cn
response.write "<link rel=""stylesheet"" href=""/style/normal.css"">"
response.write "<table class=""regsmall"" border=""1""  cellpadding=""5""><tr><td colspan=""6""><h3>Events Taking Place Between " & astrastart & " and " & astraend & "</h3></td></tr><tr><th>Date & Time</th><th>Event Name</th><th>Location</th><th>Status</th><th>Event Type</th><th>Contact</th>"
    Do While NOT rs.EOF
    mname = rs("MEETING NAME")
    mbuilding= rs("building")
    mroom=rs("room")
    mcontact=rs("contact")
    mstatus=rs("event_status")
    mtype=rs("event_type")
    mtime = rs("start_time") & " - " & rs("end_time")
    For thisdate = CDate(rs("START_DATE")) To CDate(rs("END_DATE"))
%>
    <tr>
        <td><strong><%=thisdate%></strong><br/><%=mtime%></td>
        <td><%=mname%></td>
        <td><%=mbuilding%> &nbsp; <%=mroom%></td>
            <td><%=mstatus%></td>
                <td><%=mtype%></td>
        <td><%=mcontact%></td>
    </tr>
<%
    Next
    rs.MoveNext
Loop
rs.Close
%> 
</table>

Is there a way to make sure the sort doesn't get ignored?