kjohnson530
IS-IT--Management
does anyone have any recommendations for good tutorials regarding building tree menus using SQL data (getting data using VBscript).
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
<%
Set conn = Server.CreateObject("ADODB.Connection")
Conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=databaseServer;UID=UserName;PWD=Password;DATABASE=Northwind "
%>
<%
Function GetProducts(ProdID, Counter)
sql = "SELECT CategoryID, ProductName, ProductID FROM Products " & _
"where CategoryID = " & ProdID & " " & _
"order by ProductName "
Dim rs2
set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.open sql, Conn
Dim aProducts
If not rs2.EOF then
aProducts = rs2.GetRows()
'Close Recordset to use the new array with the 2 columns data
rs2.Close()
set rs2 = Nothing
'Declare Constants for the above SQL columns for better readability
'Use these Constants instead of referring to the array numeric indexes
Const c_CatID = 0
Const c_ProductName = 1
Const c_ProductID = 2
'Ubound(MyArray,1) 'Returns the Number of Columns
'Ubound(MyArray,2) 'Returns the Number of Rows
Dim iRowLoop
Response.Write("<ul>")
For iRowLoop = 0 to UBound(aProducts, 2)
Response.Write("<li>")
%>
<input type="radio" name="prodid" id="prodid<%=aProducts(c_ProductID,iRowLoop)%>" value="<%=aProducts(c_ProductID,iRowLoop)%>">
<%=aProducts(c_ProductName,iRowLoop)%>
<%
Counter = Counter + 1
'Call GetProducts(ProdID, Counter) 'to call recursively incase of n level of nestings
Counter = Counter - 1
%>
</li>
<%
Next 'iRowLoop
Response.Write("</ul>")
End If 'rs2.EOF
'Response.End
End Function
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/html4/loose.dtd">[/URL]
<html>
<head>
<style type="text/css">
* {
border: 0px none;
padding: 0;
margin: 0
}
#menu {
padding:0;
margin:0;
}
#menu li {
list-style-type:none;
}
#menu ul {
padding: 0;
margin: 6px;
list-style-type: none;
}
a.a_style:link {color:#0000ff; text-decoration:none;}
a.a_style:visited {color:#0000ff; text-decoration:none;}
a.a_style:hover {color:#ff0000; text-decoration:underline;}
a.a_style:hover {color:#ff0000; text-decoration:underline;}
</style>
<script type="text/javascript">
//Function to show and hide the tree
function s_Hide(el){
//alert("Showing Element: "+el);
objID = 'UI_'+el;
//alert(objID);
obj = document.getElementById(objID).style;
(obj.display == 'none')? obj.display = 'block' : obj.display = 'none';
}
</script>
<title>On Demand Building of tree with User OnClick</title>
</head>
<body>
<form name="Customers" id="Customers" action="">
<%
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CategoryID, CategoryName FROM Categories "
rs.open sSQL, Conn
Dim aCategory
If not rs.EOF Then
'Dump the recordset into the above array
aCategory = rs.getRows()
rs.Close
Set rs = Nothing
Dim iRows
For iRows = 0 to UBound(aCategory, 2)
CatID = aCategory(0, iRows)
%>
<ul id="menu">
<li>
<input type="radio" checked="checked" name="prodid" id="Catid<%=aCategory(0, iRows)%>" value="<%=aCategory(0, iRows)%>">
<a href="#" class="a_style" onclick="s_Hide('<%=aCategory(0, iRows)%>'); return false;"><%=aCategory(1, iRows)%></a>
<ul id="UI_<%=aCategory(0, iRows)%>">
<%
Call GetProducts(CatID, Counter)
%>
</ul>
</li>
</ul>
<%
Next 'iRows
End If
%>
</form>
</body>
</html>
<%
Conn.Close
Set Conn = Nothing
%>