locked
Index for Insert in Datagridview tied to bindingsource after Delete RRS feed

  • Question

  • I have a datagridivew tied to a bindingsource where I need to insert rows at a specific index as well as remove specific rows. I'm using the following methods to do so:

    Remove Row:

    Table1BindingSource.RemoveCurrent()

    Insert Row<o:p></o:p>

    DataSet1.Tables("Table1").Rows.InsertAt(newRow,
    Table1BindingSource.Position())<o:p></o:p>

    This method works well until I try to insert a row at any position below a row that I deleted. When I attempt this, the row will be inserted one position (or several positons depending on how many rows were deleted) off.

    How do I get the row index to refresh after deleting a row so that subsequent rows inserted go into the desired position?

    Friday, September 1, 2017 6:23 PM

Answers

  • I tried the following and so far it seems to be working. If I take the primary key of the current row from the bindingsource behind the datagridview and use it to find the index of the row with the same primary key in the datatable, I can then return the index of that row on the datatable which is the correct index to insert the new line because it includes all the the rows marked for deletion.

    Dim pkRow As DataRow = DataSet1.Tables("Table1").Rows.Find(Table1BindingSource.Current("Table1ID"))

    Dim pkIndex As Integer = DataSet1.Tables("Table1").Rows.IndexOf(pkRow)

    • Marked as answer by Technobeam Thursday, October 12, 2017 7:53 PM
    Monday, October 2, 2017 8:42 PM

All replies

  • maybe call endedit after removing or store the bs.position in a variable and reset the binding, then insert at stored position

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Sunday, September 3, 2017 1:30 AM
  • How do I get the row index to refresh after deleting a row so that subsequent rows inserted go into the desired position?

    Hi Technobeam,

    According to your description, you want to refresh row index after deleting a row. If you update the BindSource after deleting a row, you also can insert row into the desired postion.

    Here is the delete row:

      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            
            Test11BindingSource.RemoveCurrent()
            Test11BindingSource.EndEdit()
            Test11TableAdapter.Update(Data3DataSet2.Test11)
    
               End Sub

    Here is the insert row:

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim newrow As DataRow = Data3DataSet2.Test11.NewRow()
            newrow("ID") = 4
            newrow("Location") = "GG"
            Data3DataSet2.Tables("Test11").Rows.InsertAt(newrow, 3)
            Test11BindingSource.EndEdit()
            Test11TableAdapter.Update(Data3DataSet2.Test11)
    
        End Sub

    Best Regards,

    Cherry


    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, September 4, 2017 3:30 AM
  • Cherry,

    Thanks for your reply. However, I do not want to write back to the database every time I insert or delete a row. I just need for rows I insert to be inserted at the desired position in the datagridview after items are deleted. Any other suggestions would be appreciated.

    Thanks

    Tuesday, September 5, 2017 4:07 PM
  • I'm still testing this but I think adding:

    Data3DataSet2.Test11.AcceptChanges() instead of Test11TableAdapter.Update(Data3DataSet2.Test11)

    Accomplishes my goal.  I'm going to continue to test this and report back.

    Tuesday, September 5, 2017 7:28 PM
  • Here is something that may or may not be of value with this issue. If there is a sort set on the data and when using the code shown, this could be a problem spot.

    I ran into this when writing a MSDN code sample. The following shows a method (which I know is not what you are looking for) for moving an existing row but the same would hold true possibly in your case. Lastly for you ignore the AcceptChanges.

    Public Module LanguageExtensions
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Sub MoveRowUp(ByVal sender As BindingSource)
            If Not String.IsNullOrWhiteSpace(sender.Sort) Then
                sender.Sort = ""
            End If
    
            Dim NewIndex As Int32 = CInt(IIf(sender.Position = 0, 0, sender.Position - 1))
            Dim dt = CType(sender.DataSource, DataTable)
            Dim RowToMove As DataRow = DirectCast(sender.Current, DataRowView).Row
            Dim NewRow As DataRow = dt.NewRow
    
            NewRow.ItemArray = RowToMove.ItemArray
            dt.Rows.RemoveAt(sender.Position)
            dt.Rows.InsertAt(NewRow, NewIndex)
            dt.AcceptChanges()
            sender.Position = NewIndex
    
        End Sub
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Sub MoveRowDown(ByVal sender As BindingSource)
            If Not String.IsNullOrWhiteSpace(sender.Sort) Then
                sender.Sort = ""
            End If
    
            Dim UpperLimit As Int32 = sender.Count - 1
            Dim NewIndex As Int32 = CInt(IIf(sender.Position + 1 >= UpperLimit, UpperLimit, sender.Position + 1))
            Dim dt = CType(sender.DataSource, DataTable)
            Dim RowToMove As DataRow = DirectCast(sender.Current, DataRowView).Row
            Dim NewRow As DataRow = dt.NewRow
    
            NewRow.ItemArray = RowToMove.ItemArray
            dt.Rows.RemoveAt(sender.Position)
            dt.Rows.InsertAt(NewRow, NewIndex)
            dt.AcceptChanges()
            sender.Position = NewIndex
    
        End Sub
    End Module
    If the sort check were to be removed and my code samples executed there would be some awkward moves/inserts.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 5, 2017 8:03 PM
  • I tried the code suggested by Cherry using AcceptChanges instead of Tableadapter.Update. while this ensured the position of the items being inserted was correct, it created a new problem. The AcceptChanges command cleared the status of all the items in the table. Therefore, when I called TableAdapterManager.UpdateAll, the Update command was being called for new rows instead of insert.

    I then tried removing the AcceptChanges command and adding Karen's suggestion of clearing the sort of the bindingsource prior to inserting or deleting a line using the code below. This resulted in my original problem resurfacing. Again, any inserts I made with an index below a row I deleted was inserted at a position offset by the number of rows deleted above the insertion point.

    Any additional help would be greatly appreciated.

    If Not String.IsNullOrWhiteSpace(sender.Sort) Then sender.Sort = ""
           
    End If

    Thursday, September 7, 2017 8:57 PM
  • In regards to removing the sort, if you have a sort and insert a row then the insert is not guaranteed to be placed where you intended as the insert must adhere to the sort, not the ordinal position displayed. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 7, 2017 9:10 PM
  • I'm still struggling with this. The only additional information I've confirmed is that the datagridview is updating it's index as I insert and delete. However, I don't think the underlying datasource is aware of these changes.
    Monday, September 11, 2017 9:11 PM
  • Something I have not tried but might be worth considering.

    Before the AcceptChange use GetChanges for the DataTable for inserts where GetChanges will return a DataTable with changed rows. Later when needed iterate the rows from GetChanges, find the row in the current DataTable and issue Row.SetModified() or Row.SetAdded() where Row is a DataRow.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, September 11, 2017 9:45 PM
  • So I just did a quickie here without any issues.

    Public Class Form1
        Private Sub DepartmentsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles DepartmentsBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.DepartmentsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.ForumExamplesDataSet)
    
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'ForumExamplesDataSet.Departments' table. You can move, or remove it, as needed.
            Me.DepartmentsTableAdapter.Fill(Me.ForumExamplesDataSet.Departments)
    
        End Sub
        Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
            If DepartmentsBindingSource.Current IsNot Nothing Then
                Dim postion As Integer = DepartmentsBindingSource.Position
                DepartmentsBindingSource.RemoveCurrent()
                Dim dt As DataTable = CType(DepartmentsBindingSource.DataSource, ForumExamplesDataSet).Tables(0)
                Dim newRow As DataRow = dt.NewRow
                newRow("Name") = "Plastic surgery"
                dt.Rows.InsertAt(newRow, postion)
            End If
        End Sub
    End Class

    The first image is prior to removal and add while the second is after remove/add. Note the -1, that is because I have not done the save.

    After the save (note the high id value)

    That was my bad, let's try that again, first I will execute these statements

    DELETE FROM dbo.Departments WHERE id > 9
    DBCC CHECKIDENT ('[Departments]', RESEED, 9);

    Run the above code again.

    That's more like it :-)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, September 11, 2017 11:39 PM
  • Karen,

    Thanks for working through this with me. However your example is missing one piece that is my main problem. You insert the new line at the same position as the old one. The problem is when you insert a new line below the line you deleted. If you use bindingsource.removecurrent() or dataset.table("tablename").Rows(Bindingsource.Position()).Delete(), the rows in the underlying table are not deleted until the tableadapter is updated and the delete command is run on the database. Until then, the rows are still in the underlying table but their datarow.rowstate is deleted. This is exactly how this should work. However, since the datagridview is now displaying less rows then the datatable, when you use the index from the bindingsource or the datagridivew as a reference to determine the insertion point when running the datatable.rows.insertat() command, you will always be off by the number of rows you have deleted above the positon you'd like to insert a new row until you save your data back to the database. to demonstate this using your example, after deleting line 4 - Microbiology, do not immediately insert the new row. Create a new button, move your cursor posiont to further down the list, past row 4 and create a new button for the insert with the following code:

               Dim postion As Integer = DepartmentsBindingSource.Position
               
    Dim dt As DataTable = CType(DepartmentsBindingSource.DataSource, ForumExamplesDataSet).Tables(0)
               
    Dim newRow As DataRow = dt.NewRow
                newRow
    ("Name") = "Plastic surgery"
                dt
    .Rows.InsertAt(newRow, postion)

    Thanks again for your help. This has been troubling me for quote some time and a resolution would be greatly appreciated.

    Tuesday, September 12, 2017 8:59 PM
  • Hi Technobeam,

    According to your description, I am not sure that you have resolved your issue or not, if yes, please remember to close your thread by marking the helpful post as answer, if not, please describe the problem you have currently.

    Thanks for your understanding.

    Best Regards,

    Cherry


    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.

    Wednesday, September 13, 2017 9:01 AM
  • Cherry,

    This did not solve my problem. My problem still exists when I try to insert a new line at a position below where I deleted a line. My reply from yesterday describes this more. If you can provide any assistance, that would be great.

    Thanks,

    Joe

    Wednesday, September 13, 2017 7:20 PM
  • No disagreement on what you said about deleted records and their row state, matter of fact I wrote a MSDN code sample (it's in C#) that allows you to get all kinds of information about changes to a DataTable including deleted rows (and most importantly their indexes). You can also get at this information via DataTable events (as in my MSDN code sample in vb.net).

    I thought about what might be a solution yet all I came up with (in my head) was doable but complex in nature. Part of it relies on have a container to "remember" information needed to get something going which would include say DataGridView row index, BindingSource Position and the primary key for the deleted records and more. I only started it as I've been rather busy. So the following (conceptual but does get information) uses RowDeleted event for the DataTable to get indexes, that is it as my time is short.

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'ForumExamplesDataSet.Departments' table. You can move, or remove it, as needed.
        Me.DepartmentsTableAdapter.Fill(Me.ForumExamplesDataSet.Departments)
        Dim dt As DataTable = CType(DepartmentsBindingSource.DataSource, ForumExamplesDataSet).Tables(0)
        AddHandler dt.RowDeleted, AddressOf _RowDeleted
    End Sub
    
    Private Sub _RowDeleted(sender As Object, e As DataRowChangeEventArgs)
        Console.WriteLine($"Pos: {DepartmentsBindingSource.Position} - id: {e.Row("id", DataRowVersion.Original)} dgv: {DepartmentsDataGridView.CurrentRow.Index}")
    End Sub
    
    The above is a modified version of my last code sample provided here. So it's not a solution but possibly might provoke an idea.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, September 13, 2017 11:19 PM
  • If I could find a way to count all the rows in the data table that are above the current row, I could use that as my insertion point. However, im not sure if this is possible as the data table itself does not have a row index. It surprises me that this is turning out to be so complex. It seems like a very common feature that would be useful on many applications. Do you have any more details on what you started to suggest?
    Tuesday, September 19, 2017 2:13 AM
  • Cherry,

    This did not solve my problem. My problem still exists when I try to insert a new line at a position below where I deleted a line. My reply from yesterday describes this more. If you can provide any assistance, that would be great.

    Thanks,

    Joe

    Hi Technobeam,

    You said that you want to insert a new line at a position below where you delete a line or many line. For example, you delete 2,3,4,5 row in the datagridview, the you want to insert a new line at 6 index, am I right?

    Best Regards,

    Cherry


    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, September 22, 2017 8:30 AM
  • I am going to throw a hail marry here... you could do some simple math from this and insert the row where ever you want

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click If Dt.Rows.Count <= 0 Then With Dt .Columns.Add("Col1", GetType(String)) .Columns.Add("Col2", GetType(String)) For i As Int32 = 0 To 1000 .Rows.Add("Col1Val" & i, "Col2Val" & i) Next End With End If BS1.DataSource = Dt dgv_t1.DataSource = BS1 End Sub

    'remove/insert' Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click With BS1 Dim CurrPos As Integer = BS1.Position .RemoveCurrent() .EndEdit() With Dt Dim MyRow As DataRow = Dt.NewRow .Rows.InsertAt(MyRow, CurrPos) End With End With End Sub



    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Saturday, September 23, 2017 3:31 AM
  • Cherry,

    That is correct. However, when you delete 2,3,4,5 the dataTable still has these rows, but are marked for deletion while the datagridview does not have them. Therefore, if I use the index of the datagridview as my position to insert the new row, I'd be using 2 as my rowindex and when you insert at that position in the datatable, you end up inserting the row in 4 positions higher then you intended.

    Monday, October 2, 2017 6:46 PM
  • I tried the following and so far it seems to be working. If I take the primary key of the current row from the bindingsource behind the datagridview and use it to find the index of the row with the same primary key in the datatable, I can then return the index of that row on the datatable which is the correct index to insert the new line because it includes all the the rows marked for deletion.

    Dim pkRow As DataRow = DataSet1.Tables("Table1").Rows.Find(Table1BindingSource.Current("Table1ID"))

    Dim pkIndex As Integer = DataSet1.Tables("Table1").Rows.IndexOf(pkRow)

    • Marked as answer by Technobeam Thursday, October 12, 2017 7:53 PM
    Monday, October 2, 2017 8:42 PM