|
 |
asp_databases thread: Looping recordset data while inside a looped recordset?
Message #1 by "Andrew Banks" <andy@n...> on Mon, 30 Dec 2002 03:33:02
|
|
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'> </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'> "
Response.write "<a title='"& m_name &"' href='?
action="&saction&"&what="&m_id&"'>" & m_name & "</a>"
Response.write " </td>"
if CDBL(m_id)=CDBL(sWhat) Then Response.write "<bgcolor='#FF9933'>"
Response.write "<td width='10'> </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
%>
Message #2 by "M.Nataraj" <nataraj@f...> on Mon, 30 Dec 2002 11:20:51 +0530
|
|
Hi Andy,
Its Simple i guess .. let me explain n u try it out ..
' Ur Main Menu Item Query !
Set rs = Server.CreateObject ("Adodb.Recordset")
Set rsSub = Server.CreateObject ("Adodb.Recordset")
strSql = "Select * From Menus Where ...... "
rs.Open strsql, .......
While Not rs.Eof
m_id=rs("m_id")
m_name= Trim(rs("m_name"))
m_action=rs("m_action")
%>
<A Href = "Page.Asp?Menu_Id=<%=M_Id%>"><%=m_name%></A><BR>
<%
' Now Requesting the Menu ID for Generating the Sub Menu
MenuID = Trim(Request("Menu_Id"))
If MenuID <> "" Then
' If User Clicks the Menu Link Generated by You then it comes inside
..
If M_Id = MenuID Then ' If the Selected Menu Id and the Main Menu Id
is Equal then Generate Sub Menu
' Guess M_Id is the Foreign Key for your Sub Menu Table so that
Sub Menu for corressponding menu item can be populated
SubSql = "Select * From SubMenus Where M_Id = " & MenuID & "
Order by ........
If rsSub.State = 1 Then rsSub.Close
rsSub.Open SubSql, Con, ..........
While Not rsSub.Eof
' Getting the Sub Menu Id and Names
Subm_id=rsSub("SubM_id")
Subm_name= Trim(rsSub("Subm_name"))
Subm_action=rs("Subm_action")
%>
<A Href
"Page.Asp?SubMenu_Id=<%=SubM_Id%>"><%=Subm_name%></A><BR>
<%
rsSub.MoveNext
Wend
End If
End If
rs.MoveNext
Wend
rs.Close
rsSub.Close
i didn't try the above code yet so u have to try and say whether it works or
not .. i have just given u a sample and u modify for ur needs .. from the
above code the Sub Menu will be Only Generated after user clicks on a Menu
Item ..
Hope it works :-) !!
Good Luck,
Nataraj
----- Original Message -----
From: "Andrew Banks" <andy@n...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, December 30, 2002 3:33 AM
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'> </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'> "
> Response.write "<a title='"& m_name &"' href='?
> action="&saction&"&what="&m_id&"'>" & m_name & "</a>"
> Response.write " </td>"
> if CDBL(m_id)=CDBL(sWhat) Then Response.write "<bgcolor='#FF9933'>"
> Response.write "<td width='10'> </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
> %>
Message #3 by "slakshmi" <slakshmi@i...> on Mon, 30 Dec 2002 15:31:04 +0530
|
|
Hi Andy,
I just did the same in jsp:
Basically I included submenu.asp file in the main menu.asp file.
May be u can try this except samll changes in syntax..
"MENU.asp"
<% Set rs = Server.CreateObject ("Adodb.Recordset")
Set rsSub = Server.CreateObject ("Adodb.Recordset")
strSql = "Select menu_name,menu_id From Menus Where ...... "
rs.Open strsql, .......
%>
<TITLE>Menu CFA</TITLE>
<script language = "javascript">
function Btnclick()
{
if (advpart.style.display == "none")
{
advpart.style.display = "block";
}
else if (advpart.style.display == "block")
{
advpart.style.display = "none";
}
}
function btnclicksub(x)
{
if(x==0)
if (Cfapart.style.display == "none")
{
Cfapart.style.display = "block";
}
else if (Cfapart.style.display == "block")
{
Cfapart.style.display = "none";
}
}
else
{
if (Cfapart(x).style.display == "none")
{
Cfapart(x).style.display = "block";
}
else if (Cfapart(x).style.display == "block")
{
Cfapart(x).style.display = "none";
}
}
}
</script>
<BODY leftmargin="0" topmargin="0" marginwidth="0" marginheight="0"
bgcolor="steelblue">
<form name = menufrm method="get">
<font face ="verdana" size="2">
<br>
<div align="left">
<p>
<font color="white" face="Arial, Helvetica, san-serif" size=5
style="cursor:hand"
onclick="javascript:Btnclick();"><b>MENU</b></font><br><br>
'''' menu starts here..
<div id=advpart style="DISPLAY: block; POSITION: relative" align="left">
<% While Not rs.Eof
{
%>
<% 'If Menu has subitem in it..thru SQL.Suppose tmp_subitem is the count
of subitems for the menuitem
if(tmp_subitem = 0) 'No sub menu for the menu item
%>
<FONT face ="Arial, Helvetica, san-serif" size="1.5" color=white
style="cursor:hand" onclick="javascript:somefnc();
"> <b><%=rs("menu_name")%></b></FONT><br><br>
;
<%
else ' if sub menu is present for the menu item
m=0;
%>
<FONT face ="Arial, Helvetica, san-serif" size="5" color=white
style="cursor:hand"
onclick="javascript:btnclicksub(<%=m%>);"><b> <%=rs("menu_name")%
></b></FONT><br><br>
<input type="hidden" name="menuid" value =<%=rs("menu_id")%>>
<div id=Cfapart style="DISPLAY: none; POSITION: relative"
align="left">
''' Include submenu .asp here.
<%@ include file = "submenu.asp" %>
</div>
<%m=m+1;%>
<% %>
</div>
</p>
</div>
</form>
</BODY>
"submenu.asp:"
<% Set rs1 = Server.CreateObject ("Adodb.Recordset")
Set rsSub1 = Server.CreateObject ("Adodb.Recordset")
''querry as per..
strSql1 = "Select sub_menu_name,sub_menu_id From Menus Where menu_id
"+menu_id
rs.Open strsql, .......
%>
<TITLE>Sub Menu</TITLE>
<BODY>
<form name = "sbmenufrm">
<div align = "left">
<%while not rs1.eof
%>
<FONT face ="Arial, Helvetica, san-serif" size="1.5" color=white
style="cursor:hand" onclick="javascript:somefnc1();">
<b> <%=rs1("sub_menu_id")%> </b></FONT><br><br>
<%
rs1.next()
%>
</div>
</form>
</BODY>
Hope this helps you.
Cheers,
Sree.
----- Original Message -----
From: "M.Nataraj" <nataraj@f...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, December 30, 2002 11:20 AM
Subject: [asp_databases] Re: Looping recordset data while inside a looped
recordset?
> Hi Andy,
>
> Its Simple i guess .. let me explain n u try it out ..
>
> ' Ur Main Menu Item Query !
> Set rs = Server.CreateObject ("Adodb.Recordset")
> Set rsSub = Server.CreateObject ("Adodb.Recordset")
>
> strSql = "Select * From Menus Where ...... "
> rs.Open strsql, .......
>
> While Not rs.Eof
>
> m_id=rs("m_id")
> m_name= Trim(rs("m_name"))
> m_action=rs("m_action")
> %>
> <A Href = "Page.Asp?Menu_Id=<%=M_Id%>"><%=m_name%></A><BR>
> <%
>
> ' Now Requesting the Menu ID for Generating the Sub Menu
> MenuID = Trim(Request("Menu_Id"))
> If MenuID <> "" Then
> ' If User Clicks the Menu Link Generated by You then it comes
inside
> ..
>
> If M_Id = MenuID Then ' If the Selected Menu Id and the Main Menu
Id
> is Equal then Generate Sub Menu
>
> ' Guess M_Id is the Foreign Key for your Sub Menu Table so
that
> Sub Menu for corressponding menu item can be populated
> SubSql = "Select * From SubMenus Where M_Id = " & MenuID & "
> Order by ........
> If rsSub.State = 1 Then rsSub.Close
> rsSub.Open SubSql, Con, ..........
>
> While Not rsSub.Eof
> ' Getting the Sub Menu Id and Names
> Subm_id=rsSub("SubM_id")
> Subm_name= Trim(rsSub("Subm_name"))
> Subm_action=rs("Subm_action")
> %>
> <A Href
> "Page.Asp?SubMenu_Id=<%=SubM_Id%>"><%=Subm_name%></A><BR>
> <%
> rsSub.MoveNext
> Wend
> End If
>
> End If
>
> rs.MoveNext
> Wend
>
> rs.Close
> rsSub.Close
>
>
> i didn't try the above code yet so u have to try and say whether it works
or
> not .. i have just given u a sample and u modify for ur needs .. from the
> above code the Sub Menu will be Only Generated after user clicks on a Menu
> Item ..
>
> Hope it works :-) !!
>
> Good Luck,
> Nataraj
>
>
>
> ----- Original Message -----
> From: "Andrew Banks" <andy@n...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Monday, December 30, 2002 3:33 AM
> 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'> </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'> "
> > Response.write "<a title='"& m_name &"' href='?
> > action="&saction&"&what="&m_id&"'>" & m_name & "</a>"
> > Response.write " </td>"
> > if CDBL(m_id)=CDBL(sWhat) Then Response.write "<bgcolor='#FF9933'>"
> > Response.write "<td width='10'> </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
> > %>
>
>
Message #4 by "slakshmi" <slakshmi@i...> on Mon, 30 Dec 2002 17:26:51 +0530
|
|
Hey what is "sAction" in submenu querry?
What is the realtion b/w menu and submenu?
By any chance is the querry for submenu is something like this:
sqlstmt = "SELECT * from submenus WHERE m_related="&mAction
sqlstmt = sqlstmt & " ORDER by m_name"
wher mAction is menu name?
Can u make it clear pls.
Cheers,
Sree
----- Original Message -----
From: "Andrew Banks" <andy@n...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, December 30, 2002 3:33 AM
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'> </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'> "
> Response.write "<a title='"& m_name &"' href='?
> action="&saction&"&what="&m_id&"'>" & m_name & "</a>"
> Response.write " </td>"
> if CDBL(m_id)=CDBL(sWhat) Then Response.write "<bgcolor='#FF9933'>"
> Response.write "<td width='10'> </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
> %>
Message #5 by "Andy" <Andy@N...> on Tue, 31 Dec 2002 01:15:52 +1100
|
|
Hi,
Firstly thanks for the reply. Now I will try and explain to you if I
can what is happening.
Variables defined in a function.asp page include:
sAction=request.querystring("action")
sWhat=request.querystring("what")
sType=request.querystring("type")
I found this code on an asp forum that someone had developed, it wasn't
quite what I was looking for but the principle seemed to work for my
needs with a few changes.
I guess maybe if I provide more details you will get a better idea of
what is happening.
Database has 2 tables of interest; menus and submenus.
Menu contains: m_id, m_name, m_type.
Submenu contains: m_id, m_related, m_name, m_action.
The only relationship in the database is where m_id (menu table) is
m_related, the m_id in the submenu table is different to the m_id in the
menu table.
This seems to work okay at the moment, the only downfall I can see with
this setup is the fact that there will need to be an entry for every
submenu item that is related to a menu item, ie lets say I have a menu
for Clothing which needs a submenu called accessories, when another menu
requires a submenu called accessories I will need to create another
entry in the submenus table and relate it to the menu via the m_related
field. This will obviously make the database larger than it needs to
be, but for small projects this probably won't be a major drawback.
Unless there is a better way to go about it?
The code that I posted seemed to be the only way I could organise a
menu/submenu system that works on the same asp page. Like I said it all
pretty much works apart from the fact that the submenus open up at the
bottom of my menu items rather than popup in between them like I would
prefer.
If you need more info or copies of the code just drop me an email, I
appreciate the time you have taken in your reply.
Much thanks Andy.
-----Original Message-----
From: slakshmi [mailto:slakshmi@i...]
Sent: Monday, 30 December 2002 10:57 PM
To: ASP Databases
Subject: [asp_databases] Re: Looping recordset data while inside a
looped recordset?
Hey what is "sAction" in submenu querry?
What is the realtion b/w menu and submenu?
By any chance is the querry for submenu is something like this:
sqlstmt = "SELECT * from submenus WHERE m_related="&mAction
sqlstmt = sqlstmt & " ORDER by m_name"
wher mAction is menu name?
Can u make it clear pls.
Cheers,
Sree
----- Original Message -----
From: "Andrew Banks" <andy@n...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, December 30, 2002 3:33 AM
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'> </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'> "
> Response.write "<a title='"& m_name &"' href='?
> action="&saction&"&what="&m_id&"'>" & m_name & "</a>"
> Response.write " </td>"
> if CDBL(m_id)=CDBL(sWhat) Then Response.write "<bgcolor='#FF9933'>"
> Response.write "<td width='10'> </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
> %>
Message #6 by "M.Nataraj" <nataraj@f...> on Mon, 30 Dec 2002 20:37:02 +0530
|
|
ANDY:
Try the code which i sent to you .. its in single page only and u pass
the subMenu query as
SubSql = "Select * From SubMenus Where M_Related = " & MenuID & "Order by
........" as u said M_ID in both tables are Different .. but its always
better to have a diff field name in order to have a proper relationship ....
try the code and i guess it will populate the sub menu underneath only
- Nataraj
----- Original Message -----
From: "Andy" <Andy@N...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, December 30, 2002 7:45 PM
Subject: [asp_databases] Re: Looping recordset data while inside a looped
recordset?
>
> Hi,
>
> Firstly thanks for the reply. Now I will try and explain to you if I
> can what is happening.
>
> Variables defined in a function.asp page include:
>
> sAction=request.querystring("action")
> sWhat=request.querystring("what")
> sType=request.querystring("type")
>
> I found this code on an asp forum that someone had developed, it wasn't
> quite what I was looking for but the principle seemed to work for my
> needs with a few changes.
>
> I guess maybe if I provide more details you will get a better idea of
> what is happening.
>
> Database has 2 tables of interest; menus and submenus.
>
> Menu contains: m_id, m_name, m_type.
> Submenu contains: m_id, m_related, m_name, m_action.
>
> The only relationship in the database is where m_id (menu table) is
> m_related, the m_id in the submenu table is different to the m_id in the
> menu table.
>
> This seems to work okay at the moment, the only downfall I can see with
> this setup is the fact that there will need to be an entry for every
> submenu item that is related to a menu item, ie lets say I have a menu
> for Clothing which needs a submenu called accessories, when another menu
> requires a submenu called accessories I will need to create another
> entry in the submenus table and relate it to the menu via the m_related
> field. This will obviously make the database larger than it needs to
> be, but for small projects this probably won't be a major drawback.
> Unless there is a better way to go about it?
>
> The code that I posted seemed to be the only way I could organise a
> menu/submenu system that works on the same asp page. Like I said it all
> pretty much works apart from the fact that the submenus open up at the
> bottom of my menu items rather than popup in between them like I would
> prefer.
>
> If you need more info or copies of the code just drop me an email, I
> appreciate the time you have taken in your reply.
>
> Much thanks Andy.
>
>
>
>
>
>
>
> -----Original Message-----
> From: slakshmi [mailto:slakshmi@i...]
> Sent: Monday, 30 December 2002 10:57 PM
> To: ASP Databases
> Subject: [asp_databases] Re: Looping recordset data while inside a
> looped recordset?
>
> Hey what is "sAction" in submenu querry?
> What is the realtion b/w menu and submenu?
> By any chance is the querry for submenu is something like this:
>
> sqlstmt = "SELECT * from submenus WHERE m_related="&mAction
> sqlstmt = sqlstmt & " ORDER by m_name"
>
> wher mAction is menu name?
> Can u make it clear pls.
> Cheers,
> Sree
> ----- Original Message -----
> From: "Andrew Banks" <andy@n...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Monday, December 30, 2002 3:33 AM
> 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'> </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'> "
> > Response.write "<a title='"& m_name &"' href='?
> > action="&saction&"&what="&m_id&"'>" & m_name & "</a>"
> > Response.write " </td>"
> > if CDBL(m_id)=CDBL(sWhat) Then Response.write "<bgcolor='#FF9933'>"
> > Response.write "<td width='10'> </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
> > %>
>
>
>
>
>
>
|
|
|
View Cart
