inner loop rs.movenext hitting EOF

I will try to keep this succinct, yet still explain the problem.

I was charged with approving OCT NOV and DEC vacation days for 160 people based on requested day off and seniority. Basically, the oldest person here gets the day off. No problem. I write an Access database and set up a query that does it fine.

I decide to port the lookup aspect of it to the web so that I can show off whiling honing my virtually non-existant ASP skills. I've accomplished alot, and the script works nicely (even though its ugly) - but the problem is (i think - I'm still not sure) that I have rs.movenext in the inner loop, so that it hits the EOF and gives an exception error. I cannot figure out how to rewrite my loops which work exactly like I want in every other respect.


<%

Dim adoCon, rs, strSQL, strSQL2
Dim i, style
Dim strDate
dim pctOff

Set adoCon = Server.CreateObject("ADODB.connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("fpdb/leave.mdb")
Set rs = Server.CreateObject("ADODB.Recordset")

strSQL2 = "SELECT vacation.day_off, vacation.status, e_info.e_name, e_info.e_email, e_info.e_eod, e_info.stop, m_info.m_name, m_info.m_phone, m_info.m_email FROM (m_info RIGHT JOIN e_info ON m_info.stop = e_info.stop) RIGHT JOIN vacation ON e_info.e_seid = vacation.e_seid WHERE (((vacation.day_off) = # 01/05/2005 #)) ORDER BY vacation.day_off, e_info.e_eod;"

rs.CursorType = 2
rs.LockType = 3
rs.Open strSQL2, adoCon
i = "0"
strDate = "9/30/2004"
intStaffing = 160

Response.Write ("<div align='center'>")
Response.Write ("<h1>Requests for 10/01/2004 through 01/07/2005</h1>")
Response.Write ("<h2>Rows in yellow are approved leave")
Response.Write (" ")

Do While not rs.EOF 'outer loop
if strDate = rs("day_off") then 'Done so days with no one scheduled off will not show
Response.Write ("<h2>" & rs("day_off") & "</h2>")
Response.Write ("<table align='center' border='1' width='75%'><tr><th>Number</th><th>Employee</th><th>Eod</th><th>Stop</th><th>Manager</th><th>Phone</th></tr>")
Do While rs("day_off") = strDate 'populates the table for a particular day
if strDate = "12/27/2004" then 'different percentages allowed for certain days
pctOff = .7
Elseif strDate = "12/28/2004" then
pctOff = .7
Elseif strDate = "12/29/2004" then
pctOff = .7
Elseif strDate = "12/30/2004" then
pctOff = .7
Elseif strDate = "11/22/2004" then
pctOff = .3
Elseif strDate = "11/23/2004" then
pctOff = .4
Elseif strDate = "11/24/2004" then
pctOff = .6
elseif strDate = "11/26/2004" then
pctOff = .6
elseif strDate = "12/20/2004" then
pctOff = .55
elseif strDate = "12/21/2004" then
pctOff = .6
elseif strDate = "12/22/2004" then
pctOff = .6
elseif strDate = "12/23/2004" then
pctOff = .75
end if
i = i + 1

if i > (intStaffing * pctOff) then 'if to set style and approved/not approved flag in DB
style = "white;"
approved = False
end if

Response.Write ("<tr style='background-color:" & style & "'>" & "<td align='left'>" & i & "</td>")
Response.Write ("<td><a href='mailto:" & rs("e_email") & "'>" & rs("e_name") & "</a>" & "</td>")
Response.Write ("<td>" & rs("e_eod") & "</td>")
Response.Write ("<td>" & rs("stop") & "</td>")
Response.Write ("<td><a href='mailto:" & rs("m_email") & "'>" & rs("m_name") & "</a>" & "</td>")
Response.Write ("<td>" & rs("m_phone") & "</td></tr>")

if approved = true then 'updates DB with approved or not approved
rs.FIELDS("status") = True
Else
rs.FIELDS("status") = False
End if
rs.movenext ' this is where the problem is

Loop
Response.Write ("</table><br>")
Response.Write ("<h3>" & 160*pctOff & " CRs can be allowed off on this day</h3><hr><hr>")
end if

'Reset all inner loop variables
i = "0"
style = "yellow;"
approved = True
pctOff = .1
strDate = DateAdd("d",1,strDate)
Loop

rs.Close
Set rs = Nothing
Set adoCon = Nothing
%>


I apologize for the messy code, I'm pretty new at this and it is a complex project for me. And lastly, this is just a page that only *I* have access to. When I push this out, I will build seperate pages for employee access - ones that DON'T update the DB.

Thank you for any help you can provide.

-NEExt

Why is the inner loop a loop in the first place? Shouldn't that be a simple IF statement and the MOVENEXT be the last thing done in the outer loop?

Well, the inner loop writes everyone who requested for a particular day which is why the rs.movenext is in that spot. I don't understand how I could write a 30-50 row table with an if statement.

The outer loop basically just resets the counters I use in the inner loop, and cycles through to the next day (leaving out the ones that have no data). I can't see a way to do it without 2 loops - but then like I said, I'm a newb at this.

I've tried an if statement to see if rs.EOF = true before the rs.movenext but I can't get it to work properly. That solution would be less than ideal anyway as breaking at that point leaves out data that I want written to the page.

Thanks for your assistance.

If the inner loop is to display all the people who've requested a specific day off then the outer loop is used to switch which day so your outer loop shouldn't have anything to do with the recordset. Your outer loop may also iterate through days that no one has requested off.

The problem you're seeing though may be happening because you're updating the recordset but not committing it, you should be calling "rs.Update" before "rs.MoveNext" but your inner loop should be where you check for the EOF condition as well.

Well, after another 3 hours of trouble shooting the problem I'm am absolutely at a loss.

The query is double sorted - first by day off request, then by seniority. So, I really do need two loops to put them in a table for each day off ordered by seniority. The rs.MoveNext has to be in the inner loop to drive the whole looping process.

No matter how I play with the code, or even start from scratch again trying to rethink my approach, I still come down to the same solution - which works great until I get to the end of the recordset - upon which time it excepts out.

If anyone can help me solve this dilema I would be most appreciative - im running out of time to play with this. I've cleaned up the code above and taken alot of the non-core functions out of it to make it simpler.

Do While not rs.EOF 'outer loop
if strDate = rs("day_off") then 'Done so days with no one scheduled off will not show
'these next two lines start the new table for the new day
Response.Write ("<h2>" & rs("day_off") & "</h2>")
Response.Write ("<table align='center' border='1' width='75%'><tr><th>Number</th><th>Employee</th><th>Eod</th><th>Stop</th><th>Manager</th><th>Phone</th></tr>")
'inner loop that populates the table for a particular day
Do While rs("day_off") = strDate
Response.Write ("<tr style='background-color:" & style & "'>" & "<td align='left'>" & i & "</td>")
Response.Write ("<td><a href='mailto:" & rs("e_email") & "'>" & rs("e_name") & "</a>" & "</td>")
Response.Write ("<td>" & rs("e_eod") & "</td>")
Response.Write ("<td>" & rs("stop") & "</td>")
Response.Write ("<td><a href='mailto:" & rs("m_email") & "'>" & rs("m_name") & "</a>" & "</td>")
Response.Write ("<td>" & rs("m_phone") & "</td></tr>")
rs.movenext 'this is where the problem is at the end of the recordset
Loop
'these two lines close the table off nicely, except at the end of the recordset where the code breaks.
Response.Write ("</table><br>")
Response.Write ("<h3>" & intStaffing*pctOff & " CRs can be allowed off on this day</h3><hr><hr>")
end if

i = "0"
strDate = DateAdd("d",1,strDate)
Loop

Roy's idea is right. You should loop through the days.
Try this:

dim strDate, endDate, prevDate, day_off

strDate = "10/1/2004"
endDate = "01/07/2005"
prevDate = ""
rs.movefirst
do while (DateDiff("d", strDate, endDate)<=0) and not rs.BOF and not rs.EOF)
day_off = rs("day_off")
if strDate = day_off then
if prevDate<>day_off then 'not same day off with the previous record
if prevDate<>"" then 'already created the table so close it
Response.Write ("</table>")
Response.Write ("<h3>" & intStaffing*pctOff & " CRs can be allowed off on this day</h3><hr><hr>")
end if
'these next two lines start the new table for the new day
Response.Write ("<h2>" & rs("day_off") & "</h2>")
Response.Write ("<table align='center' border='1' width='75%'><tr><th>Number</th><th>Employee</th><th>Eod</th><th>Stop</th><th>Manager</th><th>Phone</th></tr>")
end if
Response.Write ("<tr style='background-color:" & style & "'>" & "<td align='left'>" & i & "</td>")
Response.Write ("<td><a href='mailto:" & rs("e_email") & "'>" & rs("e_name") & "</a>" & "</td>")
Response.Write ("<td>" & rs("e_eod") & "</td>")
Response.Write ("<td>" & rs("stop") & "</td>")
Response.Write ("<td><a href='mailto:" & rs("m_email") & "'>" & rs("m_name") & "</a>" & "</td>")
Response.Write ("<td>" & rs("m_phone") & "</td></tr>")
prevDate = day_off 'remember previous date
end if
strDate = DateAdd("d",1,strDate)
rs.movenext
loop

That works REALLY well Glenn with one minor addition at the end.


rs.movenext
if rs("day_off") <> strDate then
strDate = DateAdd("d",1,strDate)
i = 0
end if


Thank you so much for the help. I *never* would have thought of doing it that way. All I could think about was two loops. Two loops just seemed like the most natural solution.

TY again! :thumbsup:

Wouldn't that caused an error after the last record is accessed? If you are at the last record and you do rs.movenext, the cursor will go to rs.EOF so accessing day_off field after movenext will cause an error.
The code should be:

rs.movenext
if not rs.EOF then
if rs("day_off") <> strDate then
strDate = DateAdd("d",1,strDate)
i = 0
end if
end if

I can't make it a compound If...Then statement as VBScript doesn't allow "short-circuiting" like in Javascript and other languages. This will not work as the second condition will still be evaluated even if the first one is True.

if not rs.EOF and rs("day_off") <> strDate then
strDate = DateAdd("d",1,strDate)
i = 0
end if

You are right. It did cause an error with certain search ranges and I came to the same conclusion as in your last post.

Thanks also for the explanation about compound if..thens. I have problems getting them to work as intended.

Anyway, everything is fully functional now and the bosses are impressed, which was the ultimate goal. Thanks again for your help.