Recursive Hierarchies(Unlimited Categories and Subcategories

[rdx73]

I am having difficulty with recursive hierarchies. I would like to have unlimited categories and subcategories displayed in a breadcrumb inside a drop down box for the entire table. My table is tblCategories and it has a ID and Parent ID field in the database. Any help would be greatly appreciated. I am really struggling and don't quite understand the mathematics behind it. Here is the code I came up with, but its not working:

<form name="form1">
<select name="menu1" onChange="MM_jumpMenu('parent',this,0)">
<option value="#">Select One</option>
<option value="index.asp?Action=categoryset&ID=0">Top Level</option>
<%OpenDB()

Set objRSy = GetData("SELECT * FROM tblCategories")
Do While Not objRSy.EOF or intParID = 0
intParIDCon = objRSy("ParentID")
intParID = objRSy("ParentID")
intIDN = objRSy("ID")
strBase = objRSy("Name")
strBreadCrumb = strBase


strBreadCrumb = strBase
Set objRSu = GetData("SELECT * FROM tblCategories WHERE ID =" & intParID)

Do Until objRSu.EOF

strBreadCrumb = objRSu("Name") & " >> " & strBreadCrumb

%>
<option value="index.asp?Action=categoryset&ID=<%=intIDN%>"><%=strBreadCrumb%></option>
<%


objRSu.movenext
loop
objRSu.close

intParID = factorial(intParID)

objRSy.movenext
loop
objRSy.close

%>
</select>
</form>
<%function factorial(intParID)
if intParID = 0 then
factorial = 1
exit function
end if

factorial = intParID * factorial(intParID-1)
end function%>

[degsy]

It depends on how your database is setup.

I use a recursive function that takes the id of the category then checks to see if it has any sub categories associated with it.

For this type of system you would require a new table and use joins to extract the data.

Function listSubCat(CatID, Counter)
	SubCat__MMColParam = CatID
	
	Set SubCat = Server.CreateObject("ADODB.Recordset")
	SubCat.ActiveConnection = MM_MenuSystem_STRING
	SubCat.Source = "SELECT Category.CatID, Category.CatName  FROM Category INNER JOIN SubCat ON Category.CatID = SubCat.CatID  WHERE (((SubCat.SubCatID)= " + Replace(SubCat__MMColParam, "'", "''") + "))  ORDER BY Category.CatName"
	SubCat.CursorType = 0
	SubCat.CursorLocation = 2
	SubCat.LockType = 1
	SubCat.Open()
		
	If Not SubCat.EOF Or Not SubCat.BOF Then
		While NOT SubCat.EOF
			Response.Write Space(2+Counter) & "<ul>" & vbCRLF
			Response.Write Space(4+Counter) & "<li>"
			Response.Write "<em>"
			Response.Write SubCat("CatName") & " (CategoryID " & SubCat("CatID") & ")"
			Response.Write "</em>" & vbCRLF
			
			Counter = Counter + 4
			Call listSubCat(SubCat("CatID"), Counter)
			Call listLink(SubCat("CatID"), Counter)
			Counter = Counter - 4
			
			Response.Write Space(4+Counter) & "</li>" & vbCRLF
			Response.Write Space(2+Counter) & "</ul>" & vbCRLF
		
		SubCat.MoveNext()
		Wend
	End If ' end Not SubCat.EOF Or NOT SubCat.BOF 
	
	SubCat.Close()
	Set SubCat = Nothing
End Function

http://computer-helpforum.com/asp/as...ystem_List.asp

[rdx73]

Thank you my friend for taking the time to help. I've googled myself to death looking for an answer to my problem. Here is how my database is setup. It has other fields, but the important ones are as follows:

ID ParentID Name
1 0 Pets
2 1 Cats
3 2 Siamese Cats

I am trying to get this:

Pets >> Cats >> Siamese Cats


I am new to ASP and SQL. I am not very familiar with JOINS.

My output looks good until I get to the third node, then it craps out.

The output looks like this when I try to add Siamese Cats to Cats:

Pets
Pets >> Cats
Cats >> Siamese Cats

It should look like this:

Pets
Pets >> Cats
Pets >> Cats >> Siamese Cats

If you have the time, could you please comment you code a little more to show me exactly what you are doing and/or show me how I would incorporate this code into my sample to get the drop down select box to work? Thank you. Your time is greatly appreciated.