I have a datagridview with a dataTable as the dataSource. The user can add new rows to the datagridview, but I don't 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 programmatically 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?!
Best Answer
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:
Another way to handle it is to remove the (empty) row from the DataGridView:
A third popular way this can be handled is to have your validation cancel the editing of the current row when nothing is done:
(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.)