Populate Treeview with storedproc

[pfechner]

Hi,

apologies at first as my vb.net experience is only a few days old...

I am trying to populate a treeview via data stored in an sql2005 db.

The stored proc returns the data as follows:
Asian_Elephants Fishing_Cat Pump
Asian_Elephants Otters Pump
GSO Leopard_Seal Pump
GSO Penguin Pump

Each column represents a sub level of the tree.
ie Pump is a sub level of Fishing_Cat.
Fishing_Cat and Otters are a sub levels of Asian_Elephants.

How do i loop through and build the tree?

Should i be using a Datatable of datareader?

I have tried with the datareader but i think it may be the wrong method.

My code is below. it bombs out after i try to map the newarea after the reader.NextResult() statement saying there is no data.

The code needs to be tweaked to add the nodes at the correct level but that is not my concern at the moment - i just need to get the result set loop happening.

Any help is truly appreciated.

Dim myconnection As New SqlConnection("Data Source=ICAUTOLTP99\SQL2005;Initial Catalog=TarongaZoo;Integrated Security=True")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader

Dim newprecinct As String
Dim oldprecinct As String
Dim newarea As String
Dim oldarea As String
Dim newobjecttype As String
Dim oldobjecttype As String

cmd.CommandText = "app_display_menu"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = myconnection

myconnection.Open()

reader = cmd.ExecuteReader
'reader mappings (0)=PRECINCT, (1)=AREA, (2)=TYPE


Do While reader.HasRows

Do While reader.Read
''map parent level
newprecinct = (reader.GetString(0))
oldprecinct = (reader.GetString(0))

'map child1 level
newarea = (reader.GetString(1))
oldarea = (reader.GetString(1))

'map child2 level
newobjecttype = (reader.GetString(2))
oldobjecttype = (reader.GetString(2))

'precinct level

TreeView1.Nodes.Add(newprecinct)

'child1 level
Do Until newprecinct <> oldprecinct
oldprecinct = newprecinct
'insert child1
TreeView1.Nodes.Add(newarea)

'child2 level
Do Until newarea <> oldarea
oldarea = newarea
'insert child2 level
TreeView1.Nodes.Add(newobjecttype)

reader.NextResult()
newarea = (reader.GetString(1))
Loop
Loop
Loop

Loop

myconnection.Close()

 

[cjard]

Is this tree always 3 deep?

 

[pfechner]

no, always between 1 and 4 deep.

i'm all new to .net so dont know if sqlreader is the best method for this, is datareader the better option? (if yes i would love it if you could give me a quick demo of how to step through records in a data set).

cheers

 

[pfechner]

haven't found/been able to understand msdn related articles

ok, i have changed a few things and am now using datatables. That is all working.

but now when i populate the treeview it is bombing out with the error

"Cannot add or insert the item 'PMP-OT-02' in more than one place. You must first remove it from its current location or clone it.
Parameter name: node"

it appears to happen when i add a second or third level.

here is my code (the data stuff is working nicely now)

Do Until mycount = dt.Rows.Count

                'map new to old
                oldprecinct = dt.Rows(mycount)("precinct_desc")
                'insert top level
                precinctnode.Text = dt.Rows(mycount)("precinct_desc")
                TreeView1.Nodes.Add(precinctnode)




                'commence second level
                Do Until oldprecinct <> dt.Rows(mycount)("precinct_desc")

                    'map new to old
                    oldarea = dt.Rows(mycount)("area_desc")
                    'insert second level
                    areanode.Text = dt.Rows(mycount)("area_desc")
                    precinctnode.Nodes.Add(areanode)




                    'commence third level
                    Do Until dt.Rows(mycount)("area_desc") <> oldarea

                        'map new to old
                        oldobjecttype = dt.Rows(mycount)("type_desc")
                        'insert third level
                        typenode.Text = dt.Rows(mycount)("type_desc")
                        areanode.Nodes.Add(typenode)




                        'commence fourth level
                        Do Until dt.Rows(mycount)("type_desc") <> oldobjecttype

                            'insert fourth level
                            objectnode.Text = dt.Rows(mycount)("object_desc")
                            typenode.Nodes.Add(objectnode)

                            'goto next record
                            mycount = mycount + 1


                            If mycount >= dt.Rows.Count Then
                                GoTo cleanup
                            Else
                                If dt.Rows(mycount)("area_desc") <> oldarea Then
                                    Exit Do
                                End If
                            End If

                        Loop
                    Loop
                Loop
            Loop

 

[cjard]

i dont see anywhere that you make a new areanode.

For example you cannot build a tree like this:

Dim tmpNode as New TreeNode

For i as Integer = 0 to 10
  tmpNode.text = "Node" & i
  myTreeView.Add(tmpNode)
Next

you MUST:

Dim tmpNode as TreeNode

For i as Integer = 0 to 10
  [B]tmpNode = New TreeNode[/B]
  tmpNode.text = "Node" & i
  myTreeView.Add(tmpNode)
Next

a new node kus be mad every time. you cannot call add and pass in any variable still pointing to the same node you passed in before

 

[pfechner]

SOLVED! Treeview Dramas

   'connect to database
        Dim myconnection As New SqlConnection("Data Source=ICAUTOLTP99\SQL2005;Initial Catalog=TarongaZoo;Persist Security Info=True;User ID=zoo_config_user;Password=password")
        Dim ta As New TarongaZooDataSetTableAdapters.app_view_menuTableAdapter
        Dim ds As New TarongaZooDataSet

        'Fill an existing table.
        ta.Fill(ds.app_view_menu)

        'Get a populated table.
        Dim dt As TarongaZooDataSet.app_view_menuDataTable = ta.GetData()

        Dim oldprecinct As String
        Dim oldarea As String
        Dim oldobjecttype As String
        Dim mycount As Integer

        Dim precinctcount As Integer
        Dim areacount As Integer
        Dim typecount As Integer

        mycount = 0
        precinctcount = 0
        areacount = 0
        typecount = 0

        If dt.Rows.Count > 0 Then

            TreeView1.Nodes.Clear()

            With TreeView1

                .BeginUpdate()
                Do Until mycount = dt.Rows.Count

                    .Nodes.Add(dt.Rows(mycount)("Precinct_desc"))
                    oldprecinct = dt.Rows(mycount)("precinct_desc")
                    areacount = 0

                    Do Until dt.Rows(mycount)("Precinct_desc") <> oldprecinct

                        .Nodes(precinctcount).Nodes.Add(dt.Rows(mycount)("Area_desc"))
                        .Nodes(precinctcount).Name = "Precinct"
                        oldarea = dt.Rows(mycount)("Area_Desc")
                        typecount = 0

                        Do Until dt.Rows(mycount)("Area_Desc") <> oldarea

                            .Nodes(precinctcount).Nodes(areacount).Nodes.Add(dt.Rows(mycount)("type_desc"))
                            .Nodes(precinctcount).Nodes(areacount).Name = "Area"
                            oldobjecttype = dt.Rows(mycount)("type_desc")

                            Do While dt.Rows(mycount)("type_desc") = oldobjecttype And dt.Rows(mycount)("area_desc") = oldarea

                                .Nodes(precinctcount).Nodes(areacount).Nodes(typecount).Nodes.Add(dt.Rows(mycount)("object_desc"))
                                .Nodes(precinctcount).Nodes(areacount).Nodes(typecount).Name = "ObjectType"
                                mycount = mycount + 1

                                If mycount = dt.Rows.Count Then
                                    GoTo cleanup
                                End If
                            Loop

                            If dt.Rows(mycount)("type_desc") <> oldobjecttype Then
                                typecount = typecount + 1
                            End If

                            If dt.Rows(mycount)("area_Desc") <> oldarea Then
                                Exit Do
                            End If

                        Loop

                        If dt.Rows(mycount)("area_Desc") <> oldarea Then
                            areacount = areacount + 1
                        End If

                    Loop

                    precinctcount = precinctcount + 1

                Loop

                .EndUpdate()

            End With

        End If

cleanup:
        TreeView1.EndUpdate()
        ta = Nothing
        dt = Nothing
        myconnection.Dispose()
        myconnection.Dispose()
        oldprecinct = Nothing
        oldarea = Nothing
        oldobjecttype = Nothing
        mycount = Nothing
        precinctcount = Nothing
        areacount = Nothing
        typecount = Nothing
        Exit Sub