Link to home
Start Free TrialLog in
Avatar of ericlockshine
ericlockshineFlag for United States of America

asked on

Reading Data into TreeView from SQL Table

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.
Avatar of craskin
craskin
Flag of United States of America image

SELECT state FROM myTable GROUP ON state
you can also do

SELECT DISTINCT state FROM myTable
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ericlockshine

ASKER

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?
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.
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!