...

Recursive Hierarchies(Unlimited Categories and Subcategories

rdx73
01-12-2007, 02:28 AM
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">
<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")

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

Do Until objRSu.EOF

%>
<%

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
01-12-2007, 03:43 PM
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

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

rdx73
01-12-2007, 04:12 PM
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.