Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    May 2004
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Code:
    <%
    
    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

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #3
    New Coder
    Join Date
    May 2004
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #5
    New Coder
    Join Date
    May 2004
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Code:
    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

  • #6
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,033
    Thanks
    0
    Thanked 248 Times in 245 Posts
    Roy's idea is right. You should loop through the days.
    Try this:
    Code:
    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

  • #7
    New Coder
    Join Date
    May 2004
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That works REALLY well Glenn with one minor addition at the end.

    Code:
      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!

  • #8
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,033
    Thanks
    0
    Thanked 248 Times in 245 Posts
    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:
    Code:
    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.
    Code:
    if not rs.EOF and rs("day_off") <> strDate then
       strDate = DateAdd("d",1,strDate)
       i = 0
    end if

  • #9
    New Coder
    Join Date
    May 2004
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •