VB TreeView and MySQL

[churni]

Hi All

I am creating a program which will (in the end) help me quickly transfer products from one category into another within a MySQL Database.

I have a database with TWO separate tables which are connected using INNER JOIN where the products have a categoryID as do the categories have categoryID fields.

I want to show, in a treeview, all of the categories and then when i 'open' a category, i can see all of the products. eventually I'll add the moving properties, sub categories etc but at the moment i just do not know how to display a parent and child properly.

I am getting this;

See Image Attachment
you can see that 35mm ... should be the parent (category) and the sony ... should be the products

and this is my code

Imports MySql.Data
Imports MySql.Data.MySqlClient


Public Class Form1
    Dim dbCon As MySqlConnection
    Dim strQuery As String = ""
    Dim SQLCmd As MySqlCommand
    Dim DR As MySqlDataReader
    


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

    End Sub

    Private Sub GetDBData()
        Try
            ' Prepare conn and query
            dbCon = New MySqlConnection("Server=localhost;Database=xxxxxxx;Uid=xxxx")

            strQuery = "SELECT categories.category_name, categories.homepagevis, products.product_name " & _
                "FROM categories INNER JOIN products ON categories.categoryID = products.categoryID " & _
                "ORDER BY categories.category_name ASC "

            SQLCmd = New MySqlCommand(strQuery, dbCon)
            Dim DA As New MySqlDataAdapter(SQLCmd)
            Dim DT As New DataTable()
            DA.Fill(DT)

            'Open db and kick of query
            dbCon.Open()

            DR = SQLCmd.ExecuteReader

            Dim parentnodes As TreeNode
            Dim childnodes As TreeNode

            While DR.Read()
                parentnodes = New TreeNode(DR("category_name"))
                childnodes = New TreeNode(DR("product_name"))
                TreeView1.Nodes.Add(parentnodes)
                TreeView1.Nodes.Add(childnodes)
            End While

            'For Each DR As DataRow In DT.Rows
            '    Dim tn As New TreeNode()
            '    tn.Text = DR("category_name").ToString()
            '    TreeView1.Nodes.Add(tn)
            'Next

            'While DR.Read
            '    txtData.Text = txtData.Text & DR.Item("category_name") & Space(10) & DR.Item("homepagevis") & Space(5) & DR.Item("product_name") & vbCrLf
            'End While

            ' done! close database
            DR.Close()
            dbCon.Close()
        Catch ex As Exception
            MsgBox("failure to communicate" & vbCrLf & vbCrLf & ex.Message)
        End Try
    End Sub
End Class

I would much appreciate any help with this,

Thank you.

I must add, i am at beginner-intermediate level and i can answer any queries you may have.

[GeekByChoiCe]

Try this code:

While DR.Read
			If TreeView1.Nodes.ContainsKey("tvNode" & index) Then 'check if a node with that name already exist
				TreeView1.Nodes.Item("tvNode" & index).Nodes.Add(DR("product_name").ToString) 'add product to this node
			Else
				Dim parentNode As TreeNode = New TreeNode() With {.Name = "tvNode" & index, .Text = DR("category_name").ToString} 'create a new node with Name and Text property
				parentNode.Nodes.Add(DR("product_name").ToString) 'add product to this node
				TreeView1.Nodes.Add(parentNode)	'add category node to treeview
				index += 1 'increase index for the uniq name creation
			End If
		End While

[churni]

Thanks for your help. this made the parent and childs work but still kept repeating the categories. nevertheless i have decided to program this in PHP rather than vb.net. thank you

I will post again if i decide to revert back to vb.net

[invisal]

I am assuming that each category has its own unique name. With your current query which you have already ordering the record by category name, it is very convenience to arrange it into tree.

Dim currentCategory As String = ""
        Dim currentParent As TreeNode

        While DR.Read
            If currentCategory <> DR("category_name") Then
                currentParent = New TreeNode(DR("category_name"))
                TreeView1.Nodes.Add(currentParent)
                currentCategory = DR("category_name")
            End If

            currentParent.Nodes.Add(DR("product_name"))
        End While