datagridview last programmatically changed cell does not get included in changedrows

Question

VB.net app changes values in a datagridview programmatically. The values are all as they should be, but the save routine (dtShipments is the datatable that is the source for the datagridview) Dim dtChanges As DataTable = dtShipments.getchanges() If more than one has changed, dtChanges is always missing the last row.

In the routine that changes the cell values, I have tried DatagridView1.EndEdit and DatagridView1.CommitEdit, but the behavior is the same. I even tried adding a SendKeys.Send(vbTab) line, since hitting the tab key when making the changes manually is enough to get all the changes to show up in .GetChanges.

What am I missing?

code per request:

Private Sub btnAssign_Click(sender As Object, e As EventArgs) Handles btnAssign.Click
        strModErr = "Form1_btnAssign_Click"
        Dim sTruck As String = ""
        Try
            sTruck = Me.DataGridView2.SelectedCells(0).Value.ToString
            For Each row As DataGridViewRow In DataGridView1.SelectedRows
                row.Cells("Truck").Value = sTruck                
            Next
            DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
        Catch ex As Exception
            WriteErrorToLog(Err.Number, strModErr + " - " + Err.Description)
        End Try
    End Sub
Private Function SaveChanges() As Boolean
        strModErr = "Form1_SaveChanges"
        Dim Conn As New SqlConnection(My.Settings.SQLConnectionString)
        Dim sSQL As String = "UPDATE fs_Shipments SET Truck = @Truck, Stop = @Stop WHERE SalesOrder = @SO"
        Dim cmd As New SqlCommand(sSQL, Conn)
        Dim sSO, sTruck As String
        Dim iStop As Integer = 0
        Try
            DataGridView1.EndEdit()
            DataGridView1.ClearSelection()
            Dim dtChanges As DataTable = dtShipments.getchanges()       'DataRowState.Modified

            If Not dtChanges Is Nothing Then
                Conn.Open()

                For Each row As DataRow In dtChanges.Rows
                    sSO = row("SalesOrder").ToString
                    sTruck = row("Truck").ToString
                    iStop = CInt(row("Stop").ToString)
                    With cmd.Parameters
                        .Clear()
                        .AddWithValue("@SO", sSO)
                        .AddWithValue("@Truck", sTruck)
                        .AddWithValue("@Stop", iStop)
                    End With
                    cmd.ExecuteNonQuery()
                Next
            End If
            Return True
        Catch ex As Exception
            WriteErrorToLog(Err.Number, strModErr + " - " + Err.Description)            
            Return False
        End Try
    End Function

Answer 1

I am not exactly 100% sure why this happens. The problem appears to be specific to when the user “selects” the cells in the first grid, and then calls the SaveChanges code “BEFORE” the user has made another selection in the first grid. In other words, if the user “selects” the rows to “assign” the truck to in grid 1, then, “AFTER” the “selected” cells have been filled with the selected truck, THEN, the user selects some other cell in grid 1, THEN calls the save changes code. In that case the code works as expected.

All my attempts at committing the changes, either failed or introduced other issues. I am confident a lot of this has to do with the grids SelectedRows collection. IMHO, this looks like a risky way to set the cell values, I would think a more user-friendly approach may be to add a check box on each row and assign the truck values to the rows that are checked. But this is an opinion.

Anyway, after multiple attempts, the solution I came up with only further demonstrates why using a BindingSource is useful in many ways. In this case, it appears the DataTable is not getting updated with the last cell change. Again, I am not sure “why” this is, however since it appears to work using a BindingSource, I can only assume it has something to do with the DataTable itself. In other words, before the “Save” code is executed, we could call the tables AcceptChanges method, but then we would lose those changes. So that is not an option.

To help, below is a full (no-fluff) example. In the future, I highly recommend you pull out the parts of the code that do NOT pertain to the question. Example, all the code that saves the changes to the DB is superfluous in relation to the question… so remove it. The more unnecessary code you add to your question only increases the number of SO users that will “ignore” the question. If you post minimal, complete and working code that reproduces the problem so that users can simply copy and paste without having to add addition code or remove unnecessary code will increase you chances of getting a good answer. Just a heads up for the future.

The solution below simply adds a BindingSource. The BindingSource’s DataSource is the DataTable dtShipments then the BindingSource is used a DataSource to DataGridView1. Then in the SaveChanges method, “before” the code calls the dtShipment.GetChanges() method, we will call the BindingSources.ResetBinding() method which should complete the edits to the underlying data source, in this case the DataTable dtShipments.

If you create a new VS-VB winforms solution, drop two (2) grids and two (2) buttons onto the form as shown below, then change the button names to “btnAssign” and “btnApplyChanges.” The assign button will add the selected truck in grid two to the selected rows in grid one. The apply changes button simply resets the binding source and displays a message box with the number of rows that were changed in the dtShipments DataTable. It should be noted, that the code calls the dtShipments.AcceptChanges() method to clear the changes. Otherwise, the code will update changes that have already been made.

Dim dtShipments As DataTable
Dim dtTrucks As DataTable
Dim shipBS As BindingSource

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  dtShipments = GetShipmentsDT()
  shipBS = New BindingSource()
  shipBS.DataSource = dtShipments
  dtTrucks = GetTrucksDT()
  dtShipments.AcceptChanges()
  DataGridView1.DataSource = shipBS
  DataGridView2.DataSource = dtTrucks
End Sub

Private Function GetShipmentsDT() As DataTable
  Dim dt = New DataTable()
  dt.Columns.Add("ID", GetType(String))
  dt.Columns.Add("Truck", GetType(String))
  dt.Rows.Add(1)
  dt.Rows.Add(2)
  dt.Rows.Add(3)
  dt.Rows.Add(4)
  Return dt
End Function

Private Function GetTrucksDT() As DataTable
  Dim dt = New DataTable()
  dt.Columns.Add("Truck", GetType(String))
  For index = 1 To 10
    dt.Rows.Add("Truck" + index.ToString())
  Next
  Return dt
End Function

Private Sub btnAssign_Click(sender As Object, e As EventArgs) Handles btnAssign.Click
  Dim sTruck = DataGridView2.SelectedCells(0).Value.ToString
  'Dim drv As DataRowView
  For Each row As DataGridViewRow In DataGridView1.SelectedRows
    'drv = row.DataBoundItem
    'drv("Truck") = sTruck
    row.Cells("Truck").Value = sTruck
  Next
  'DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
  'shipBS.ResetBindings(True)
  'DataGridView1.CurrentCell = DataGridView1.Rows(0).Cells(0)
End Sub

Private Function SaveChanges() As Boolean
  Try
    shipBS.ResetBindings(True)
    Dim dtChanges As DataTable = dtShipments.GetChanges()
    If (dtChanges IsNot Nothing) Then
      MessageBox.Show("There are " & dtChanges.Rows.Count & " rows changed in the data table")
      ' update sql DB
      dtShipments.AcceptChanges()
    End If
    Return True
  Catch ex As Exception
    Return False
  End Try
End Function

Private Sub btnApplyChanges_Click(sender As Object, e As EventArgs) Handles btnApplyChanges.Click
  Dim ChangesMade As Boolean
  ChangesMade = SaveChanges()
End Sub

Lastly, since you are using VB… I highly recommend that you set the “Strict” option to “ON.” When this option is ON, it will flag possible errors that you may be missing. To do this for all future VB solutions, open VS, close any solutions that may be open, then go to Tools->Options->Projects and Solutions->VB Defaults, then set “Option Strict” to “On.” The default setting is off.

I hope this makes sense and helps.