DataGridView in VB.net will not allow me to update

Question

I have a datagridview with a dataTable as the dataSource. The user can add new rows to the datadridview, but I dont display the primary key column (for obvious reasons) and set it to .visible = false. When I need to update the information in the datagridview to the database, I use the sqlClient.SqlCommandBuilder to then update the underlying datasource (the dataTable mentioned above). Now, because the hidden column is the primary key, I loop through the datagridview and programically add the required primary key field to each new row that does not already contain a primary key (user added rows). This works great 95% of the time... The problem is when the user somehow gives focus at some point (any point) to that bottom row on the datagridview, below their added rows, that is used to add new rows. The update command gives me an error stating that it cannot insert null into the primary key field, even though when checking all the values in every row, it is definitely NOT null for any of them. I have tried to trap for row.isNewRow (as the field never shows null) and deleting that row, but I get an error stating I can not delete an uncommitted row. If the focus is never given to that empty row beneath the existing rows and user added rows, the update works fine. What is going on?!

Answer 1

Sounds like your code is trying to delete the blank new row from the database--but it isn't in the database yet; it is "detached", and just in the memory of the DataGridView... so that's where you need to remove it. You could do this with a line in your validation event(s), which of course will (or should) be called before you save.

One way to handle this is to prompt the user to completely fill out the new row:

Private Sub dataGridView1_RowValidating(ByVal sender As Object, ByVal e As DataGridViewCellCancelEventArgs)
    If dataGridView1.Rows(e.RowIndex) IsNot Nothing AndAlso Not dataGridView1.Rows(e.RowIndex).IsNewRow Then
        Dim [error] As String = ""

        If dataGridView1.Rows(e.RowIndex).Cells("Field1").Value Is Nothing OrElse dataGridView1.Rows(e.RowIndex).Cells("Field1").Value.ToString().Trim() = "" Then
            [error] += " * Field #1" & vbLf
        End If

        If dataGridView1.Rows(e.RowIndex).Cells("Field2").Value Is Nothing OrElse dataGridView1.Rows(e.RowIndex).Cells("Field2").Value.ToString().Trim() = "" Then
            [error] += " * Field #2" & vbLf
        End If

        If [error] <> "" Then
            MessageBox.Show("The following required fields were left blank:" & vbLf & vbLf & [error], "Row Validation Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
            e.Cancel = True
        End If
    End If
End Sub

Another way to handle it is to remove the (empty) row from the DataGridView:

Private Sub dataGridView1_RowValidating(ByVal sender As Object, ByVal e As DataGridViewCellCancelEventArgs)
    If dataGridView1.Rows(e.RowIndex) IsNot Nothing AndAlso Not dataGridView1.Rows(e.RowIndex).IsNewRow Then
        dataGridView1.Rows.Remove(e.RowIndex)
    End If
End Sub

A third popular way this can be handled is to have your validation cancel the editing of the current row when nothing is done:

Private Sub dataGridView1_RowValidating(ByVal sender As Object, ByVal e As DataGridViewCellCancelEventArgs)
    Dim drv As DataRowView = TryCast(dataGridView1.CurrentRow.DataBoundItem, DataRowView)
    If drv IsNot Nothing AndAlso drv(0) = DBNull.Value Then
        drv.CancelEdit()
        e.Cancel = True
    End If
End Sub

(Also, on a side note: if your primary key (a/k/a identity) field is set to auto-number, you may find that ADO.NET is better able to handle the details of getting the next id and such during saves. For more info, check out the MSDN article on "Retrieving Identity of Autonumber Values" and this great set of examples for both SQL Server and MS Access.)