Treeview nightmares - How to read data in.

Question

4.00/5 (1 vote)

See more:

HI All
I have spent most of the day looking at various tutorials and examples of how to read data in from a datareader and populate a treeview. I have looked at so many different ways of doing it (none that I can get to work) that I have forgoten the piece that I started with so I hope somebody out their has an easy solution.

I have a single mysql table called tbl_productcategories containing the following fields:

idtbl_productcategories (Auto Increment / unique)
customer_id (INT)
parent_id (INT)
category_description (VARCHAR)

The same application will be used by multiple customers and since we only want the treeview to display the categories specific to the user this will explain the need for the customer_id field.

The treeview is to display an indeterminate number of levels of children and since most of the code I have been viewing today all relies on a maximum of two levels you can probably understand my frustration.

In theory, if I sort on the idtbl_productcategories I will never have a situation where I attempt to add a child before the parent is added. My logic tells me that if I have a node index equal to the value in the autonumber field in the table then I should easily be able to add a child to that node.

Please guys...what is wrong with my logic and how do I do this?

Thanks
Steve

VB

        Dim con As New MySql.Data.MySqlClient.MySqlConnection("My Connection String goes here")
        Dim cmd As New MySql.Data.MySqlClient.MySqlCommand("Select * FROM tbl_productcategories where customer_id = 1 order by idtbl_productcategories;")
        Dim da As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim ds As New DataSet
        Dim dr As MySql.Data.MySqlClient.MySqlDataReader

        cmd.Connection = con
        If con.State <> ConnectionState.Open Then con.Open()
        dr = cmd.ExecuteReader

While dr.read
'*****THIS IS THE PIECE THAT I HAVE NO IDEA WHAT TO DO*******

End While 
        con.Close()
        DR.Close()
        cmd.Dispose()

Posted 30-Apr-12 4:45am

Steve R Adams

Updated 30-Apr-12 5:14am

ZurdoDev

v2

Add a Solution

Comments

ZurdoDev 30-Apr-12 11:15am

It depends on how your SQL is coming out. If you are going to do it manually then just add nodes to the tree view and add logic checking to see if you need to create a new node or add to existing.

Fredrik Bornander 1-May-12 6:56am

Glad I could help :)

3 solutions

Solution 1

Refer to this article:
deepak-sharma.net/2012/02/08/how-to-bind-multiple-sql-server-tables-with-a-treeview-in-a-hierarchical-order

Posted 30-Apr-12 6:06am

Deepak_Sharma_

Comments

Steve R Adams 1-May-12 4:01am

Hi Deepak - Thanks for your contribution. I will need some of the advanced functions which you have mentioned in your blog but only a bit further down the road. All I need to do right now is have something simple to work with as an example. (Im not the brightest peanut in the packet :) )

Thanks again
Steve

Solution 3

Fredrik....You are an absolute star - THANK YOU!! :)

Posted 30-Apr-12 21:58pm

Steve R Adams

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Fredrik Bornander · Accepted Answer · 2012-04-30T06:30:00

I'm assuming WinForms; if you have a form with a TreeView called TreeView1 on it then the following code reads and populates the tree view (I haven't got mysql here so I've just gone for normal sql readers but you should be able to change that);

VB

Imports System.Data.SqlClient

Public Class Form1

    Private Class ProductCategory

        Public Property Id As Int32
        Public Property CustomerId As Int32
        Public Property ParentId As Int32
        Public Property Description As String

        Public Sub New(ByVal id As Int32, ByVal customerId As Int16, ByVal parentId As Int32, ByVal description As String)
            Me.Id = id
            Me.CustomerId = customerId
            Me.ParentId = parentId
            Me.Description = description
        End Sub
    End Class


    Private Function FindParent(ByVal pc As ProductCategory, ByVal node As TreeNode) As TreeNode
        Dim tag As ProductCategory = CType(node.Tag, ProductCategory)

        If tag.Id = pc.ParentId Then Return node

        For Each child As TreeNode In node.Nodes
            Dim parent As TreeNode = FindParent(pc, child)
            If Not IsNothing(parent) Then Return parent
        Next

        Return Nothing
    End Function


    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load

        Using connection As SqlConnection = New SqlConnection("some super secret connection string")
            connection.Open()
            Using command As SqlCommand = New SqlCommand("select * from tbl_productcategories where customer_id=@customerId", connection)
                command.Parameters.Add(New SqlParameter("customerId", 1))

                Dim items As IList(Of ProductCategory) = New List(Of ProductCategory)

                Using reader As SqlDataReader = command.ExecuteReader()
                    While reader.Read()
                        Dim id As Int32 = CType(reader("idtbl_productcategories"), Int32)
                        Dim customerId As Int32 = CType(reader("customer_id"), Int32)

                        Dim parentId As Int32 = 0
                        If Not IsDBNull(reader("parent_id")) Then
                            parentId = CType(reader("parent_id"), Int32)
                        End If

                        Dim description As String = CType(reader("category_description"), String)
                        items.Add(New ProductCategory(id, customerId, parentId, description))
                    End While
                End Using

                Dim rootNode As TreeNode = New TreeNode("Products")
                rootNode.Tag = New ProductCategory(0, 0, 0, "Products")

                For Each product As ProductCategory In items.OrderBy(Function(pc) pc.ParentId)
                    Dim node As TreeNode = New TreeNode(product.Description)
                    node.Tag = product

                    Dim parentNode As TreeNode = FindParent(product, rootNode)
                    parentNode.Nodes.Add(node)
                Next
                TreeView1.Nodes.Add(rootNode)
            End Using
        End Using
    End Sub
End Class

Hope this helps,
Fredrik