janmarini
asked on
Dynamic TreeView from DB / Database - best logic, VB.net Windows Form
Hello,
I have an application that requires the use of a TreeView control. I am able to apply the MS TreeView to my windows form and can get it to do the basic stuff. However, my users need to be able to add new nodes via updating an SQL database (not interactively - via a Stored Procedure, or manual updating), and the new root/parent/child/grandchi ld ends up being the last record in the table. I run an SQL statement to sort everything by ParentID, Level and Sequence. Still, I am having a difficult time in creating the most easiest to use structure, and I am finding that I am having to jump through hoops in my programming logic to get it to work. I strongly feel that there must be an easier way, but I'm just not able to see it (I think I might be stuck on my last project that did all of this via VB 6 and user controls - that compared to this seemed easy). I would greatly appreciate any advice on how I might explore other alternatives to the path I am following.
Thank you,
Jan
Table Structure
iID, sName, iParentID, iLevel, iSequence
Example:
iID sNAME iPARENTID iLEVEL iSEQUENCE
1 Batch Report Group 0 0 1
2 Machine Report Group 0 0 2
3 Management Reports 0 0 3
4 Performance Reports 0 0 4
5 Misc Reports 0 0 5
6 Find 0 0 6
7 Batch List Pr 1 1 1
8 Batch Status Pr 1 1 4
9 Machine Batch Pr 2 1 1
10 Mach Maintenance Pr 2 1 4
12 BatchSize Summary 3 1 1
.
.
.
To build the tree in VB.Net:
This is where I get stuck - and need a breath of fresh air.
- I cannot find a way to sort the table in a top-down structure (Root/Parent/etc.) without the user having to manually redo the sort (a new - and unwanted process/field) each time something new is added to the table.
- I find that I can not do this in a single step programmaticaly
- For my first pass through I am using SQLDataReader - and can add new levels to the root
- I then call another query to obtain the children - but can't get past this point - with the structure I have.
It's frustrating because if I had a table that I could just read through this would be easy - but that's not going to happen. Need a new perspective here - I appreciate your help and experience. If you would llike to see my code attempts (some of my commented out code came close - but didn't do the the job), I'll include it. But I am thinking I really do need a fresh approach.
Jan
I have an application that requires the use of a TreeView control. I am able to apply the MS TreeView to my windows form and can get it to do the basic stuff. However, my users need to be able to add new nodes via updating an SQL database (not interactively - via a Stored Procedure, or manual updating), and the new root/parent/child/grandchi
Thank you,
Jan
Table Structure
iID, sName, iParentID, iLevel, iSequence
Example:
iID sNAME iPARENTID iLEVEL iSEQUENCE
1 Batch Report Group 0 0 1
2 Machine Report Group 0 0 2
3 Management Reports 0 0 3
4 Performance Reports 0 0 4
5 Misc Reports 0 0 5
6 Find 0 0 6
7 Batch List Pr 1 1 1
8 Batch Status Pr 1 1 4
9 Machine Batch Pr 2 1 1
10 Mach Maintenance Pr 2 1 4
12 BatchSize Summary 3 1 1
.
.
.
To build the tree in VB.Net:
This is where I get stuck - and need a breath of fresh air.
- I cannot find a way to sort the table in a top-down structure (Root/Parent/etc.) without the user having to manually redo the sort (a new - and unwanted process/field) each time something new is added to the table.
- I find that I can not do this in a single step programmaticaly
- For my first pass through I am using SQLDataReader - and can add new levels to the root
- I then call another query to obtain the children - but can't get past this point - with the structure I have.
It's frustrating because if I had a table that I could just read through this would be easy - but that's not going to happen. Need a new perspective here - I appreciate your help and experience. If you would llike to see my code attempts (some of my commented out code came close - but didn't do the the job), I'll include it. But I am thinking I really do need a fresh approach.
Jan
Did you ORDER BY fieldname the Database? Or is this just a Treeview problem.
Regards
Shaztronics
Regards
Shaztronics
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all for your answers, I really appreciated them. James, I was able to study your solution and use it as an excellent foundation to finally getting it all to work exactly how I wanted. It will take me a little work to document what my end result looks like - a little different from yours, but I hope that both will help others facing the same challenge. Your sample was the perfect mentor. Thanks!
Now my boss wants this (LOL) - OK - no complaints...OK - after reflecting on this for a moment...HELP!!:
"I like this. It has a clean appearance and is functional. The only thing I might suggest we add is if we could right click and add to a favorites list and or on the selection and list the 5 most recent selections like:
Most Recent
Report 1
Report 2
Report 3
My Favorites
Report 1
Report 2
Report 3"
If there's a will, there's a way :-)
Now my boss wants this (LOL) - OK - no complaints...OK - after reflecting on this for a moment...HELP!!:
"I like this. It has a clean appearance and is functional. The only thing I might suggest we add is if we could right click and add to a favorites list and or on the selection and list the 5 most recent selections like:
Most Recent
Report 1
Report 2
Report 3
My Favorites
Report 1
Report 2
Report 3"
If there's a will, there's a way :-)
Try adding all your tree nodes first and then do this
myTreeControl.sorted=True
regards,
sukhdev.