I have an ASP.NET page with a DataGrid. I populate the grid with
data from a table in SQL Server using a DataSet and DataTable
and allow the user to do various operations (Edit, Delete etc)
on the data in DataGrid.

When the user is satisfied with his changes I want him to press
a 'Save to Database' button to commit everything to the DB. I
don't want round trips to the database for each change he makes,
he has to consciously commit them all at once.

So in the clickevent for the 'Save to Database' button I do
DataTable dt = ds.Tables["Distributions"]; // ds is my DataSet
      foreach (DataRow dr in dt.Rows)
      {
        switch (dr.RowState)
        {
          case DataRowState.Added :
          {
            ins( ... ); // helper method to do a SQL INSERT
            break;
          }
          case DataRowState.Deleted :
          {
            del(Int32.Parse(dr["DistributionID"].ToString())); //helper method to do a SQL DELETE
          }
          .....

This fails with the DeletedRowInaccessibleException, which says:
"Deleted row information cannot be accessed through the row"
which seems fair enough, but I need to get the key of that
row so I can do the SQL DELETE.

I found a Microsoft knowledge base article which shows how to
use a CurrencyManager object, create a DataView etc to get
to the underlying data: but CurrencyManager is part of the
Windows.Forms namespace and I don't think I can use it in a
webform.

So, how do I do this?

Will Irwin

Hello,

Do do a dynamic insert to the database, you don't need to loop through the data table to find the rows to be added, deleted, etc. It automatically does it for you.

See the example code for the update method at: http://msdn.microsoft.com/library/de...pdatetopic.asp. You don't have to use the CommandBuilder; you can use custom queries and stored procedures to do the update.

Hope this helps,

Brian

Brian,

Thank you; that is a lot easier than what I was trying to do. I had
seen that command building going on in the graphical 'designer' mode
of Visual Studio, but I don't use that graphical stuff and had
overlooked the CommandBuilder in code.

I must say the semantics of the line from the MSDN help

  OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdaptor);

looks odd. It creates a new object (custDB) that is not referenced
anywhere else. It does its work in the constructor which evidently
makes changes to the DataAdapter that is passed into it.

Will Irwin

I've been running into the infamous "Deleted row information cannot be accessed through the row" when deleting a row from a data grid with a DataSet as the data source, then doing a delete in the database, which in turn has another trigger delete. I found a quick and easy solution by rejecting the changes in a new DataSet -- it seems to unlock the rows:

if(MyDataSet.HasChanges(DataRowState.Deleted))
{
    DataSet dsDeletedRows = MyDataSet.GetChanges(DataRowState.Deleted);
    dsDeletedRows.RejectChanges();
    for (int row = 0; row < dsDeletedRows .Tables[0].Rows.Count; row++)
        {
        SqlHelper.ExecuteNonQueryTypedParams(connection, "spDeleteRow", dsDeletedRows .Tables["MyDataTable"].Rows[row]);
        }
}