ASP.NET Forum

**taigar** · 10-06-2006, 04:54 PM

I have the following code and database but don’t know how to create a Loop. At least I believe it requires a Loop but have no clues how to do it. The part that I want to create a Loop is in the Switch Menu section. I managed to get it to work partially. For example, the Switch Menu opens and closes showing only the first item when clicked on. If I have more than one item it doesn’t show it. That’s why I think it might require a Loop statement somewhere in the Switch Menu section to enable the display. The bolded line below is the part where it controls the menu item(s) and is where I need to apply the Loop statement. Does anyone know how to write the code?

I’ve attached a database file that goes with the code.

My Code:

<%
Dim Dbq, Dsn, Dc
Dim rsCat, rsSubCat
Dim sCatName, sSubCatName, sLink

Set Dc = Server.CreateObject("ADODB.Connection")
Dbq = Server.MapPath(".\") & "\left_nav_menu_dse.mdb;"
Dsn = "driver={Microsoft Access Driver (*.mdb)};pwd=;Dbq=" & Dbq
Dc.Open Dsn
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>
<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
<meta name="generator" content="Adobe GoLive 6">
<title>nav_page</title>

<link rel="stylesheet" type="text/css" href="/ncdss_web_dev/css/left_nav_switch.css" />
<style type="text/css" media="screen"></style>
<script type="text/javascript">

/***********************************************
* Switch Menu script- by Martial B of http://getElementById.com/
* Modified by Dynamic Drive for format & NS4/IE4 compatibility
* Visit http://www.dynamicdrive.com/ for full source code
***********************************************/

var persistmenu="yes" //"yes" or "no". Make sure each SPAN content contains an incrementing ID starting at 1 (id="sub1", id="sub2", etc)
var persisttype="sitewide" //enter "sitewide" for menu to persist across site, "local" for this page only

if (document.getElementById){ //DynamicDrive.com change
document.write('<style type="text/css">\n')
document.write('.submenu{display: none;}\n')
document.write('</style>\n')
}

function SwitchMenu1(obj){
if(document.getElementById){
var el = document.getElementById(obj);
var ar = document.getElementById("leftnav").getElementsByTa gName("span"); //DynamicDrive.com change
if(el.style.display != "block"){ //DynamicDrive.com change
for (var i=0; i<ar.length; i++){
if (ar[i].className=="leftnavsubmenu") //DynamicDrive.com change
ar[i].style.display = "none";
}
el.style.display = "block";
}else{
el.style.display = "none";
}
}
}

function get_cookie(Name) {
var search = Name + "="
var returnvalue = "";
if (document.cookie.length > 0) {
offset = document.cookie.indexOf(search)
if (offset != -1) {
offset += search.length
end = document.cookie.indexOf(";", offset);
if (end == -1) end = document.cookie.length;
returnvalue=unescape(document.cookie.substring(off set, end))
}
}
return returnvalue;
}

function onloadfunction(){
if (persistmenu=="yes"){
var cookiename=(persisttype=="sitewide")? "switchmenu" : window.location.pathname
var cookievalue=get_cookie(cookiename)
if (cookievalue!="")
document.getElementById(cookievalue).style.display ="block"
}
}

function savemenustate(){
var inc=1, blockid=""
while (document.getElementById("sub"+inc)){
if (document.getElementById("sub"+inc).style.display= ="block"){
blockid="sub"+inc
break
}
inc++
}
var cookiename=(persisttype=="sitewide")? "switchmenu" : window.location.pathname
var cookievalue=(persisttype=="sitewide")? blockid+";path=/" : blockid
document.cookie=cookiename+"="+cookievalue
}

if (window.addEventListener)
window.addEventListener("load", onloadfunction, false)
else if (window.attachEvent)
window.attachEvent("onload", onloadfunction)
else if (document.getElementById)
window.onload=onloadfunction

if (persistmenu=="yes" && document.getElementById)
window.onunload=savemenustate

</script>

</head>

<body bgcolor="#ffffff">



<div id="leftnav">

<%
Set rsCat = Dc.Execute("SELECT * FROM CATEGORY")
While Not rsCat.EOF
sCatName = rsCat("NAME")
sCatSubNum = rsCat("SUB_NUM")
%>
<div class="menutitle1" onclick="SwitchMenu1('sub<%=sCatSubNum%>')"><img src="/ncdss_web_dev/images/img_arrow_red.gif" alt="" height="12" width="12" border="0"><%=sCatName%></div>

<%
Set rsSubCat = Dc.Execute("SELECT * FROM SUB_CATEGORY WHERE CATID= " & rsCat(0) & " ORDER BY SUB_NUM_SEQ ASC")
While Not rsSubCat.EOF
sSubCatName = rsSubCat("NAME")
sLink = rsSubCat("LINK")
%>
<span class="leftnavsubmenu" id="sub<%=sCatSubNum%>">

<a href="<%=sLink%>" target="_parent"><%=sSubCatName%></a>
</span>

<%rsSubCat.MoveNext%>
<%Wend%>

<%rsCat.MoveNext%>
<%Wend%>

</div>




<div id="pic"><img src="/ncdss_web_dev/images/img_dse_select_topic_people.jpeg" alt="" height="50" width="195" border="0"></div>


</body>

</html>

**ADAM Web Design** · 10-06-2006, 05:07 PM

You've already got the loop in place. The part beginning with the word "While" and ending with "Wend" is a loop (in fact, you've got a loop inside of a loop.)

The problem that you have is that you're Executing queries instead of Opening them. You only want to Execute queries where you have to modify data in some way, shape or form (e.g. inserting a new row into a table). Opening records for reading tends to work much better.

Here's a sample of how to open a record and loop through it:

http://www.stardeveloper.com/article...0071001&page=1

This example involves recordset paging, which you don't need for your sample (recordset paging is the idea of taking a whole bunch of stuff and breaking it down into groups of 5, 10, 15, whatever.) But it'll give you a good general idea if you deconstruct it.

You'll see how the query is opened in that sample:

rs.Open

That's the key to it.

Note: there are other aspects of the code you'll want to play with to get it working properly and efficiently, but this will get it "to work" for now.

**taigar** · 10-06-2006, 06:26 PM

Thanks a lot ADAM for your explanation!

Not sure if I follow you correctly but I tried and replaced the code Dc.Execute to Dc.Open. Is that what you meant? Where do I add rs.Open? Is it in the Switch Menu section where I want to display the menu items?

**taigar** · 10-11-2006, 03:47 PM

Hi ADAM,

I followed your suggestion and I'm still not able to get it wroking. Can you have a look at the new code below and point me in the right direction.

<%
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Dim sConnString
sConnString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" &Server.MapPath(".\") & "\left_nav_menu_dse.mdb;"
Conn.Open sConnString
Dim Recordset, rsCat
Set Recordset=Server.CreateObject("ADODB.Recordset")
Set rsCat = Conn.Execute("SELECT * FROM CATEGORY")
Dim SQL
SQL="SELECT * FROM SUB_CATEGORY WHERE CATID= " & rsCat(0) & " ORDER BY SUB_NUM_SEQ ASC"
Recordset.Open SQL, Conn
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
<meta name="generator" content="Adobe GoLive 6">
<title>nav_page</title>

<link rel="stylesheet" type="text/css" href="/ncdss_web_dev/css/left_nav_switch.css" />
<style type="text/css" media="screen"></style>
<script type="text/javascript">

/***********************************************
* Switch Menu script- by Martial B of http://getElementById.com/
* Modified by Dynamic Drive for format & NS4/IE4 compatibility
* Visit http://www.dynamicdrive.com/ for full source code
***********************************************/

var persistmenu="yes" //"yes" or "no". Make sure each SPAN content contains an incrementing ID starting at 1 (id="sub1", id="sub2", etc)
var persisttype="sitewide" //enter "sitewide" for menu to persist across site, "local" for this page only

if (document.getElementById){ //DynamicDrive.com change
document.write('<style type="text/css">\n')
document.write('.submenu{display: none;}\n')
document.write('</style>\n')
}

function SwitchMenu1(obj){
if(document.getElementById){
var el = document.getElementById(obj);
var ar = document.getElementById("leftnav").getElementsByTa gName("span"); //DynamicDrive.com change
if(el.style.display != "block"){ //DynamicDrive.com change
for (var i=0; i<ar.length; i++){
if (ar[i].className=="leftnavsubmenu") //DynamicDrive.com change
ar[i].style.display = "none";
}
el.style.display = "block";
}else{
el.style.display = "none";
}
}
}

function get_cookie(Name) {
var search = Name + "="
var returnvalue = "";
if (document.cookie.length > 0) {
offset = document.cookie.indexOf(search)
if (offset != -1) {
offset += search.length
end = document.cookie.indexOf(";", offset);
if (end == -1) end = document.cookie.length;
returnvalue=unescape(document.cookie.substring(off set, end))
}
}
return returnvalue;
}

function onloadfunction(){
if (persistmenu=="yes"){
var cookiename=(persisttype=="sitewide")? "switchmenu" : window.location.pathname
var cookievalue=get_cookie(cookiename)
if (cookievalue!="")
document.getElementById(cookievalue).style.display ="block"
}
}

function savemenustate(){
var inc=1, blockid=""
while (document.getElementById("sub"+inc)){
if (document.getElementById("sub"+inc).style.display= ="block"){
blockid="sub"+inc
break
}
inc++
}
var cookiename=(persisttype=="sitewide")? "switchmenu" : window.location.pathname
var cookievalue=(persisttype=="sitewide")? blockid+";path=/" : blockid
document.cookie=cookiename+"="+cookievalue
}

if (window.addEventListener)
window.addEventListener("load", onloadfunction, false)
else if (window.attachEvent)
window.attachEvent("onload", onloadfunction)
else if (document.getElementById)
window.onload=onloadfunction

if (persistmenu=="yes" && document.getElementById)
window.onunload=savemenustate

</script>

</head>
<body bgcolor="#ffffff">



<div id="leftnav">
<%
While Not rsCat.EOF
sCatName = rsCat("NAME")
sCatSubNum = rsCat("SUB_NUM")
%>
<div class="menutitle1" onclick="SwitchMenu1('sub<%=sCatSubNum%>')"><img src="/ncdss_web_dev/images/img_arrow_red.gif" alt="" height="12" width="12" border="0"><%=sCatName%></div>
<%
Do While NOT Recordset.Eof
sSubCatName = Recordset("NAME")
sLink = Recordset("LINK")
'sSubCatSubNum = Recordset("SUB_NUM")
Recordset.MoveNext
Loop
%>
<span class="leftnavsubmenu" id="sub<%=sCatSubNum%>">
<a href="<%=sLink%>" target="_parent"><%=sSubCatName%></a>
</span>
<%rsCat.MoveNext%>
<%Wend%>
<%
Recordset.Close
Set Recordset=Nothing
Conn.Close
Set Conn=Nothing
%>

</div>



<div id="pic"><img src="/ncdss_web_dev/images/img_dse_select_topic_people.jpeg" alt="" height="50" width="195" border="0"></div>


</body>
</html>

Thanks!

**ADAM Web Design** · 10-11-2006, 04:02 PM

You already look like you're there, except you need to change this line too:

Set rsCat = Conn.Execute("SELECT * FROM CATEGORY")

to match what you did with "recordset".

**taigar** · 10-11-2006, 04:24 PM

I made the change for line Set rsCat = Conn.Execute("SELECT * FROM CATEGORY") to Set rsCat=Server.CreateObject("ADODB.rsCat")

As well, made changes to the code in the Swtich Menu section:



<div id="leftnav">
<%
Do While Not rsCat.EOF
sCatName = rsCat("NAME")
sCatSubNum = rsCat("SUB_NUM")
rsCat.MoveNext
Loop
%>
<div class="menutitle1" onclick="SwitchMenu1('sub<%=sCatSubNum%>')"><img src="/ncdss_web_dev/images/img_arrow_red.gif" alt="" height="12" width="12" border="0"><%=sCatName%></div>
<%
Do While NOT Recordset.Eof
sSubCatName = Recordset("NAME")
sLink = Recordset("LINK")
'sSubCatSubNum = Recordset("SUB_NUM")
Recordset.MoveNext
Loop
%>
<span class="leftnavsubmenu" id="sub<%=sCatSubNum%>">
<a href="<%=sLink%>" target="_parent"><%=sSubCatName%></a>
</span>
<%
rsCat.Close
Set rsCat=Nothing
Conn.Close
Set Conn=Nothing
%>
<%
Recordset.Close
Set Recordset=Nothing
Conn.Close
Set Conn=Nothing
%>
</div>



It's giving me this error:

Server objecterror 'ASP 0177 : 800401f3'
Server.CreateObject Failed
/ncdss_web_dev/nc_decision_support_envrnmt/left_nav_menu_dse3.asp, line 14 Invalid ProgID. For additional information specific to this message please visit the Microsoft Online Support site located at: http://www.microsoft.com/contentredirect.asp.

**taigar** · 10-12-2006, 11:58 AM

Hi ADAM,

Is it possible to create more than one Recordset? The other one I created is rsCat but for some reason it's giving errors. Is there any other changes I need to make to get it working?

Thanks!

**chrishirst** · 10-12-2006, 03:08 PM

there is no such thing as as ("ADODB.rsCat")

it is ADODB.Recordset

and yes you can create several recordsets

**taigar** · 10-12-2006, 04:13 PM

I made the change to ADODB.Recordset and still no luck. Here's revised code:

<%
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Dim sConnString
sConnString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" &Server.MapPath(".\") & "\left_nav_menu_dse.mdb;"
Conn.Open sConnString
Dim rs1, rs2
Set rs1=Server.CreateObject("ADODB.Recordset")
Set rs2=Server.CreateObject("ADODB.Recordset")
Dim SQL, SQL2
SQL = "SELECT * FROM SUB_CATEGORY WHERE CATID= " & rs2(0) & " ORDER BY SUB_NUM_SEQ ASC"
SQL2 = "SELECT * FROM CATEGORY"
rs1.Open SQL, Conn
rs2.Open SQL2, Conn
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
<meta name="generator" content="Adobe GoLive 6">
<title>nav_page</title>

<link rel="stylesheet" type="text/css" href="/ncdss_web_dev/css/left_nav_switch.css" />
<style type="text/css" media="screen"></style>
<script type="text/javascript">

var persistmenu="yes" //"yes" or "no". Make sure each SPAN content contains an incrementing ID starting at 1 (id="sub1", id="sub2", etc)
var persisttype="sitewide" //enter "sitewide" for menu to persist across site, "local" for this page only

if (document.getElementById){ //DynamicDrive.com change
document.write('<style type="text/css">\n')
document.write('.submenu{display: none;}\n')
document.write('</style>\n')
}

function SwitchMenu1(obj){
if(document.getElementById){
var el = document.getElementById(obj);
var ar = document.getElementById("leftnav").getElementsByTa gName("span"); //DynamicDrive.com change
if(el.style.display != "block"){ //DynamicDrive.com change
for (var i=0; i<ar.length; i++){
if (ar[i].className=="leftnavsubmenu") //DynamicDrive.com change
ar[i].style.display = "none";
}
el.style.display = "block";
}else{
el.style.display = "none";
}
}
}

function get_cookie(Name) {
var search = Name + "="
var returnvalue = "";
if (document.cookie.length > 0) {
offset = document.cookie.indexOf(search)
if (offset != -1) {
offset += search.length
end = document.cookie.indexOf(";", offset);
if (end == -1) end = document.cookie.length;
returnvalue=unescape(document.cookie.substring(off set, end))
}
}
return returnvalue;
}

function onloadfunction(){
if (persistmenu=="yes"){
var cookiename=(persisttype=="sitewide")? "switchmenu" : window.location.pathname
var cookievalue=get_cookie(cookiename)
if (cookievalue!="")
document.getElementById(cookievalue).style.display ="block"
}
}

function savemenustate(){
var inc=1, blockid=""
while (document.getElementById("sub"+inc)){
if (document.getElementById("sub"+inc).style.display= ="block"){
blockid="sub"+inc
break
}
inc++
}
var cookiename=(persisttype=="sitewide")? "switchmenu" : window.location.pathname
var cookievalue=(persisttype=="sitewide")? blockid+";path=/" : blockid
document.cookie=cookiename+"="+cookievalue
}

if (window.addEventListener)
window.addEventListener("load", onloadfunction, false)
else if (window.attachEvent)
window.attachEvent("onload", onloadfunction)
else if (document.getElementById)
window.onload=onloadfunction

if (persistmenu=="yes" && document.getElementById)
window.onunload=savemenustate

</script>

</head>
<body bgcolor="#ffffff">



<div id="leftnav">
<%
Do While Not rs2.EOF
sCatName = rs2("NAME")
sCatSubNum = rs2("SUB_NUM")
rs2.MoveNext
Loop
%>
<div class="menutitle1" onclick="SwitchMenu1('sub<%=sCatSubNum%>')"><img src="/ncdss_web_dev/images/img_arrow_red.gif" alt="" height="12" width="12" border="0"><%=sCatName%></div>
<%
Do While NOT rs1.Eof
sSubCatName = rs1("NAME")
sLink = rs1("LINK")
'sSubCatSubNum = rs1("SUB_NUM")
rs1.MoveNext
Loop
%>
<span class="leftnavsubmenu" id="sub<%=sCatSubNum%>">
<a href="<%=sLink%>" target="_parent"><%=sSubCatName%></a>
</span>
</div>



<div id="pic"><img src="/ncdss_web_dev/images/img_dse_select_topic_people.jpeg" alt="" height="50" width="195" border="0"></div>


</body>
</html>

I really don't know what I'm doing wrong???