Solved

Update changes against SQL Server database which reflects changes in TreeView

Posted on 2011-09-06
2
342 Views
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
        TreeView1.BeginUpdate()
        TreeView1.Nodes.Clear()

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



        da.Fill(ds, "dtSP")
        cn.Close()
        builder.GetInsertCommand()
        builder.GetDeleteCommand()
        builder.GetUpdateCommand()

        ' 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)


        LoadTreeView(ds)
        TreeView1.ExpandAll()


        TreeView1.EndUpdate()
    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")
                oTreeView.Nodes.Add(oNode)

                'Popuni od definisanog roota
                RecursivelyLoadTree(oDataRow, oNode)
            End If
        Next oDataRow
        Controls.Add(oTreeView)

    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")
            oNode.Nodes.Add(oChildNode)
            '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
        Else
            '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"),  _
                TreeNode)

            Do Until targetNode Is Nothing
                If targetNode Is dropNode Then
                    e.Effect = DragDropEffects.None
                    Exit Sub
                End If
                targetNode = targetNode.Parent
            Loop
        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"),  _
              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
        dropNode.Remove()

        '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
            selectedTreeview.Nodes.Add(dropNode)
        Else
            targetNode.Nodes.Add(dropNode)
        End If

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

    End Sub

    Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
        da.Update(ds.Tables("dtSP"))
        MsgBox("Saved")
    End Sub
End Class

Open in new window

0
Comment
Question by:alsam
2 Comments
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 36497780
Hi,

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
0
 
LVL 1

Author Closing Comment

by:alsam
ID: 36519351
Hi,
thank you for your advice...it 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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now