rs.eof prob... 1

[mthompo]

Hi,

have the following code which makes two lists
a list for holtypeid =1 and a list for holtypeid =2.
i dont want two rs, so i goto the first record for
the second list using rs.movefirst so the loop starts again - which is fine as long as there are records for holtypeid =1 or 2 , else i get an eof error.

hope i make sense

sql

sqlside= "SELECT CC.username, CB.bookst, CB.bookend, CC.userid, CB.holbookid, CB.holtypeid, holtype.holtypeimg "_
& "FROM holbook AS CB "_
& "LEFT JOIN user AS CC ON CC.userid = CB.userholid "_
& "LEFT JOIN holtype ON holtype.holtypeid = CB.holtypeid "_
& "WHERE manhol=" & 1 _
& " AND deptid=" & deptid _
& " AND ((cb.bookst BETWEEN '"&dstartdatesql&"' AND '"&denddatesql&"') "_
& "OR (cb.bookend BETWEEN '"&dstartdatesql&"' AND '"&denddatesql&"') "_ 
& "OR (cb.bookst <= '"& dstartdatesql& "') "_ 
& "AND (cb.bookend >= '"&denddatesql&"'))"

asp

Set RSside = Conn.Execute(sqlside)
<%' holiday quicklist
response.write "<br><img src = images/spacer.gif height=20>"
response.write "<div id=break></div>"
response.write "<table>"
response.write "<tr>"
response.write "<td><img src=""images/bookbuthol.gif""><span class = mfitsml>Holidays this Month</span></td>"
response.write "</tr>"
'rs.movefirst
Do while not rsside.eof
holtypeid = rsside("holtypeid")
if holtypeid = 1 then
    employee = rsside("username")
	bookst = rsside("bookst")
	bookend = rsside("bookend")

response.write "<tr>"
response.write "<td>><span class=courtcarreg>"& employee &"</span></td></tr><tr><td class=mfitsml>"& bookst & " to " & bookend &"</td>"
response.write "</tr>"
end if
rsside.movenext
loop
response.write "</table>"%>

<%' training quicklist
response.write "<br><img src = images/spacer.gif height=20>"
response.write "<div id=break></div>"
response.write "<table>"
response.Write "<tr><td height =10></td></tr>"
response.write "<tr>"
response.write "<td><img src=""images/bookbuttrain.gif""><span class = mfitsml>Training this Month</span></td>"
response.write "</tr>"

'this line is the one i cant work out!!
if not (rsside.eof) then
rsside.movefirst
end if

Do while not rsside.eof
holtypeid = rsside("holtypeid")
if holtypeid = 2 then
    employee = rsside("username")
	bookst = rsside("bookst")
	bookend = rsside("bookend")
response.write "<tr>"
response.write "<td><span class=courtcarreg>"& employee &"</span></td></tr><tr><td class=mfitsml>"& bookst & " to " & bookend &"</td>"
response.write "</tr>"
end if
rsside.movenext
loop
rsside.close
response.write "</table>"
response.write "<br><img src = images/spacer.gif height=20>"
response.write "<div id=break></div>"
%>

 

[mthompo]

also tried putting in

if not (rsside.eof) then
rsside.movefirst
end if

but of course this wont work because after
the end of the first list the rs is eof
so it never movesfirst

 

[Tarwn]

I can think of two options off the top of my head:
1) Create a variable for your second list and modify your loop so that if the holtypeid is 1, you Response.Write, but if the holtypeid is 2, you append to the variable. This way when your done with your loop you can simply output the value of your variable to get your second list.
The downside of this is that the string concatenations to continue appending the values for holtypeid 2 are inefficient.

[COLOR=red]Dim typeTwo[/color]
Do while not rsside.eof
holtypeid = rsside("holtypeid")
if holtypeid = 1 then
    employee = rsside("username")
    bookst = rsside("bookst")
    bookend = rsside("bookend")
    response.write "<tr>"
    response.write "<td>><span class=courtcarreg>"& employee &"</span></td></tr><tr><td class=mfitsml>"& bookst & " to " & bookend &"</td>"
    response.write "</tr>"
[COLOR=red]ElseIf holtypeid = 2 Then
   typeTwo = typeTwo & "<tr><td><span class=courtcarreg>"& employee &"</span></td></tr><tr><td class=mfitsml>"& bookst & " to " & bookend &"</td></tr>"[/color]
end if
rsside.movenext
loop
response.write "</table>"

[COLOR=red]'... later down after you start your new table
Response.Write typeTwo[/color]
%>

2) Add an order by statement to your SQL query and order by the holtypeid first (you may want additional fields after that, perhaps employee so your two lists will be alphabetically in order by employee). By ordering by the holtypeid you then start your loop as you have above, but break out of the loop when you hit a holtypeid that is equal to 2.

[b]Modified SQL Statement[/b]
sqlside= "SELECT CC.username, CB.bookst, CB.bookend, CC.userid, CB.holbookid, CB.holtypeid, holtype.holtypeimg "_
& "FROM holbook AS CB "_
& "LEFT JOIN user AS CC ON CC.userid = CB.userholid "_
& "LEFT JOIN holtype ON holtype.holtypeid = CB.holtypeid "_
& "WHERE manhol=" & 1 _
& " AND deptid=" & deptid _
& " AND ((cb.bookst BETWEEN '"&dstartdatesql&"' AND '"&denddatesql&"') "_
& "OR (cb.bookend BETWEEN '"&dstartdatesql&"' AND '"&denddatesql&"') "_
& "OR (cb.bookst <= '"& dstartdatesql& "') "_
& "AND (cb.bookend >= '"&denddatesql&"')) "[COLOR=red]_
& "ORDER BY CB.holtypeid"[/color]

[b]Modified Loops[/b]
Do while not rsside.eof
holtypeid = rsside("holtypeid")
if holtypeid =[COLOR=red] 2 then Exit Loop[/color]

employee = rsside("username")
bookst = rsside("bookst")
bookend = rsside("bookend")

response.write "<tr>"
response.write "<td>><span class=courtcarreg>"& employee &"</span></td></tr><tr><td class=mfitsml>"& bookst & " to " & bookend &"</td>"
response.write "</tr>"
end if
rsside.movenext
loop
response.write "</table>"%>

<%' training quicklist
response.write "<br><img src = images/spacer.gif height=20>"
response.write "<div id=break></div>"
response.write "<table>"
response.Write "<tr><td height =10></td></tr>"
response.write "<tr>"
response.write "<td><img src=""images/bookbuttrain.gif""><span class = mfitsml>Training this Month</span></td>"
response.write "</tr>"

'this line is the one i cant work out!!
[COLOR=red][s]if not (rsside.eof) then[/s]
[s]rsside.movefirst[/s]
[s]end if[/s][/color]

Do while not rsside.eof
holtypeid = rsside("holtypeid")

 

[mthompo]

thanks tarwn - your reply was very helpful and clearly explained the opions available - went number two
as this was the simplest!

changed the sql order and added this line after the do while loop for both lists
[except changed the holtypeid to 2 for the second list!]

if holtypeid <> 1 then exit do

and got rid of the other bits - works great, am sure it
it better than opening the rs again on the seconed loop
which is what i had done!

 

[mthompo]

forgot to give a star *

 

[Tarwn]

No problem, I meant to come back and check in and ran out of time yesterday Glad my post helped,

-T