Update changes against SQL Server database which reflects changes in TreeView

Posted on 2011-09-06
Last Modified: 2013-11-26
Dear experts,
Please find below VB Code (windows application) I use to populte TreeView from SQL Server database .Also drag and drop functionality is introduced which works ok...
In terms to save changes against database after treenode was draged & droped within treeview (position of treenode(s) was changed and new parent id should be asigned according to position in treeview) I used dataadapter update method but unfortunatelly no changes were reflected in SQL Server database table...I have no idea why :(
Could you help me in order to make this working? This should be easy as it is for DataGridView but for TreeView I really don't know why is this so hard... Table structure (HIERARCHY TABLE) in SQL Server is as follow:
uid     iParentId    sName    Where uid is PrimaryKey column....

If someone has solved this problem before I would be more than greatfull in order to help me solve mine...Also, please feel free to do amendments to my VB Code if needed to help me with the issue....
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Data.SqlTypes

Public Class Form1
    Inherits System.Windows.Forms.Form
    Private WithEvents cn As New SqlConnection(My.Settings.MyCon)
    Private WithEvents ds As New DataSet
    Private WithEvents da As New SqlClient.SqlDataAdapter
    Private WithEvents daSelectCommand As New SqlCommand
    Private WithEvents daUpdateCmd As SqlCommand
    Private WithEvents builder As SqlCommandBuilder = New SqlCommandBuilder(da)

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

        da.SelectCommand = daSelectCommand
        da.SelectCommand.Connection = cn
        da.SelectCommand.CommandText = "SELECT * FROM HIERARCHIES"

        da.Fill(ds, "dtSP")

        ' Without the SqlCommandBuilder this line would fail.
        da.Update(ds, "dtSP")

        ' Kreiranje relacije izmedju objekata "Parent to Child" u tabeli iz koje citamo podatke

        ds.Relations.Add("Parenttochild", ds.Tables("dtSP").Columns("ID"), ds.Tables("dtSP").Columns("ParentId"), False)


    End Sub
    Private Sub LoadTreeView(ByVal ds As DataSet)
        Dim oTreeView As TreeView = New TreeView()
        Dim oDataRow As DataRow
        oTreeView = Me.TreeView1

        For Each oDataRow In DS.Tables("dtSP").Rows
            ' pronalazi Root node, node koji ima vrijednost NULL
            If oDataRow("ParentId") Is DBNull.Value Then '= "No_Reports" Then

                Dim oNode As New System.Windows.Forms.TreeNode
                oNode.Tag() = oDataRow("Id")
                oNode.Text = oDataRow("ID_Description")

                'Popuni od definisanog roota
                RecursivelyLoadTree(oDataRow, oNode)
            End If
        Next oDataRow

    End Sub

    Private Sub RecursivelyLoadTree(ByVal oDataRow As DataRow, _
   ByRef oNode As TreeNode)
        Dim oChildRow As DataRow
        ' generise niz redova u kojima se nalaze childs
        For Each oChildRow In oDataRow.GetChildRows("Parenttochild")
            'Kreiraj Child node i dodaje ga pripadajucem parentu
            Dim oChildNode As New TreeNode()
            oChildNode.Tag = oChildRow("Id")
            oChildNode.Text = oChildRow("ID_Description")
            'Ponavljamo za svaki child
            RecursivelyLoadTree(oChildRow, oChildNode)
        Next oChildRow
    End Sub

    Public Sub TreeView1_ItemDrag(ByVal sender As System.Object, _
     ByVal e As System.Windows.Forms.ItemDragEventArgs) _
     Handles TreeView1.ItemDrag

        'Set the drag node and initiate the DragDrop 
        DoDragDrop(e.Item, DragDropEffects.Move)

    End Sub

    Public Sub TreeView1_DragEnter(ByVal sender As System.Object, _
        ByVal e As System.Windows.Forms.DragEventArgs) _
        Handles TreeView1.DragEnter

        'See if there is a TreeNode being dragged
        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
            True) Then
            'TreeNode found allow move effect
            e.Effect = DragDropEffects.Move
            'No TreeNode found, prevent move
            e.Effect = DragDropEffects.None
        End If

    End Sub

    Public Sub TreeView1_DragOver(ByVal sender As System.Object, _
        ByVal e As DragEventArgs) Handles TreeView1.DragOver

        'Check that there is a TreeNode being dragged 
        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
               True) = False Then Exit Sub

        'Get the TreeView raising the event (incase multiple on form)
        Dim selectedTreeview As TreeView = CType(sender, TreeView)

        'As the mouse moves over nodes, provide feedback to 
        'the user by highlighting the node that is the 
        'current drop target
        Dim pt As Point = _
            CType(sender, TreeView).PointToClient(New Point(e.X, e.Y))
        Dim targetNode As TreeNode = selectedTreeview.GetNodeAt(pt)

        'See if the targetNode is currently selected, 
        'if so no need to validate again
        If Not (selectedTreeview.SelectedNode Is targetNode) Then
            'Select the    node currently under the cursor
            selectedTreeview.SelectedNode = targetNode

            'Check that the selected node is not the dropNode and
            'also that it is not a child of the dropNode and 
            'therefore an invalid target
            Dim dropNode As TreeNode = _
                CType(e.Data.GetData("System.Windows.Forms.TreeNode"),  _

            Do Until targetNode Is Nothing
                If targetNode Is dropNode Then
                    e.Effect = DragDropEffects.None
                    Exit Sub
                End If
                targetNode = targetNode.Parent
        End If

        'Currently selected node is a suitable target
        e.Effect = DragDropEffects.Move
        ' End If

    End Sub

    Public Sub TreeView1_DragDrop(ByVal sender As System.Object, _
        ByVal e As System.Windows.Forms.DragEventArgs) _
        Handles TreeView1.DragDrop

        'Check that there is a TreeNode being dragged
        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
              True) = False Then Exit Sub

        'Get the TreeView raising the event (incase multiple on form)
        Dim selectedTreeview As TreeView = CType(sender, TreeView)

        'Get the TreeNode being dragged
        Dim dropNode As TreeNode = _
              CType(e.Data.GetData("System.Windows.Forms.TreeNode"),  _

        'The target node should be selected from the DragOver event
        Dim targetNode As TreeNode = selectedTreeview.SelectedNode

        'Remove the drop node from its current location

        'If there is no targetNode add dropNode to the bottom of
        'the TreeView root nodes, otherwise add it to the end of
        'the dropNode child nodes
        If targetNode Is Nothing Then
        End If

        'Ensure the newley created node is visible to
        'the user and select it
        selectedTreeview.SelectedNode = dropNode

    End Sub

    Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
    End Sub
End Class

Open in new window

Question by:alsam
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 13

Accepted Solution

gamarrojgq earned 500 total points
ID: 36497780

According to your code, you are not making any change to the DS.Tables("dtSP"), you are just allowing the user to move the tree nodes on memory, but since the DS.Tables("dtSP") is not bounded to the Treeview it will remains the same, since nothing is actually change on it, therefore when you call the Update method of you DataAdapter nothing chances on the SQL Server Database Table.

You should add some code in the TreeView1_DragDrop event, and look for the row that represents the node that is been dropped and change its ParentId, so when you update the databable the changes will be propagate to the SQL Server Database Table

Hope that Helps

Author Closing Comment

ID: 36519351
thank you for your was so obvious that I don't make any changes...
Thanks to you, now i make changes in dataset itself so changes are reflected by calling update method....
Thank you for your help...for sure this is 500 for you

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question