locked
SAVING SETTINGS TO SQL USING TREEVIEW RRS feed

  • Question

  • Dear All

    I want to save settings of users using treeview to sql server using vb.net

    how can i do that 

    please see attachment of my treeview

    Wednesday, May 20, 2020 9:12 AM

All replies

  • Hi Omi4u,

    Thank you for posting here.

    According to your question, I make a test on my side.

    In order to save the selected nodes to database, you can refer to the following code:

    Imports System.Data.SqlClient
    Imports System.Transactions
    
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim node1 As TreeNode = New TreeNode("items")
            Dim node2 As TreeNode = New TreeNode("Stock In")
            Dim node3 As TreeNode = New TreeNode("Stock Out")
            Dim node4 As TreeNode = New TreeNode("Search Items")
            Dim array As TreeNode() = New TreeNode() {node1, node2, node3, node4}
            Dim treeNode As TreeNode = New TreeNode("Stock", array)
            TreeView1.Nodes.Add(treeNode)
        End Sub
        Private Sub SaveBtn_Click(sender As Object, e As EventArgs) Handles SaveBtn.Click
            Dim lst As List(Of String) = New List(Of String)()
            GetCheckedNodes(lst, TreeView1.Nodes(0).Nodes)
            Dim connstring As String = "your connection string"
    
            Using conn As SqlConnection = New SqlConnection(connstring)
                conn.Open()
                WriteToBase(lst, conn)
            End Using
        End Sub
        Private Sub WriteToBase(ByVal lst As List(Of String), ByVal Connection As SqlConnection)
            Using scope As TransactionScope = New TransactionScope()
                Dim sqlIns As String = "INSERT INTO TestTable ([Id],[CheckedName]) VALUES (@id, @Name)"
                Dim cmdIns As SqlCommand = New SqlCommand(sqlIns, Connection)
    
                For i As Integer = 0 To lst.Count - 1
                    cmdIns.Parameters.Clear()
                    cmdIns.Parameters.AddWithValue("@id", i + 1)
                    cmdIns.Parameters.AddWithValue("@Name", lst(i))
                    cmdIns.ExecuteNonQuery()
                Next
                scope.Complete()
            End Using
        End Sub
    
        Public Shared Sub GetCheckedNodes(ByVal lst As List(Of String), ByVal nodes As TreeNodeCollection)
            For Each aNode As System.Windows.Forms.TreeNode In nodes
                If Not aNode.Checked Then Continue For
                lst.Add(aNode.Text)
                If aNode.Nodes.Count <> 0 Then GetCheckedNodes(lst, aNode.Nodes)
            Next
        End Sub
    
    End Class

    Result of my test:

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 21, 2020 2:49 AM
  • hi @ xingyu zhao

    how are you . thank you for your code they worked fine but how i will load the saved treeview selected items.

    Thursday, May 21, 2020 8:48 AM
  • Hi Omi4u,

    First you need to save the root node.

        Dim parNode As String
        Private Sub SaveBtn_Click(sender As Object, e As EventArgs) Handles SaveBtn.Click
            parNode = TreeView1.Nodes(0).Text
    
            '...
        End Sub

    Then you can refer to the following code to load the saved treeview selected items.

        Private Sub LoadBtn_Click(sender As Object, e As EventArgs) Handles LoadBtn.Click
            Dim connstring As String = "your connection string"
            Dim node As TreeNode = New TreeNode(parNode)
            Using conn As SqlConnection = New SqlConnection(connstring)
                conn.Open()
                Using cmd As SqlCommand = New SqlCommand()
                    cmd.Connection = conn
                    cmd.CommandText = "SELECT CheckedName FROM TestTable"
                    Dim reader = cmd.ExecuteReader()
                    While reader.Read()
                        Dim result = reader(0).ToString()
                        node.Nodes.Add(New TreeNode(result))
                    End While
                    TreeView1.Nodes.Clear()
                    TreeView1.Nodes.Add(node)
                End Using
            End Using
        End Sub

    Result:

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 22, 2020 6:50 AM
  • Hi,
    try following demo:

    Imports System.Data.SqlClient
    
    Public Class Form1
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {TreeView1, btnCreate, btnSave, btnLoad})
      End Sub
    
      Private WithEvents btnSave As New Button With {.Text = "Save data", .Dock = DockStyle.Top}
      Private WithEvents btnLoad As New Button With {.Text = "Load Data", .Dock = DockStyle.Top}
      Private WithEvents btnCreate As New Button With {.Text = "Create DataTable", .Dock = DockStyle.Top}
      Private TreeView1 As New TreeView With {.Dock = DockStyle.Fill, .CheckBoxes = True}
    
      Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
        ' clear TreeView
        TreeView1.Nodes.Clear()
        ' add root node
        Dim n0 As New TreeNode("Stock")
        TreeView1.Nodes.Add(n0)
        ' load data and populate tree
        Try
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              cmd.CommandText = "SELECT * FROM Table1;"
              Dim rdr = cmd.ExecuteReader
              Do While rdr.Read
                Dim n1 = New TreeNode With {.Text = rdr("NodeName").ToString, .Tag = rdr("ID"), .Checked = CType(rdr("Selected"), Boolean)}
                n0.nodes.Add(n1)
              Loop
              rdr.Close()
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message & vbCrLf & ex.ToString, vbCritical)
        End Try
        TreeView1.ExpandAll()
      End Sub
    
      Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Try
          ' Load root node
          Dim n0 As TreeNode = TreeView1.Nodes(0)
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              cmd.CommandText = "Update Table1 SET Selected=@Selected WHERE ID=@ID;"
              Dim par1 = cmd.Parameters.Add("@Selected", SqlDbType.Bit)
              Dim par2 = cmd.Parameters.Add("@ID", SqlDbType.Int)
              For Each n1 As TreeNode In n0.Nodes
                par1.Value = n1.Checked
                par2.Value = n1.Tag
                cmd.ExecuteNonQuery()
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message & vbCrLf & ex.ToString, vbCritical)
        End Try
      End Sub
    
      Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
        Try
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              ' delete previous table in SQL Server 2016 and above
              cmd.CommandText = "DROP TABLE IF EXISTS Table1;"
              ' delete previous table in previous versions of SQL server 2016
              'cmd.CommandText = "If OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE IF EXISTS Table1;"
              Debug.Print($"Return value Drop: {cmd.ExecuteNonQuery}")
              ' Cretae table
              cmd.CommandText = "CREATE TABLE Table1([ID] Integer Identity, [NodeName] nvarchar(50), [Selected] bit, CONSTRAINT [PK_Table1] PRIMARY KEY ([ID]));"
              Debug.Print($"Return value Create Table: {cmd.ExecuteNonQuery}")
              ' Insert records 
              Dim names() As String = {"Items", "Stock In", "Stock Out", "Search Items", "Low Stock", "Expiry Stock", "Stock Journal", "Master", "Bulk Update Item", "Print"}
              cmd.CommandText = "INSERT Table1([NodeName],[Selected]) VALUES(@NodeName,0);"
              Dim par1 = cmd.Parameters.Add("@NodeName", SqlDbType.NVarChar, 50)
              For Each name As String In names
                par1.Value = name
                cmd.ExecuteNonQuery()
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message & vbCrLf & ex.ToString, vbCritical)
        End Try
      End Sub
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Friday, May 22, 2020 7:06 AM
  • Hi,
    if you have a complex structure you can use following demo:

    Imports System.Data.SqlClient
    
    Public Class Form1
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {TreeView1, btnCreate, btnSave, btnLoad})
      End Sub
    
      Private WithEvents btnLoad As New Button With {.Text = "Load Data", .Dock = DockStyle.Top}
      Private WithEvents btnSave As New Button With {.Text = "Save data", .Dock = DockStyle.Top}
      Private WithEvents btnCreate As New Button With {.Text = "Create DataTable", .Dock = DockStyle.Top}
      Private TreeView1 As New TreeView With {.Dock = DockStyle.Fill, .CheckBoxes = True}
    
      Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
        LoadNodes()
      End Sub
    
      Private Sub LoadNodes()
        ' Load TreeView
        TreeView1.Nodes.Clear()
        Try
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              cmd.CommandText = "SELECT ID, NodeName, NodeID, ParentID, Selected FROM Table1 where ParentID = @parent;"
              cmd.Parameters.Add("@parent", SqlDbType.Int)
              LoadNodes(TreeView1.Nodes, 0, cmd)
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message & vbCrLf & ex.ToString, vbCritical)
        End Try
        TreeView1.ExpandAll()
      End Sub
    
      Private Sub LoadNodes(nodes As TreeNodeCollection, parent As Integer, cmd As SqlCommand)
        cmd.Parameters(0).Value = parent
        Dim l As New List(Of MyTreenode)
        Dim rdr = cmd.ExecuteReader
        Do While rdr.Read
          Dim n = New MyTreenode With {.Text = rdr(1).ToString,
            .ID = CType(rdr(0), Integer),
            .NodeID = CType(rdr(2), Integer),
            .ParentID = CType(rdr(3), Integer),
            .Checked = CType(rdr(4), Boolean)}
          nodes.Add(n)
          l.Add(n)
        Loop
        rdr.Close()
        For Each n In l
          LoadNodes(n.Nodes, n.NodeID, cmd)
        Next
      End Sub
    
      Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        SaveNodes()
      End Sub
    
      Private Sub SaveNodes()
        Try
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              cmd.CommandText = "Update Table1 SET Selected=@Selected WHERE ID=@ID;"
              cmd.Parameters.Add("@Selected", SqlDbType.Bit)
              cmd.Parameters.Add("@ID", SqlDbType.Int)
              SaveNodes(TreeView1.Nodes, cmd)
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message & vbCrLf & ex.ToString, vbCritical)
        End Try
      End Sub
    
      Private Sub SaveNodes(nodes As TreeNodeCollection, cmd As SqlCommand)
        For Each n1 As MyTreenode In nodes
          cmd.Parameters("@Selected").Value = n1.Checked
          cmd.Parameters("@ID").Value = n1.ID
          cmd.ExecuteNonQuery()
          SaveNodes(n1.Nodes, cmd)
        Next
      End Sub
    
      Public Class MyTreenode
        Inherits TreeNode
        Public Property ID As Integer
        Public Property NodeID As Integer
        Public Property ParentID As Integer
      End Class
    
      Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
        Using cn As New SqlConnection(My.Settings.cnSQL)
          cn.Open()
          Using cmd As New SqlCommand With {.Connection = cn}
            ' delete previous table in SQL Server 2016 and above
            cmd.CommandText = "DROP TABLE IF EXISTS Table1;"
            ' delete previous table in previous versions of SQL server 2016
            'cmd.CommandText = "If OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE IF EXISTS Table1;"
            Debug.Print($"Return value Drop: {cmd.ExecuteNonQuery}")
            ' Cretae table
            cmd.CommandText = "CREATE TABLE Table1([ID] Integer Identity, [NodeName] nvarchar(50), [NodeID] int, [ParentID] int, [Selected] bit, CONSTRAINT [PK_Table1] PRIMARY KEY ([ID]));"
            Debug.Print($"Return value Create Table: {cmd.ExecuteNonQuery}")
            ' Insert records 
            cmd.CommandText = "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Root',1,0,0);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Items',2,1,1);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Stock In',3,1,0);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Stock Out',4,1,0);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Search Items',5,1,0);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Low Stock',6,1,0);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Expiry Stock',7,1,1);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Stock Journal',8,1,1);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Master',9,1,1);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Bulk Update Item',10,1,1);"
            cmd.CommandText &= "INSERT Table1([NodeName],[NodeID],[ParentID],[Selected]) VALUES('Print',11,1,1);"
            Debug.Print($"Return value Insert records: {cmd.ExecuteNonQuery}")
          End Using
        End Using
      End Sub
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Friday, May 22, 2020 7:31 AM
  • Hi Omi4u,

    How is the question going? If your question has been answered then please click the "Mark as Answer" Link at the bottom of the correct post(s), so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 25, 2020 1:17 AM