2 Attachment(s)
VB2010 - Auto populate TreeView
Hi there,
Please bear with me as I am not familiar yet with all aspects and possibilities of Visual Basic.
I have a Microsoft SQL Express 2008 database setup with values shown in the attached image "Image1.jpg".
This is just an example, there can be more or less disciplines, the categories arn't necessarily the same for each discipline and each category has a set of sub-categories.
Upon form load I am trying to get this information to automatically populate a treeview, as shown in the attached image "Image2.jpg".
I have been trying for a couple of days now, and not having much luck. The code example I found was on youtube, but unfortunately it uses a binded access database and a dataset, the code is as follows:
Code:
Imports System.Data.OleDb
Public Class Form1
Private Sub TreeBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Me.Validate()
Me.TreeBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.TestDataSet)
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'TestDataSet.tree' table. You can move, or remove it, as needed.
Me.TreeTableAdapter.Fill(Me.TestDataSet.tree)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
TreeTableAdapter.Connection.Open()
TreeView1.Nodes.Clear()
FillTree("a", "first", Nothing)
TreeTableAdapter.Connection.Close()
End Sub
Public Sub FillTree(ByVal Key As String, ByVal Txt As String, ByVal N As TreeNode)
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim NN As TreeNode
If N Is Nothing Then
NN = TreeView1.Nodes.Add(Key, Txt)
Else
NN = N.Nodes.Add(Key, Txt)
End If
cn = TreeTableAdapter.Connection
cmd = New OleDbCommand("select * from tree where node_parent='" & Key & "'", cn)
Dim dr = cmd.ExecuteReader
Do While dr.Read()
FillTree(dr("node"), dr("node_name"), NN)
Loop
dr.Close()
cmd.Dispose()
End Sub
End Class
Also this seemed to only ever add 1 root node "FillTree("a", "first", Nothing)".
I had also tried this example http://support.microsoft.com/kb/320755.
I downloaded the test database, I got no errors but the TreeView was empty.
I had managed to use "SELECT DISTINCT Discipline FROM Table1" sql statement, and use a while loop with TreeView1.Nodes.Add(retrieved data) to populate the root nodes. My only idea now is to keep running sql statements e.g. SELECT Category FROM Table1 WHERE Discipline ='" & TreeView1.Nodes(i).Text & "'" and so on for the child and grandchild nodes. But I'm not even sure if this will work and I am hoping someone knows of a neater easier way.
I am getting quite frustrated with this, so if anyone can help or even post some code examples it would be greatly appreciated.
Thanks in advance, and I await your replies.
squatman
Re: VB2010 - Auto populate TreeView
You named a database and values but not Tables, that data in the Image (Discipline/Category/Sub-Category) is a Table? or it comes from different Tables? Understanding your Table structure is essential to be able to offer a solution on how to fill the Treeview.
Re: VB2010 - Auto populate TreeView
Hi jcis,
Thanks a lot for your reply.
I have a database say "testdb" with a table "Table1", the data in Image1 (Discipline/Category/Sub-Category) is exactly how it appears in the table. There are more fields and data, but I just wrote a few out into Excel and took a screenshot to upload as part of this post.
I hope that makes sense, thanks again.
Re: VB2010 - Auto populate TreeView
This FillTree() Sub takes an OleDbDataReader as parameter. Ensure all fields have valid data, because their content is used as Node Key, the TreeView name is TV, you could change this code and send it as parameter if you prefer. You need to Import System.Data.OleDb for this:
Code:
Private Sub FillTree(ByVal dr As OleDbDataReader)
Dim lNode(dr.FieldCount - 1) As TreeNode, i As Integer, lKey As String, lNodeCol As TreeNodeCollection
While dr.Read()
lKey = vbNullString
For i = 0 To dr.FieldCount - 1
lKey = lKey & dr(i)
If i = 0 Then lNodeCol = TV.Nodes Else lNodeCol = lNode(i - 1).Nodes
If Not lNodeCol.ContainsKey(lKey) Then
lNode(i) = lNodeCol.Add(lKey, dr(i))
Else
lNode(i) = lNodeCol.Item(lNodeCol.IndexOfKey(lKey))
End If
Next
End While
End Sub
Example: Connecting to Access and returning the DataReader, add all columns to ORDER BY:
Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\bd1.mdb;")
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("select Discipline, Category, Subcategory from table1 Order By Discipline, Category, Subcategory", cn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
FillTree(dr)
dr.Close()
cn.Close()
cn.Dispose()
Catch oe As OleDbException
MessageBox.Show(Me, oe.ToString, "Error:")
End Try
End Sub
This could also work when using more Fields in the DataReader, i'm not 100% sure because I only tested with 3, but it should also work.
1 Attachment(s)
Re: VB2010 - Auto populate TreeView
Jcis,
First of all I just want to thank you for your fantastic response, I appreciate the time you have taken out of your day to write the example code, not many would, and this is always extremely helpful.
I have just tested it, this is my code:
Code:
Imports System.Data.OleDb
Public Class Form1
Private Sub FillTree(ByVal dr As OleDbDataReader)
Dim lNode(dr.FieldCount - 1) As TreeNode, i As Integer, lKey As String, lNodeCol As TreeNodeCollection
While dr.Read()
lKey = vbNullString
For i = 0 To dr.FieldCount - 1
lKey = lKey & dr(i)
If i = 0 Then lNodeCol = TV.Nodes Else lNodeCol = lNode(i - 1).Nodes
If Not lNodeCol.ContainsKey(lKey) Then
lNode(i) = lNodeCol.Add(lKey, dr(i))
Else
lNode(i) = lNodeCol.Item(lNodeCol.IndexOfKey(lKey))
End If
Next
End While
End Sub
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Try
Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source =C:\Databases\testtreedb3.accdb;")
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1 ORDER BY Discipline, Category, Sub-category", cn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
FillTree(dr)
dr.Close()
cn.Close()
cn.Dispose()
Catch oe As OleDbException
MessageBox.Show(Me, oe.ToString, "Error:")
End Try
End Sub
End Class
Unfortunately when I click the button I get the error message attached.
I have double checked database name/location, table name and column titles.
Any help would again be greatly appreciated.
Thank you.
squatman
Re: VB2010 - Auto populate TreeView
I implemented data binding in a TreeView recently. If you're interested let me know. You're gonna have to change your table structure slightly though for my TreeView to work with your data the way you expect.
Re: VB2010 - Auto populate TreeView
Niya,
Thank you for your response, if by data binding you mean in the design mode you specify a datasource, I don't think this would work as variables and operations before this stage in the program determine which database and table it will look at to populate the TreeView, the TreeView ends up sort of being a variable.
I apologise if I have miss understood or your method can accomplish this as well, I may have to consult you further to try this out.
I am hoping to get jcis's method working as it looks great and will hopefully accomplish what I am trying to achieve, but please let me know what you think, and thanks again for your repsonse, it is much appreciated.
Re: VB2010 - Auto populate TreeView
The DataSource of all bindable controls including my TreeView could be set at runtime.
Re: VB2010 - Auto populate TreeView
Oh excellent, thank you.
If you have a code example you could link me to, I'd love to check it out.
Thanks.
1 Attachment(s)
Re: VB2010 - Auto populate TreeView
I've attached the two source files for the TreeView. Add it to your project and compile. You should then be able to drag it as you would a normal TreeView onto the designer.
Set the DataSource property to a DataTable. Also set the DisplayMember property to the name of the field you wish to use as the text of the node. Set the IDMember property to the ID field of rows and set the ParentIDMember property to the field that your rows use to determine parentage. You may need to rework your database table structure to be compatible with this.
Re: VB2010 - Auto populate TreeView
Thanks a lot Niya, I'm greatful you took the time to post this.
I will give it a go now and get back to you.
Re: VB2010 - Auto populate TreeView
Niya,
I have just had a look and the code for both of those just looks too complex for me at the moment I think.
Thanks for uploading it for me, but I'm hoping to get something more like jcis's code working.
Thanks again,
squatman
Re: VB2010 - Auto populate TreeView
Looks like the error you're getting fires when the command executes the SELECT line:
Code:
select Discipline, Category, Subcategory from table1 Order By Discipline, Category, Subcategory
Are you sure table and fields have the correct name as they are in your db? This is a typical "wrong field name" error.
Also: This problem with the db is not related to the Treeview (main topic), so if you know another way to succesfully connect to your db then just do it that way, after that create an OleDbDataReader with your query results and send it as parameter to FillTree Sub.
Re: VB2010 - Auto populate TreeView
OMG!
jcis you are an absolute god! Thank you so much! I have been trying for days now, and your short bit of code worked so perfectly! I can't believe it!
I honestly can't thank you enough, I wish there was something I could do for you.
The problem was my field name in database is "Sub-category" so I needed to show it in SQL statement as `Sub-category`.
Oh man, thank you so much, you are a true legend, thank you!