Three level menu recursion

[cr84net2]

ID---parentID
1------NULL <<<<Parent 1
2--------1<<<<<<Child 1 of Parent 1
3--------1<<<<<<Child 2 of Parent 1
4-----NULL<<<<Parent 2
5--------4 <<<<<<Child 1 of Parent 2
6------NULL<<<<Parent 3
7--------6<<<<<<Child 1 of Parent 3
10-----NULL<<<<Parent 4
11-----NULL<<<<Parent 5
12-------2<<<<<<<Grandchild of 1, Child 1 of Child 1 of Parent 1

This is the table layout for a database fed jscript menu that works fine for the first two levels, but will not display the third or grandchild level.

I understand the first Select statement below dictates the inner recursion of the second select statement but cannot figure a way around it. Here are the SELECT statements:

(Est. record set etc)
sSql="SELECT ID,Title,parentID FROM quick WHERE parentID IS NUll"

While Not rs.EOF

This selects the parents, then before I move to the next record the second SELECT statement and recordset is called and is moved to the next record.

(Est. record set etc)
Sql="SELECT parentID, Title, ID FROM quick WHERE parentID = " & ID

While Not Rrs.EOF

This SELECT statement chooses the children for each parent, then I move to the next record:

Rrs.MoveNext
Wend

rs.MoveNext
Wend

I can see why I cannot get the grandchild (ID=12) with this recursion but I am lost as to how to add the third level. Any help would be appreciated.

 

[VBrit]

You need to use recursion in order to achieve this simply.

Recursion: A function which calls itself until it completes a repetitive task, or calculation task.

 

[damber]

You can either continue the way you have, which is manual sub-level loops using while/wend for each level you want to add, or you can use a recursive function.

Some people will warn you to stay away from Recursive functions, due to risk of an infinite loop and even some performance penalties. Though if you code it with enough care, it can be very useful.

You would code it something like this:

  function processLevel(parentID)

    dim sSQL
    if isEmtpy(parentID) then
        Err.Raise  vb.ObjectError + 99999, "ASPFunction:processLevel","No parent ID was specified")
        Exit Function
    end if
    sSQL = "SELECT id, Title,isParent FROM quick WHERE parentID = " & parentID
    'Open recordset etc  (I've not included it because you seem not to have an issue with this and I'm lazy..;-)

    while not oRS.EOF
      'Do some stuff with the data

      'If you want you can use a flag field of isParent in the database
      'this will allow you to only call the function when there are children nodes.
      'Not necessary though as the code will select an empty recordset - it's just not as efficient
      if oRS.Fields("isParent") then
          processLevel oRS.Fields("id") 
      end if
      oRS.MoveNext
    wend

  end function

I've written this on the fly so be careful of typos etc - though it should give you a general idea of how to approach it.

I added a field to your db structure called 'isParent', which would be a boolean flag (true/false). You dont have to use this, but it reduces the function calls by the number of terminal nodes (those without children).

You might also consider using the GetRows method - it will help increase performance over using a normal connected recordset.

Hope this helps.

A smile is worth a thousand kind words. So smile, it's easy!

 

[cr84net2]

I will give this a try and see if I can progress to that elusive third level. Thanks, I appreciate the help.