Hello all,
I am developing an application in VB 2008 that works with a SQL2005 DB to store and manipulate employee data. In one section of the app I want to be able to show a treeview of the hierarchy as a whole showing a nested hierarchy of who reports to who such that when it is first loaded, all you will see is the top guy in the company with a plus next to his name. Opening that will reveal his first level reports and opening those will reveal the next level and so on. This one piece of the puzzle has been the elemental drive of this application and it has yet to be accomplished.
The fields in the database that I need to work on are as such:
1. AssociateNumber (Basically the employee number of the person. Formatted like A-232)
2. Sponsor (This is the field showing the AssociateNumber of the person this associate reports to. It is also formatted like A-232)
Thus if A-20 reported to A-2 then it would look like this in A-20's row
AssociateNumber =A-20, Sponsor=A-2
I understand that I need to query the database and build a recordset for the data that I want to display and then populate the treeview from that recordset but I have been horribly unsuccessful thusfar.
Mostly I seem to be able to get the recordset created (using the Northwind as a test DB pulling from EmployeeID and ReportsTo fields) but when I create the treeview it seems to put all 9 of the employees on the first level as well as nesting them causing duplication of names.
Below is the code I used to accomplish this disaster.
Any help would be greatly appreciated!
Thanks!
Peter
_______________ _______________ _______________ _
Public Function BuildHierarchy( ) As DataSet
'Experimental code for the nested hierarchial view
Dim result As New DataSet
Dim connString As String = "server = .\sqlexpress;in tegrated security = true;database = Northwind"
Dim myConnection = New SqlConnection(c onnString)
If (True) Then
Dim query As String = "Select EmployeeID,Firs tName,LastName, Title,ReportsTo from Employees"
Dim myCommand As New SqlCommand(quer y, myConnection)
Dim myAdapter As New SqlDataAdapter
myCommand.Comma ndType = CommandType.Tex t
myAdapter.Selec tCommand = myCommand
myAdapter.Fill( result)
myAdapter.Dispo se()
End If
result.DataSetN ame = "Employees"
result.Tables(0 ).TableName = "Employee" '
Dim relation As New DataRelation("P arentChild", result.Tables(" Employee").Colu mns("EmployeeID "), result.Tables(" Employee").Colu mns("ReportsTo" ), True)
relation.Nested = True
result.Relation s.Add(relation)
result.GetXml()
Dim nodeAssociate As TreeNode
Dim nodeSubAssociat e As TreeNode
For Each rowAssociate In result.Tables(" Employee").Rows
nodeAssociate = New TreeNode()
nodeAssociate.T ext = rowAssociate("E mployeeID") & ". " & rowAssociate("F irstName") & " " & rowAssociate("L astName")
tvwHierarchy.No des.Add(nodeAss ociate)
For Each rowTitle In rowAssociate.Ge tChildRows("Par entChild")
nodeSubAssociat e = New TreeNode()
nodeSubAssociat e.Text = rowTitle("Emplo yeeID") & ". " & rowTitle("First Name") & " " & rowTitle("LastN ame")
nodeAssociate.N odes.Add(nodeSu bAssociate)
Next
Next
End Function
I am developing an application in VB 2008 that works with a SQL2005 DB to store and manipulate employee data. In one section of the app I want to be able to show a treeview of the hierarchy as a whole showing a nested hierarchy of who reports to who such that when it is first loaded, all you will see is the top guy in the company with a plus next to his name. Opening that will reveal his first level reports and opening those will reveal the next level and so on. This one piece of the puzzle has been the elemental drive of this application and it has yet to be accomplished.
The fields in the database that I need to work on are as such:
1. AssociateNumber (Basically the employee number of the person. Formatted like A-232)
2. Sponsor (This is the field showing the AssociateNumber of the person this associate reports to. It is also formatted like A-232)
Thus if A-20 reported to A-2 then it would look like this in A-20's row
AssociateNumber =A-20, Sponsor=A-2
I understand that I need to query the database and build a recordset for the data that I want to display and then populate the treeview from that recordset but I have been horribly unsuccessful thusfar.
Mostly I seem to be able to get the recordset created (using the Northwind as a test DB pulling from EmployeeID and ReportsTo fields) but when I create the treeview it seems to put all 9 of the employees on the first level as well as nesting them causing duplication of names.
Below is the code I used to accomplish this disaster.
Any help would be greatly appreciated!
Thanks!
Peter
_______________ _______________ _______________ _
Public Function BuildHierarchy( ) As DataSet
'Experimental code for the nested hierarchial view
Dim result As New DataSet
Dim connString As String = "server = .\sqlexpress;in tegrated security = true;database = Northwind"
Dim myConnection = New SqlConnection(c onnString)
If (True) Then
Dim query As String = "Select EmployeeID,Firs tName,LastName, Title,ReportsTo from Employees"
Dim myCommand As New SqlCommand(quer y, myConnection)
Dim myAdapter As New SqlDataAdapter
myCommand.Comma ndType = CommandType.Tex t
myAdapter.Selec tCommand = myCommand
myAdapter.Fill( result)
myAdapter.Dispo se()
End If
result.DataSetN ame = "Employees"
result.Tables(0 ).TableName = "Employee" '
Dim relation As New DataRelation("P arentChild", result.Tables(" Employee").Colu mns("EmployeeID "), result.Tables(" Employee").Colu mns("ReportsTo" ), True)
relation.Nested = True
result.Relation s.Add(relation)
result.GetXml()
Dim nodeAssociate As TreeNode
Dim nodeSubAssociat e As TreeNode
For Each rowAssociate In result.Tables(" Employee").Rows
nodeAssociate = New TreeNode()
nodeAssociate.T ext = rowAssociate("E mployeeID") & ". " & rowAssociate("F irstName") & " " & rowAssociate("L astName")
tvwHierarchy.No des.Add(nodeAss ociate)
For Each rowTitle In rowAssociate.Ge tChildRows("Par entChild")
nodeSubAssociat e = New TreeNode()
nodeSubAssociat e.Text = rowTitle("Emplo yeeID") & ". " & rowTitle("First Name") & " " & rowTitle("LastN ame")
nodeAssociate.N odes.Add(nodeSu bAssociate)
Next
Next
End Function