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