posted on Thursday, May 04, 2006 2:15 PM by admin

Issues updating a DataGrid row when using an ObjectDataSource and DataSet

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.