Loop within a Loop

[geronim0]

Hello all, new here and wanted to see if someone could offer me some advice.

I basically have an .asp page that selects records from a view and then loops through the records displaying the values in a table. In one of the columns I have a notes field. If there are any notes for the specific book, I have to query another table and then loop through the records displaying the notes.

It works, but my question is how can I make it better or more efficient. I know I dont want to open the database and recordset in the loop for every record like I am currently doing. Its a loop within a loop and I am trying to see the best way to accomplish it.

The first loop I use the getRows method and the second loop uses a different approach.

Any help in laying this out better is much appreciated.


<%
'connect to db
Set AC = server.CreateObject("adodb.connection")
AC.Open Application("databaseconnection")

'get userID
thisID = SESSION("user")

'selecting records from a SQL View that pertain to this user
SQL = "SELECT * FROM vwBooks " & _
" WHERE USERID = " & thisID

'open recordset
Set RS = AC.Execute(SQL)

'does recordset contain data? If so, get data and free resources
If Not(RS.EOF) then
mbook = RS.GetRows()
mbookcount = UBound(mbook, 2)
End If

'clean up
RS.Close
AC.Close
Set RS = Nothing
Set AC = Nothing
%>

<table cellspacing="1">

<%
'loop thru records
For row = 0 to mbookcount
%>
<tr>
<td><%=mbook(11, row)%></td>
<td><%=mbook(4, row)%></td>
<td><%=mbook(31, row)%></td>
<td>
<%
'get notes

'open db conn again
Set AC = server.CreateObject("adodb.connection")
AC.Open Application("databaseconnection")

'look in notes table to see if there are any notes on this book
SQL = "SELECT * FROM NOTES WHERE bookID = " & mbook(2, row)

'open recordset
Set RS = AC.Execute(SQL)

'does recordset contain data?
If Not(RS.EOF) then

'loop through all the notes, writing each one to a new line
do while not RS.EOF
response.write "<br>" & RS("bookNote")
RS.movenext
loop

End If

'clean up
RS.Close
AC.Close
Set RS = Nothing
Set AC = Nothingn
%>
<td><%=mbook(18, row)%></td>
<td><%=mbook(19, row)%></td>
</tr>
<%
Next
%>

</table>

[geronim0]

FYI, I have had some help here:
http://forums.aspfree.com/asp-development-5/loop-within-a-loop-394420.html

In case any one wants to chime in :)

thanks!

[Old Pedant]

Only comment I'll make is that all those people missed one important thing: STOP USING SELECT *

Look at your original code:

You do SELECT *, but then you only use data from columns 11, 4, 31, 18, 19.

ABSOLUTE waste to get all those other columns!!! You should *ALWAYS* select ONLY the fields you need. Much much more efficient.

I could also have pointed you here:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=154
but you seem to have finally arrived at that same answer.

I don't see any need for a dictionary, no matter what. The "disconnected" technique I show there for the second way works as well without the overhead of the dictionary.