Reading Data into TreeView from SQL Table

ericlockshine
ericlockshine used Ask the Experts™
on
Hi all,
I have a table with Zip Codes in it.  From this, I want to create a tree view of data.  However, I need to drill down and give people options on what they are allowed to select.  My datatable contains 4 columns:
State, County, City, Zip
I want to create a parent node at their level (State, County, or City).  and then give them the ability to select anything below them
for instance, I am at a county level.  In the tree view, I want to see my county, then all the cities in my county and then all my zip codes in my city.  So my tree would look like this:
County
|-City1
|--Zip1C1
|--Zip2C1
|-City2
|--Zip1C2
|-City3
|--Zip1C3
|--Zip2C3
|--Zip3C3

But, if I am at the state level, I want to be able to have the state as the parent, and then each county under that, and each city....

How can I separate out the parent (I already know what level they are at) and then create all the children.  This is all in 1 table.
I do have the table set at the correct level for data reading (meaning I do a select * from table where state='mystate' if I am at state level, select * from table where state='mystate' and county='mycounty' for county level, and so on.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
SELECT state FROM myTable GROUP ON state

Commented:
you can also do

SELECT DISTINCT state FROM myTable
Senior .Net Consultant
Top Expert 2016
Commented:
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

ericlockshineDirector of IT

Author

Commented:
craskin:
I already have the data in one table from the database.  That is not what I was asking but thanks for the help.
emoreau:  Do you know how to create relationships out of the same table?  Do I have to retrieve the data in a different way to allow for the tree to be built?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
see the code in the "Filling the Treeview" section. You need to have 2 datatables (1 for city and one for zips in your case) and establish a DataRelation between those 2 DataTables.
ericlockshineDirector of IT

Author

Commented:
Thanks emoreau:
I was able to populate the nodes and create the relationships.  What I had to do was this:  For each level that I was at, select the appropriate information from my database.  So, for each level I need to select one more level.  Your example gave me the information that I needed to program the tree and to decide how to select the information from the database.

I have All States, Counties, Cities and Zip Codes to load.  For each level, they get access at their level: Cities get city level and zip level, Counties get county level, city level and zip level.  Once I figured out how to get this out of the database, the rest was easy.

Thanks for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial