Drag and Drop on a DataGridView Bound to a Filtered BindingSource

Help please - this is driving me nuts.
I am trying to implement Drag and Drop Functionality on a Datagridview that is bound to a Filtered BindingSource (Me.QuoteLinesBindingSource.Filter = "QuoteID = " & id) and the data table has an AutoIncrement ID.
I have not specified a sortorder for the Grid...so it appears to display in the ID order.

By Googling I found some code - but it doesnt seem to work for a Filtered List?
So I have modified it to try looking for the row in my Bindingsource.
When running ... my row moves (mostly but not always to the correct place)
But after Saving the data (QuoteLinesBindingSource.EndEdit() and QuoteLinesTableAdapter.Update(HEATPRODataSet1.QuoteLines))
I can see that the row I tried to move is still where it was....and a copy of the row shows as the last grid row.

I suspect the AutoIncrement may be the cause of at least part of my problem - as the moved row is added to the bottom of the Database Table - I am considering removing the ID as it is not really used.

My code is as folows..

Code:

---

Dim dragBoxFromMouseDown As Rectangle
Dim rowIndexFromMouseDown, rowIndexOfItemUnderMouseToDrop As Integer

Me.QuoteLinesDataGridView.AllowDrop = True ' Enable Drag and Drop

Private Sub QuoteLinesDataGridView_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles QuoteLinesDataGridView.DragDrop
    Try
        Dim clientPoint As Point = QuoteLinesDataGridView.PointToClient(New Point(e.X, e.Y))
        rowIndexOfItemUnderMouseToDrop = QuoteLinesDataGridView.HitTest(clientPoint.X, clientPoint.Y).RowIndex
        If e.Effect = DragDropEffects.Move Then
            Dim GridrowToMove As DataGridViewRow = CType(e.Data.GetData(GetType(DataGridViewRow)), DataGridViewRow)
            Dim DataRowToMove As DataRow = CType(GridrowToMove.DataBoundItem, DataRowView).Row
            Dim row As DataRow = HEATPRODataSet1.QuoteLines.NewRow()
            row.ItemArray = DataRowToMove.ItemArray
            HEATPRODataSet1.QuoteLines.Rows.Remove(DataRowToMove) ' remove old row - Changes on Grid ok - But not in Database after Save
            HEATPRODataSet1.QuoteLines.Rows.InsertAt(row, rowIndexOfItemUnderMouseToDrop) ' insert new row
            '****Also tried the following and got the same results****
          '****HEATPRODataSet1.Tables("QuoteLines").Rows.Remove(DataRowToMove)
          '****HEATPRODataSet1.Tables("QuoteLines").Rows.InsertAt(row, rowIndexOfItemUnderMouseToDrop)
        End If
    Catch ex As System.Exception
        MessageBox.Show("Drag and Drop Error" & vbCrLf & ex.ToString)
    End Try
End Sub

Private Sub QuoteLinesDataGridView_DragOver(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles QuoteLinesDataGridView.DragOver
    e.Effect = DragDropEffects.Move
End Sub

Private Sub QuoteLinesDataGridView_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles QuoteLinesDataGridView.MouseDown
    rowIndexFromMouseDown = QuoteLinesDataGridView.HitTest(e.X, e.Y).RowIndex
    If rowIndexFromMouseDown <> -1 Then
        Dim dragSize As Size = SystemInformation.DragSize
        dragBoxFromMouseDown = New Rectangle(New Point(CInt(e.X - (dragSize.Width / 2)), CInt(e.Y - (dragSize.Height / 2))), dragSize)
    Else
        dragBoxFromMouseDown = Rectangle.Empty
    End If
End Sub

Private Sub QuoteLinesDataGridView_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles QuoteLinesDataGridView.MouseMove
    If e.Button = Windows.Forms.MouseButtons.Left Then
        If dragBoxFromMouseDown <> Rectangle.Empty And dragBoxFromMouseDown.Contains(e.X, e.Y) Then
            Dim dropEffect As DragDropEffects = QuoteLinesDataGridView.DoDragDrop(QuoteLinesDataGridView.Rows(rowIndexFromMouseDown), DragDropEffects.Move)
        End If
    End If
End Sub

---

Consider this. Let's say that you have a database table with 20 records and you query the database and 10 of those records are retrieved. If you then saved changes from your DataTable back to the database, would you expect the 10 rows you didn't retrieve to be deleted? I would expect not. That's basically waht you're suggesting should happen with your code though. Here:

vb.net Code:

HEATPRODataSet1.QuoteLines.Rows.Remove(DataRowToMove)

you are removing a DataRow from the DataTable. That results in the same state as if you never retrieved that record in the first place. I think that we've already established that we wouldn't want such a record deleted.

What you need to do is call the Delete method of the DataRow itself. Doing so will leave the DataRow in the DataTable but set its RowState to Deleted. That's what flags it for deletion in the database when you save changes, just as RowStates of Added and Modified flag rows for insertion and updating. Setting the RowState to Deleted will cause it to be filtered out by default by the DefaultView of the DataTable, so it will disappear from your grid, just as you'd expect.

Thanks jmcilhinney - that makes sense.
I have changed my Row Deletion to DataRowToMove.Delete() and the row being moved is now deleted.

Re My new Row being saved to the End of the Datatable - so does not appear in the correct position when being redisplayed
Is that because the table has an AutoIncrement on the ID?
If I didnt have the Autoincrement would it save and then redisplay where placed?

You can sort data any way you want. Records are usually going to be returned by a database in the order that they were added or by their primary key by default. If you have an autoincremented ID then the two will result in the same order. If you want your records to be accessible in a specific order that is not related to any of the data, then your best bet is to add a column specifically for sort order. You can then set numercal values in that column and sort by them in a query or in your UI.

That actually makes moving rows in a DataGridView easier. You simply bind your DataTable to a BindingSource and bind that to the grid, with the Sort property of the BindingSource set to the name of that column. You can then simply swap the values in that column for two rows and the BindingSource will take care of moving the grid rows. What you might like to do to make moving a single row easier is to multiply every value by 2, thus leaving a gap between each pair of rows. To insert a row between two others, you simply set its value in the sort column between those for the other two rows. You can then renumber every row to establish the gaps again, with that being transparent to the user.