do a google search for hierarchical recordsets.  It's a while since i've
done any of this, but I think it was on 4guysfromrolla.com that I found a
good article on this.

Basically you write a special query (you'll be shown how in the article),
use the MS Data Shape provider and return the recordset.  What's special is
that one of the fields in the RS is actually another RS, containing the
relevant submenu/sub-record fields for the current parent record.

HTH,
Col

-----Original Message-----
From: Andrew Banks [mailto:andy@n...]
Sent: 30 December 2002 03:33
To: ASP Databases
Subject: [asp_databases] Looping recordset data while inside a looped
recordset?


Hi,

I have a fairly basic requirement where I need to obtain data (lets say 
Menu & Submenu) from an access database.  ATM I have 2 recordsets that 
return the data to me.  Each recordset is looping through to obtain all 
records.  The problem is that I need the 2nd (Submenu) recordset to write 
it's data before the first records set finishes it's loop.

The reason for this is so that my Submenus will be written underneath the 
Menu items once the menu item is selected. As shown below.

MENU ITEM
  Submenu Item
MENU ITEM
MENU ITEM
etc etc.

The code that I have so far is working except for the fact that the 
Submenus are not written until the end of the (Menu) loop. Like this:

MENU ITEM
MENU ITEM
MENU ITEM
  Submenu Item
  Submenu Item
  etc etc.

Below is a copy of the code that is handling the looping and recordsets.  
If possible perhaps someone out there would be kind enough to give me some 
clues as how to best approach the problem. Many thanks in advance, Andy.


<link href="style.css" rel="stylesheet" type="text/css"> <table border="0" 
cellpadding="0" cellspacing="0" width="140" class="leftmenutable">
  <tr>
    <td width="140"></td>
    <td width="10"></td>
  </tr>
'MENU RECORDSET
<%
sqlstmt = "SELECT * from menus WHERE m_type = 0"  
sqlstmt = sqlstmt & " ORDER by m_name"
rs.open sqlstmt, conn,1 ,1
Do While Not rs.EOF 
m_id=rs("m_id")
m_name=(rs("m_name"))
m_action=rs("m_action")
Response.Write "<tr>"
Response.Write "<td align='right' width='140' bgcolor='#FFFFF0'>" & "<b><a 
title='"& rs("m_name") &"' href='?action="&m_id&"'>" & m_name&"</a></b>" 
& "</td>"
Response.Write "<td width='10' bgcolor='#FFFFF0'>&nbsp;</td>" 
Response.Write "</tr>" 
Response.Write "<tr>"
Response.Write "<td width='140' height='2' colspan='2' align='center' 
bgcolor=''#FF9900'>" 
Response.Write "<img border='0' alt='' src='gfx/nothing.gif' width='2' 
height='2'></td>"
Response.Write "</tr>"
rs.MoveNext
loop
rs.Close
%>
'SUBMENU RECORDSET
<%
sqlstmt = "SELECT * from submenus WHERE m_related="&sAction 
sqlstmt = sqlstmt & " ORDER by m_name"
rs.open sqlstmt, conn,1 ,1
If Not rs.EOF Then
Do While Not rs.EOF 
m_id=rs("m_id")
m_name=(rs("m_name"))
m_action=rs("m_action")
Response.write "<tr><td align='right' width='140'>&nbsp;"
Response.write "<a title='"& m_name &"' href='?
action="&saction&"&what="&m_id&"'>" & m_name & "</a>"
Response.write "&nbsp;</td>" 
if CDBL(m_id)=CDBL(sWhat) Then Response.write "<bgcolor='#FF9933'>"
Response.write "<td width='10'>&nbsp;</td></tr>"
Response.write "<tr><td width='140' height='1' 
background='gfx/bg_dots_grey.gif'></td>"
Response.write "<td width='10' height='1' 
background='gfx/bg_dots_grey.gif'></td></tr>"
rs.MoveNext
loop
rs.Close
%>


*******

This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.  If you are not the
intended recipient, please telephone or email the sender and delete this message and any attachment from your system.  If you are
not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.

For further information about Clifford Chance please see our website at http://www.cliffordchance.com or refer to any Clifford
Chance office.

You need to nest them.  One goes inside the other.  You have them one
after the other, and you are getting exactly the result you should be
getting because you are not nesting them.

> -----Original Message-----
> From: Andrew Banks [mailto:andy@n...]
> Sent: Monday, December 30, 2002 3:33 AM
> To: ASP Databases
> Subject: [asp_databases] Looping recordset data while inside a looped
> recordset?
> 
> Hi,
> 
> I have a fairly basic requirement where I need to obtain data (lets
say
> Menu & Submenu) from an access database.  ATM I have 2 recordsets that
> return the data to me.  Each recordset is looping through to obtain
all
> records.  The problem is that I need the 2nd (Submenu) recordset to
write
> it's data before the first records set finishes it's loop.
> 
> The reason for this is so that my Submenus will be written underneath
the
> Menu items once the menu item is selected. As shown below.
> 
> MENU ITEM
>   Submenu Item
> MENU ITEM
> MENU ITEM
> etc etc.
> 
> The code that I have so far is working except for the fact that the
> Submenus are not written until the end of the (Menu) loop. Like this:
> 
> MENU ITEM
> MENU ITEM
> MENU ITEM
>   Submenu Item
>   Submenu Item
>   etc etc.
> 
> Below is a copy of the code that is handling the looping and
recordsets.
> If possible perhaps someone out there would be kind enough to give me
some
> clues as how to best approach the problem. Many thanks in advance,
Andy.
> 
> 
> <link href="style.css" rel="stylesheet" type="text/css"> <table
border="0"
> cellpadding="0" cellspacing="0" width="140" class="leftmenutable">
>   <tr>
>     <td width="140"></td>
>     <td width="10"></td>
>   </tr>
> 'MENU RECORDSET
> <%
> sqlstmt = "SELECT * from menus WHERE m_type = 0"
> sqlstmt = sqlstmt & " ORDER by m_name"
> rs.open sqlstmt, conn,1 ,1
> Do While Not rs.EOF
> m_id=rs("m_id")
> m_name=(rs("m_name"))
> m_action=rs("m_action")
> Response.Write "<tr>"
> Response.Write "<td align='right' width='140' bgcolor='#FFFFF0'>" &
"<b><a
> title='"& rs("m_name") &"' href='?action="&m_id&"'>" &
m_name&"</a></b>"
> & "</td>"
> Response.Write "<td width='10' bgcolor='#FFFFF0'>&nbsp;</td>"
> Response.Write "</tr>"
> Response.Write "<tr>"
> Response.Write "<td width='140' height='2' colspan='2' align='center'
> bgcolor=''#FF9900'>"
> Response.Write "<img border='0' alt='' src='gfx/nothing.gif' width='2'
> height='2'></td>"
> Response.Write "</tr>"
> rs.MoveNext
> loop
> rs.Close
> %>
> 'SUBMENU RECORDSET
> <%
> sqlstmt = "SELECT * from submenus WHERE m_related="&sAction
> sqlstmt = sqlstmt & " ORDER by m_name"
> rs.open sqlstmt, conn,1 ,1
> If Not rs.EOF Then
> Do While Not rs.EOF
> m_id=rs("m_id")
> m_name=(rs("m_name"))
> m_action=rs("m_action")
> Response.write "<tr><td align='right' width='140'>&nbsp;"
> Response.write "<a title='"& m_name &"' href='?
> action="&saction&"&what="&m_id&"'>" & m_name & "</a>"
> Response.write "&nbsp;</td>"
> if CDBL(m_id)=CDBL(sWhat) Then Response.write "<bgcolor='#FF9933'>"
> Response.write "<td width='10'>&nbsp;</td></tr>"
> Response.write "<tr><td width='140' height='1'
> background='gfx/bg_dots_grey.gif'></td>"
> Response.write "<td width='10' height='1'
> background='gfx/bg_dots_grey.gif'></td></tr>"
> rs.MoveNext
> loop
> rs.Close
> %>
> to unsubscribe send a blank email to leave-asp_databases-
> 1112136X@p...