asp nested loop not working as expected

[rh71]

Have a form which displays all rows (about 800) from a table ("requests") and each row has a submit button of its own. Essentially an admin can go there and "approve" a row at a time with that submit button (row then disappears for next run). Within each row though, I am trying to do a couple drop-down columns containing additional information... this is where the nested loops are not quite working out.

http://img4.imageshack.us/img4/7576/test2jm.jpg

You can see the 1st row is the only one displaying the query results in the 2 drop-down columns. 2nd row and onward are showing the columns, but not the query results they're supposed to produce.

Essentially the structure of my ASP code is:

set rs = connR.execute (select for all rows in request table)
set rs2 = connR.execute (select statement to get servers for drop down 1)
set rs3 = connR.execute (select statement to get servers for drop down 2)

<&#37;while not rs.EOF%>
<form blah blah> <tr>

<td>
<%=rs("blahblah") %> (column 1 results)
</td>

<td>
 more =rs("blah") etc. etc. for the other columns
</td>

<td>
<select name="notsvr" id="notsvr">
<option value="none">--</option>
<%while not rs2.EOF%>
<option value="<%=rs2("blah")%>"><%=rs2("blah")%></option>
<%rs2.moveNext
wend %>
</select>
</td>

<td>
<select name="recsvr" id="recsvr">
<option value="none">--</option>
<%while not rs3.EOF%>
<option value="<%=rs3("blah")%>"><%=rs3("blah")%></option>
<%rs3.moveNext 
wend %>
</select>
</td>

<td>
"status" column with hard-coded options...
</td>

<td>
comment & submit button
</td>

</tr> </form>
<%rs.moveNext 
wend %>

Now the -- options are visible in each which means it's just the nested loops which aren't working. What am I doing wrong? Why is it not doing the nested loops on subsequent runs of the main loop?

 

[Hmongkeysauce]

I might be wrong here, but it seems like you may have to reset the pointer on rs2 and rs3 to the beginning after each of their respective while loops.

 

[rh71]

sorry I'm new to that - care to detail it out pls?

 

[Hmongkeysauce]

Judging from the resulting image that you linked, rs2 and rs3 reached EOF during the first loop of the most outter while loop (while not rs.EOF) and never get their pointers/cursor reset to the first item. Therefore, in each subsequent loop after the first, rs2 and rs3 will always be at EOF and their respective while loops will never run. Unless rs, rs2, and rs3 are read forward only, you should be able to do something like rs2.MoveFirst and rs3.MoveFirst before rs.MoveNext. If you can't do .MoveFirst on rs2 and rs3, then, you may have to store the results in a different data structure, i.e. array, and iterate through the array. I hope all this is clear.

 

[Markbnj]

Pull that stuff out into a code-behind file. Save your eyeballs before it's too late.

 

[rh71]

Judging from the resulting image that you linked, rs2 and rs3 reached EOF during the first loop of the most outter while loop (while not rs.EOF) and never get their pointers/cursor reset to the first item. Therefore, in each subsequent loop after the first, rs2 and rs3 will always be at EOF and their respective while loops will never run. Unless rs, rs2, and rs3 are read forward only, you should be able to do something like rs2.MoveFirst and rs3.MoveFirst before rs.MoveNext. If you can't do .MoveFirst on rs2 and rs3, then, you may have to store the results in a different data structure, i.e. array, and iterate through the array. I hope all this is clear.

Thnx - I tried movefirst and it worked, but it was entirely too slow loading through all 800 rows... then I tried the array method and while that was better, it's still not ideal. I was thinking of doing paging to help that but I suppose presenting the 2 fields for selection on a subsequent page may be simpler. Thanks though, learned some things.

 

[BoberFett]

The fact that you can use a .MoveFirst means that you're using one of the heavier types of recordsets, and those are extremely slow.

http://www.w3schools.com/ado/ado_ref_recordset.asp

Read up on this page, it lists the ADO recordset types in order of speed: Dynamic, Keyset, Static, Forward Only. By default, the recordset comes back as Dynamic. The other types get faster, but cut back on the functionality they provide.