Creating a Dynamic Tree menu from SQL 1

kjohnson530 · Aug 2, 2007

does anyone have any recommendations for good tutorials regarding building tree menus using SQL data (getting data using VBscript).

Sheco · Aug 2, 2007

By "tree" do you mean a dropdown mean where only the categories are shown at first but then you drill down to sub levels?

kjohnson530 · Aug 2, 2007

yes exactly. I am able to use java for these, but how I get to sql data to populate the menus is beyond me.

Sheco · Aug 2, 2007

Most of the hard work will be done in client-side code... assuming you don't want to do a reload after each menu click.

If I was starting this project from scratch, I would start by searching the web for a free client-sdie JavaScript sample solution. Then I would study the code to figure out how exactly the JavaScript was getting its data... it would probably be some sort of multi-diminsion array but who knows.

Only once I figured out how the client-side code needs to be written would I work on the exact SQL... because I figure the SQL might be different depending on exactly what sort of client-side code that I need to write using the ASP.

I hope that makes sense.

kjohnson530 · Aug 2, 2007

Very good suggestion. thank you.

tayorolls · Aug 2, 2007

Here is ASP code that shows hierarchy of products under the respective category in the Northwind database. This sample code shows how to build a tree based on the table - Categories and Products.

It builds the tree serverside, and then uses Javascript show and hide to toggle the show and hide of parent and child elements. So there is only 1 trip to the database server to get the information to build the tree. This approach works fine with fewer records. I have tried this approach with a table which had 65K records and it took me 1 minute and 56 seconds to build the tree. That is really long and a death sentence for a web developer! So I had to normalize the table data to render my tree for the ASP.

Just change the Connection String to point to your Northwind database. Mine is a SQL Server 2000 database.

Thanks.

Code:

<%
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
%>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Creating a Dynamic Tree menu from SQL 1

kjohnson530

IS-IT--Management

Sheco

Programmer

kjohnson530

IS-IT--Management

Sheco

Programmer

kjohnson530

IS-IT--Management

tayorolls

MIS

Similar threads

Part and Inventory Search

Sponsor