Populating treeview with sqlite table using recursive function

[tinfoil]

Hello

I'm trying to load a treeview with data from a sqlite table. The table contains heirarchial data using the adjacency model.
For example, if the table contained an organisational structure it would be as follows.

tblEmployees
Col1: EmployeeName
Col2: Boss

EmployeeName___Boss
Matt_____________<Null>
Dave_____________Rachael
Sue______________Matt
Carl______________Tim
Tim______________Matt
Rachael___________Matt
Peter_____________Tim
Paul______________Peter
Ian_______________Peter

In the treeview, this would be displayed as follows

+Matt
___+Sue
___+Tim
______+Carl
______+Peter
_________+Ian
_________+Paul
___+Rachael
______+Dave

I have done this in vb6 before using a recursive procedure call, i.e. You first find the parent record (the one with a null boss) and add it to the treeview, you then filter all records that have that boss and add them to the treeview. After adding each node, you recall the prodedure passing the current employee as the filter (boss) for the next level in the treeview.

My problem is that I cannot see how I can pass the current node within the recursive sub so the employees go under the correct branch of the tree.


Many thanks
tinfoil

 

[specci48]

Hi tinfoil,

here is a small sample populating a treeview in a recursive way (with numbers form 0 to 8).
Tree1 is a treeview object and node1/node2 are node objects.

Sub Globals
   'Declare the global variables here.
   a = 0
End Sub

Sub App_Start
   tree1.New1("Form1",20,20,200,200)
   node1.New1
   node2.New1
   node1.Value = tree1.AddNewNode(a)
   FillRecursive
   Tree1.ExpandAll
   Form1.Show
End Sub

Sub FillRecursive
   If a < 8 Then
      a = a + 1
      node2.Value = node1.AddNewNode(a)
      node1.Value = node2.Value
      FillRecursive1
   End If
End Sub

The root node is added before the recursion because you have to work with a treeview, not a node.


specci48

 

[tinfoil]

Thank you Specci48

Unfortunately, your solution does not give me quite what I'm after. The tree will be structured as:
+Matt
__+Sue
____+Tim
______+Carl
________+Peter
__________+Ian
____________+Paul
______________+Rachael
________________+Dave

I think this is because the node1 value is global and is not maintained within each recursion of the loop.
For example, in the example above, when Carl has been added to the tv, that sub will end and the sub that created Carl (Tim) will continue and so node1 will need to be set to Tim and not Carl. My problem is that I require infinate levels and so I do not wish to have a node defined for each level.

Thanks
tinfoil

 

[agraham]

Try resetting the node to its parent after the return from FillRecursive.

Sub FillRecursive
  ....
  ' add node
  FillRecursive
  node = node.Parent
  ...
End Sub

 

[tinfoil]

Sample app showing problem

Hi All

I have added the source code that shows my problem The problem is commented in sub sbRecurseArray


Thanks
Tinfoil

 

[tinfoil]

Thanks everyone for all your help. I have added agraham's code snippet to the bottom of the recursive sub and bingo.

The working code now is:

Sub sbRecurseArray(lTriggerID)

Dim i

For i = 0 To Table1.RowCount -1

If Table1.Cell("NodeParentID",i) = lTriggerID Then
'Msgbox (Table1.Cell("NodeName",i))
node2.Value = node1.AddNewNode(Table1.Cell("NodeName",i))

node1.Value = node2.Value

sbRecurseArray(Table1.Cell("NodeID",i))
End If
Next i
node1.value = node1.parent

End Sub

Thanks
Tinfoil