Drag & drop, Treeview, save changes in SQL server database

Hi,
in attached code I paralley fill my grid and my treeview by calling Button1_Click with values from the same table on SQL Server.
After I change the value in my grid and call Button2_Click SQL server database is updated according to changes made in grid (consequently structure of treeview is updated).
But when I do drag and drop in treeview (which visualy works OK on Form) and call Button2_Click then no data has been updated in SQL Server database. I think that problem is in Public Sub TreeView1_DragDrop but I'm not able to define what actually..
Can someone help me to review if everithing is OK with the same and why is this happening.
My table structure in SQL server looks like this (ID is primary key):
ID      float      
TCID_name      nvarchar(MAX)      
Reports      float

Thank you...      
Imports System.Data.SqlClient


Public Class Form3
    Inherits System.Windows.Forms.Form

    Private con As New SqlConnection()
    Private WithEvents cmdSelect As New SqlCommand()
    Private WithEvents cmdUpdate As New SqlCommand()
    Private WithEvents cmdInsert As New SqlCommand()
    Private WithEvents cmdDelete As New SqlCommand()


    Private WithEvents da As New SqlDataAdapter()
    Private WithEvents ds As New DataSet()
    Private WithEvents cb As New SqlCommandBuilder(da)
    Dim CTree As New CTreeView()
    Dim m_tnSource As TreeNode

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        con.ConnectionString = "Data Source=S_A-PC\SQLEXPRESS1;Initial Catalog=AdventureWorks;Integrated Security=True"
        con.Open()

        Dim dg As New DataGridView
        da.SelectCommand = cmdSelect
        da.SelectCommand.CommandText = "SELECT * FROM CostC223"
        da.SelectCommand.Connection = con

        da.Fill(ds, "dtCosts1")
        Me.Grid1.SetDataBinding(ds, "dtCosts1")
        ''''''''''''''''''''''''''''''''''''''''
        da.UpdateCommand = cb.GetUpdateCommand()
        da.InsertCommand = cb.GetInsertCommand()
        da.DeleteCommand = cb.GetDeleteCommand()
        ''''''''''''''''''''''''''''''''''''''''
        Call Rootnode()

    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd.Click
        da.Update(ds.Tables("dtCosts1"))
        con.Close()
    End Sub

    Private Sub Rootnode()
        TreeView1.Nodes.Clear()
        ds.Relations.Add("Parenttochild", ds.Tables("dtCosts1").Columns("ID"), ds.Tables("dtCosts1").Columns("Reports"), False)
        LoadTreeView(ds)
        TreeView1.CollapseAll()

    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("dtCosts1").Rows

            If oDataRow("Reports") = -1 Then
                Dim oNode As New System.Windows.Forms.TreeNode
                oNode.Tag() = oDataRow("ID")
                oNode.Text = oDataRow("ID") & (" " + oDataRow("TCID_name").ToString())
                oTreeView.Nodes.Add(oNode)


                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

        For Each oChildRow In oDataRow.GetChildRows("Parenttochild")

            Dim oChildNode As New TreeNode()
            oChildNode.Tag = oChildRow("ID")
            oChildNode.Text = oChildRow("ID") & (" " + oChildRow("TCID_name").ToString())
            oNode.Nodes.Add(oChildNode)

            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

        m_tnSource = CType(e.Item, TreeNode)
        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


        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
            True) Then

            e.Effect = DragDropEffects.Move
        Else

            e.Effect = DragDropEffects.None
        End If

    End Sub

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


        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
               True) = False Then Exit Sub


        Dim selectedTreeview As TreeView = CType(sender, TreeView)


        Dim pt As Point = _
            CType(sender, TreeView).PointToClient(New Point(e.X, e.Y))
        Dim targetNode As TreeNode = selectedTreeview.GetNodeAt(pt)


        If Not (selectedTreeview.SelectedNode Is targetNode) Then

            selectedTreeview.SelectedNode = targetNode


            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


        e.Effect = DragDropEffects.Move


    End Sub

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

        Dim pt As Point
        Dim tnDestination As TreeNode
        Dim tnNew As TreeNode
        Dim tnSourceParent As TreeNode

        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", True) Then


            pt = TreeView1.PointToClient(New Point(e.X, e.Y))
            tnDestination = TreeView1.GetNodeAt(pt)

            If CTree.IsDropAllowed(m_tnSource, tnDestination) = True Then
                tnSourceParent = m_tnSource.Parent

                tnNew = CType(e.Data.GetData("System.Windows.Forms.TreeNode"), TreeNode)
                tnDestination.Nodes.Add(CType(tnNew.Clone, TreeNode))
                tnDestination.ExpandAll()
                tnNew.Remove()

            Else
                
                MsgBox("The dragged item cannot be dropped here because" & _
                       "doing so will create a circular reference.", _
                       MsgBoxStyle.Exclamation)
            End If

        End If

    End Sub
End Class

Open in new window

LVL 1
alsamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
In line 170, you are passing the string "System.Windows.Forms.TreeNode". This is incorrect. Use one of the constants defined in System.Windows.Forms.DataFormats:

    e.g.
    System.Windows.Forms.DataFormats.Bitmap
    System.Windows.Forms.DataFormats.Text
    System.Windows.Forms.DataFormats.FileDrop
    etc.
käµfm³d 👽Commented:
P.S.

Theses constants describe the type of data you expect to be "inside" the drag.
alsamAuthor Commented:
Thank you...but since I don't have much expiriance with this drag and drop I would appreciate your help... This seems like crucial thing in making this working but ...
What should I be aware in assigning apropriate constants....is it my table structure or what....
Can you please provide me with your opinion/ maybe if you have some time to adjust my code would be great thing for me...you would help me a lot....
I'm not the person who relly on other people help a much but this is very important for me and for that reason I ask you once more to give me your full attention in order to solve this...
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

käµfm³d 👽Commented:
What are you dragging into the TreeView? Is it a file? A text string? An image? Etc.
alsamAuthor Commented:
TreeView is populated with data from table from SQL Server database....
I don't drag anything into the treeview but within the treeview.....
Basically, by dragging of nodes in TreeView I only changing position of my node and at the same time assigning new parent to the draged node....for example
Let say that my node id is 12345678 XY. Same is contained of ID and TCID_name filelds form table. This my node has parent to whom reports (column "Reports") like 1 - Product.
After drag and drop I moved my node (12345678 XY) to another position within the same treeview and assigning a new parent depending of position of dragdrop. It means that after drag n drop my node does not reports to 1-Product any more but to different parent for example 2 - Products2. Accordignaly, column "Reports" should be updated in table on SQL serv. db after I call da.Update method

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alsamAuthor Commented:
Good reference for further developing
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.