How can i do a dynamic TreeView in vb.net

I have a VB.net application that allows my wife to enter data into a mySQL database in a dynamic tree view, so she need not know anything about SQL database manipulation.  Here is the sub that updates (builds) the tree upon any database changes or data entry. 

Note Tv1 is the treeview object on the form

Sub BuildTree()

Dim rsItems As New ADODB.Recordset, rsAttributes As New ADODB.Recordset, rsValues As New ADODB.Recordset

Dim rsCatagory As New ADODB.Recordset

Dim strsql, strAtt As String

Dim lngItemID As Long

Dim ItemNode, DetailsNode, ValueNode As Integer, CatagoryNode As Integer = -1

m_Suppress = True

Tv1.Nodes.Clear()

Tv1.PathSeparator = vbLf

Tv1.BeginUpdate()

strsql = "SELECT * FROM tblCatagories ORDER BY Catagory"

clsSQL.ExecuteSQL(strsql, rsCatagory)

If rsCatagory.RecordCount > 0 Then

  rsCatagory.MoveFirst()

  Do

    If Not rsCatagory.EOF Then

      CatagoryNode += 1

      ItemNode = -1

      Tv1.Nodes.Add(UCase(Trim(rsCatagory("Catagory").Value)))

      strsql = "SELECT * FROM tblItems where Catagory = '" & rsCatagory("Catagory").Value & "' ORDER BY ITEMNUMBER"

      clsSQL.ExecuteSQL(strsql, rsItems)

      If rsItems.RecordCount > 0 Then

        rsItems.MoveFirst()

        Do

          If Not rsItems.EOF Then

            ItemNode += 1

            lngItemID = CType(rsItems("ID").Value, Long)

            Tv1.Nodes(CatagoryNode).Nodes.Add(Trim(UCase(rsItems("ItemNumber").Value)) & " :: " & Trim(UCase(rsItems("Description").Value)))

            Tv1.Nodes(CatagoryNode).Nodes(ItemNode).ForeColor = Color.Blue

            If rsItems("SubCatagory").Value Is DBNull.Value Then

              Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes.Add("SUB-CATAGORY:")

            Else

              Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes.Add("SUB-CATAGORY: " & Trim(UCase(rsItems("SubCatagory").Value)))

            End If

            Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes(0).ForeColor = Color.BlueViolet

            Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes.Add("ACTIVE: " & UCase(CType(rsItems("Active").Value, Boolean)))

            Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes(1).ForeColor = Color.BlueViolet

            Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes.Add(Trim(UCase(rsItems("Manufacturer").Value)))

            Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes(2).ForeColor = Color.BlueViolet

            Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes.Add("$ " & Format(CType(rsItems("BasePrice").Value, Single), "###,##0.00"))

            Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes(3).ForeColor = Color.BlueViolet

            DetailsNode = 3

            strsql = "SELECT * FROM tblAttributes WHERE ItemID = '" & Trim(lngItemID.ToString) & "' ORDER BY Attribute"

            clsSQL.ExecuteSQL(strsql, rsAttributes)

            If rsAttributes.RecordCount > 0 Then

              rsAttributes.MoveFirst()

              Do

                If Not rsAttributes.EOF Then

                  DetailsNode += 1

                  Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes.Add(Trim(UCase(rsAttributes("Attribute").Value)))

                  Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes(DetailsNode).ForeColor = Color.Brown

                  strsql = "SELECT * FROM tblAttributeDetails WHERE ItemID = '" & Trim(lngItemID.ToString) & "' AND Attribute = '" & rsAttributes("Attribute").Value & "' ORDER BY Tag"

                  clsSQL.ExecuteSQL(strsql, rsValues)

                  If rsValues.RecordCount > 0 Then

                    rsValues.MoveFirst()

                    ValueNode = -1

                    Do

                      If Not rsValues.EOF Then

                        ValueNode += 1

                        strAtt = Trim(UCase(rsValues("Tag").Value))

                        If Not rsValues("SkuTag").Value Is DBNull.Value Then

                          If Trim(rsValues("SkuTag").Value) <> "" Then

                            strAtt &= ", " & UCase(Trim(rsValues("SkuTag").Value)) & ""

                          Else

                            strAtt &= ", "

                          End If

                        Else

                          strAtt &= ", "

                        End If

                        If CType(rsValues("DeltaPrice").Value, Single) <> 0 Then

                          strAtt &= ", $" & Format(CType(rsValues("DeltaPrice").Value, Single), "###,##0.00")

                        End If

                        Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes(DetailsNode).Nodes.Add(strAtt)

                        Tv1.Nodes(CatagoryNode).Nodes(ItemNode).Nodes(DetailsNode).Nodes(ValueNode).ForeColor = Color.Red

                        rsValues.MoveNext()

                      End If

                    Loop While Not rsValues.EOF

                  End If

                  rsValues.Close()

                  rsAttributes.MoveNext()

                End If

              Loop While Not rsAttributes.EOF

            End If

            rsAttributes.Close()

            rsItems.MoveNext()

          End If

        Loop While Not rsItems.EOF

      End If

      rsItems.Close()

      rsCatagory.MoveNext()

    End If

  Loop While Not rsCatagory.EOF

End If

rsCatagory.Close()

Tv1.EndUpdate()

End Sub


Obviously, the tree is as follows

CATAGORY
    ITEM NUMBER :: DESCRIPTION
        SUB-CATAGORY (if any)
            ACTIVE (True/False)
            VENDOR
            BASE PRICE
            PRODUCT OPTION1 (optional)
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                .
                .
                .
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
            .
            .
            .
            PRODUCT OPTION (optional)
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                .
                .
                .
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
    .
    .
    .
    ITEM NUMBER :: DESCRIPTION
        SUB-CATAGORY (if any)
            ACTIVE (True/False)
            VENDOR
            BASE PRICE
            PRODUCT OPTION1 (optional)
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                .
                .
                .
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
            .
            .
            .
            PRODUCT OPTION (optional)
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                .
                .
                .
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
.
.
.
CATAGORY
    ITEM NUMBER :: DESCRIPTION
        SUB-CATAGORY (if any)
            ACTIVE (True/False)
            VENDOR
            BASE PRICE
            PRODUCT OPTION1 (optional)
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                .
                .
                .
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
            .
            .
            .
            PRODUCT OPTION (optional)
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                .
                .
                .
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
    .
    .
    .
    ITEM NUMBER :: DESCRIPTION
        SUB-CATAGORY (if any)
            ACTIVE (True/False)
            VENDOR
            BASE PRICE
            PRODUCT OPTION1 (optional)
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                .
                .
                .
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
            .
            .
            .
            PRODUCT OPTION (optional)
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE
                .
                .
                .
                OPTION ATTRIBUTE NAME, SKU NUMBER MODIFIER, PRICE DIFFERENCE


Here is my generic sql class

Public Class CLASS_SQL

  Sub ExecuteSQL(ByVal strSql As String, ByVal rsRecord As ADODB.Recordset)

    Dim sqlConn As New SqlClient.SqlConnection

    Dim dbData As New ADODB.Connection

    Dim strDbPassword, strDbUser As String

    strDbPassword = "xxxxxxxxxxx" (Your DB password)

    Dim strSource As String = gstrSQLServerName ' a global string with the sql server name

    Select Case gudtDbType

      Case Is = MODULE_DBENUM.DBTYPE.MSSQL

        strDbUser = "sa"

        dbData.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=" & gstrDb & ";Data Source=" & strSource & ";uid=" & strDbUser & ";pwd=" & strDbPassword

      Case Is = MODULE_DBENUM.DBTYPE.MYSQL

        strDbUser = "root"

        dbData.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & strSource & ";DATABASE=" & gstrDb & ";UID=" & strDbUser & ";PWD=" & strDbPassword & ";OPTION=35"

  End Select

  Try

    dbData.Open()

    rsRecord.CursorLocation = ADODB.CursorLocationEnum.adUseClient

    rsRecord.CursorType = ADODB.CursorTypeEnum.adOpenStatic

    rsRecord.LockType = ADODB.LockTypeEnum.adLockReadOnly

    rsRecord.Open(strSql, dbData)

    rsRecord.ActiveConnection = Nothing

    dbData.Close()

  Catch ex As Exception

    MsgBox(ex.Message, MsgBoxStyle.Critical, "USER ALARM")

   End Try

End Sub

End Class

Finally, a module to define those enums

Public Module MODULE_DBENUM

  Public gstrDb As String

  Public gstrSQLServerName As String

  Public Enum DBTYPE

    MYSQL = 0

    MSSQL = 1

  End Enum

  Public gudtDbType As DBTYPE

End Module

There is A TON more code that allows her toadd, delete & edit all database fields, shown a varying levels of the tree view.  For this application, the tree view is perfect!

Hope This Helps