M
Mark Olbert
I believe there is a flaw in the way the Windows.Forms.DataGrid handles the creation of new
rows/records.
The observations in this post are based on a DataGrid bound to a DataRelation (specifically, the
child side of the DataRelation). The DataGrid is a control on a data form which displays one record
at a time. All the tables involved use autosequence fields with a starting value of -1 and an
increment of -1 (so newly created record IDs won't interfere with the existing records in the
underlying SQL Server tables).
When a user clicks on a cell in the "add new" row of a DataGrid, the row gets populated with default
values for the fields being displayed, and the foreign key field in the child table gets set to the
current master table autosequence id number. However, the "row" does not yet exist in the underlying
DataSource for the DataGrid. In fact, so far as I can tell, the row only gets created in the
underlying DataSource when you move off the current master record.
In and of itself this wouldn't be a problem, but because the "row" doesn't exist in the underlying
DataSource, any SqlAdapter.Update() operation won't involve it. This also appears to lead to some
odd "null object" errors inside the SqlAdapter.Update() method call.
Moreover, if the master record itself is a newly-created record, a call to SqlAdapter.Update() will
force the user to encounter an error when he/she moves off the master record. This error occurs
because the SqlAdapter.Update() adds the new master record to the underlying Sql Server table and
"assigns" a valid autosequence id number to the master record...which has to be different from the
master id number appearing as a foreign key in the DataGrid, and, since the DataGrid "row" doesn't
yet exist in any DataTable, the change in master record id number doesn't propagate to the new
"row". .NET flags this inconsistency when the user attempts to move off the master record. Needless
to say, this is quite confusing to the user.
So far the only workaround I've found for this flaw is to block any attempt to call
SqlAdapter.Update() when any DataGrid is in this "new record in limbo" state. Personally, I think
that's a real ugly solution (made even worse because the only algorithm I could find on the web for
determining if a DataGrid has one of these "new but not yet stored" rows is to compare the number of
rows in the DataGrid's BindingManagerBase to the number of rows the master record "thinks" exist via
a call to GetChildRows()).
What confuses me is that it appears ADO.NET has an architecture that would allow the creation of
"pending" DataRows in the underlying DataTable, which could then be either canceled away or not
depending upon what the user did to the DataGrid. In fact, I suspect I could add this capability
myself by forcing the user to click an "Add New Detail Record" button and wrapping the creation of a
new DataGrid row with BeginEdit(), EndEdit() and CancelEdit() calls. But that's not as transparent a
user interface as just letting the user click in the "add a new row" row of a DataGrid and start
typing.
I'd be interested in hearing any thoughts or other solutions people have on this issue.
- Mark
rows/records.
The observations in this post are based on a DataGrid bound to a DataRelation (specifically, the
child side of the DataRelation). The DataGrid is a control on a data form which displays one record
at a time. All the tables involved use autosequence fields with a starting value of -1 and an
increment of -1 (so newly created record IDs won't interfere with the existing records in the
underlying SQL Server tables).
When a user clicks on a cell in the "add new" row of a DataGrid, the row gets populated with default
values for the fields being displayed, and the foreign key field in the child table gets set to the
current master table autosequence id number. However, the "row" does not yet exist in the underlying
DataSource for the DataGrid. In fact, so far as I can tell, the row only gets created in the
underlying DataSource when you move off the current master record.
In and of itself this wouldn't be a problem, but because the "row" doesn't exist in the underlying
DataSource, any SqlAdapter.Update() operation won't involve it. This also appears to lead to some
odd "null object" errors inside the SqlAdapter.Update() method call.
Moreover, if the master record itself is a newly-created record, a call to SqlAdapter.Update() will
force the user to encounter an error when he/she moves off the master record. This error occurs
because the SqlAdapter.Update() adds the new master record to the underlying Sql Server table and
"assigns" a valid autosequence id number to the master record...which has to be different from the
master id number appearing as a foreign key in the DataGrid, and, since the DataGrid "row" doesn't
yet exist in any DataTable, the change in master record id number doesn't propagate to the new
"row". .NET flags this inconsistency when the user attempts to move off the master record. Needless
to say, this is quite confusing to the user.
So far the only workaround I've found for this flaw is to block any attempt to call
SqlAdapter.Update() when any DataGrid is in this "new record in limbo" state. Personally, I think
that's a real ugly solution (made even worse because the only algorithm I could find on the web for
determining if a DataGrid has one of these "new but not yet stored" rows is to compare the number of
rows in the DataGrid's BindingManagerBase to the number of rows the master record "thinks" exist via
a call to GetChildRows()).
What confuses me is that it appears ADO.NET has an architecture that would allow the creation of
"pending" DataRows in the underlying DataTable, which could then be either canceled away or not
depending upon what the user did to the DataGrid. In fact, I suspect I could add this capability
myself by forcing the user to click an "Add New Detail Record" button and wrapping the creation of a
new DataGrid row with BeginEdit(), EndEdit() and CancelEdit() calls. But that's not as transparent a
user interface as just letting the user click in the "add a new row" row of a DataGrid and start
typing.
I'd be interested in hearing any thoughts or other solutions people have on this issue.
- Mark