How can i do a dynamic TreeView in vb.net

Question

Helo, Can any one help me to do a dynamic treeview with conection to a database? thank you very much.

David · Answer

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

How can i do a dynamic TreeView in vb.net

Answers (1)