Results 1 to 14 of 14

Thread: VB2010 - Auto populate TreeView

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    Question 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
    Attached Images Attached Images   

  2. #2
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    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.
    Last edited by jcis; May 10th, 2012 at 01:08 AM.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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.

  4. #4
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    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.
    Last edited by jcis; May 10th, 2012 at 09:11 AM.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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
    Attached Images Attached Images  

  6. #6
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    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.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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.

  8. #8
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: VB2010 - Auto populate TreeView

    The DataSource of all bindable controls including my TreeView could be set at runtime.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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.

  10. #10
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    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.
    Attached Files Attached Files
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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.

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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

  13. #13
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    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.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    May 2012
    Posts
    304

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width