ericlockshine
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.
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.
SELECT state FROM myTable GROUP ON state
you can also do
SELECT DISTINCT state FROM myTable
SELECT DISTINCT state FROM myTable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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.
ASKER
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!
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!