Question How to use a tree view to select a location

[ngugi]

Hi Guys,
I have a problem which I hope you can help me solve. How do I display my database data in a tree view. Here is the scenarion.

I have table called locations that I want to display the location in a hierarchy manner e.g state, cities, counties going down. Eg If I have a state like Ohio it display all the related cities, if I select a city the town in that city should be displayed. My SQL table has 4 fields id, parentid, name, location code.

id=sql identity that autoincreases
parentId=should refer to the top level location eg a location with parentId 0 represents all the states,
name=its the actual name of the location
code=this is the code of the location.

The SQL Table is as follows.

CREATE TABLE [dbo].[Locations](
[id] [int] IDENTITY(1,1) NOT NULL,
[parentId] [int] NOT NULL,
[name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

 [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


So if there is VB.Net form that can be able to display this hierarchy and display the selected location using a message box I will be grateful

 

[alander]

Select all and save it to a form level variable (of type datatable)
dim dt as datatable
dt = (ur sql select statement select * and save this result in ur datatable)


use

dim dr() as datarow =  dt.select("code = 0") 'if the code is 0, it is a country (this is an assumption)

for each dr u have a code, this code i believe correspond to the parentid of childrens

so in ur for loop
u create a treenode

for i as integer = 0 to dr.length-1
Dim q = New System.Windows.Forms.TreeNode _
                With {.Text = 'the country name, _ 'dr(i)(fieldname).tostring()
                .Tag = 'the country code, _
                .Name = 'the country code}
                treecountry.nodes.add(q)
                addchild(code,parentid,name,q)

next

private sub addChild(byval code as string, byval parentid as string, byval name as string, q as treenode)

'find all the childrens
dim drChild() as datarow = dt.select("Parentid = " & dr(i)("Code").tostring())
for i as integer = 0 to drChild.length-1
Dim childQ = New System.Windows.Forms.TreeNode _
                With {.Text = 'the country name, _ 'drChild(i)(fieldname).tostring()
                .Tag = 'the country code, _
                .Name = 'the country code}
q.nodes.add(childq)

'check for grand child
dim drGrandChild() as datarow = dt.select("Parentid = " & drChild(i)("Code").tostring())
if drGrandChild.length > 0
addChild(code,parentid,name,q)
end if
next
end sub

not tested code

might contain typo

but the idea is there

good luck

for the onclick messagebox, refer to the tree api event handlers

 

[cjard]

Select all and save it to a form level variable (of type datatable)

Ugh.. isn't this what databases are for?


I'd put the treeview on the form, run the query: SELECT * FROM places WHERE parentID = 0

assuming all STATES have parentID of 0

Then populate the names of states into the tree. You may have to add a dummy node onto each parent in order to get it to show its [+] symbol
When the user expands a node, catch the event and retrieve the ID of the node (you should store the ID of the state into the tag of the node)
e.g. user clicks VIRGINIA and this has id 17, run the same query:

SELECT * FROM places WHERE parentID = 17

Populate the children of VIRGINIA with the results


Essentially you should write one method that accepts a NODE as an argument, and populates all children using the DB, and a query of having (that node passed into the method)'s ID as their Parent


pseudocode:

Sub PopulateChildren(tn as TreeNode)
  'run select comamnd
  dt as PlacesDatatable = PlacesTableAdapter.GetChildrenForID(tn.Tag)

  'loop the children row adding them to the node
  ForEach ro as PlacesRow in dt
    Dim n as New TreeNode(name of node)
    n.Tag = ro.ID
    tn.Nodes.Add(n)
  Next
End Sub

'in form load
Dim n as New TreeNode("all places")
n.Tag = "0"
PopulateChildren(n)


'in treview nodeexpanding
If Not node.HasChildren Then
  PopulateChildren(node)
End if

This is how to keep your form light and quick rather than downloading allthe db contents into it, only download those the user wants