I have been having issues using the DataGrid's built in Edit and Update features when the DataGrid was using the ObjectDataSource. The code generated by the IDE was causing a
"could not find a non-generic method" error when committing the changes
to an edited row in a DataGrid control.
Here is my analysis and solution:
The root of the problem has to do with the TableAdapter and its configuration wizard within the DataSet.xsd.
By default, Visual Studio will enable the "Refresh the data table"
option on the "Advanced Options" dialog accessible via a button in the
lower left corner of the "Enter a SQL Statement" page of the
TableAdapter Configuration Wizard. If the "Refresh the data table"
checkbox is checked, it will append a SELECT statement to the end of
the UPDATE and INSERT statements to "retrieve identity column values,
default values, and other values calculated by the database" of the row
that was just updated. To do this, it needs the "<PKField>" in
addition to the "original_<PKField>" to be sent from the DataGrid
control . When the column property for the primary key is set to
ReadOnly = True, the DataGrid will not include the "<PKField>" in
its Parameters collection during the Update method call. (note:
<PKField> is the field name of your primary key as in "ID", or
"original_ID")
When ever we Update from the DataGrid, the "original_<PKField>"
will be sent. This is good. It's the additional requirement of
sending the "<PKField>" for the additional SELECT statement at
the end of the Update query that is messing up our DataGrid's
functionality.
In other words, if the DataGrid is bound to the methods in the
TableAdapter via an ObjectDataSource, and if the primary key column of
the DataGrid is set ReadOnly=True, the DataGrid control will not pass
the primary key to the Update and Insert methods of the TableAdapter
even though those methods require it. Instead, it will only include
the "original_<PKField>"which it will use to locate the correct
record to update.
Disabling the "Refresh the data table" checkbox on the "Advanced
Options" dialog during configuration of the TableAdapter makes the most
sense to me and has the least impact on design and functionality while
still allowing the IDE to generate most of the code.
Some people have suggested changing the OldValuesParameterFormatString
of the ObjectDataSource from "original_{0}" to simply "{0}", but this
then causes the "original_<PKFieldName>" (i.e. "original_ID"),
which is still there in the parameters collection of the
ObjectDataSource, to never be initialized (i.e. null will be passed in
the Parameters collection during the Update and Insert) by the
GridView, resulting in the "Value cannot be null" error if your field
cannot be null. Some people benifited from the
OldValuesParameterFormatString fix, but I believe it was because their
fields could be null.