Strange Problem with updating Database from Dataset

Question

Operations:

Delete in DataGridView selected row from Dataset:

FuDataSet.FuRow row = (FuDataSet.FuRow) ((DataRowView)FuBindingSource.Current).Row;
row.Delete();

To add a new Row I'm doing:

FuDataSet.FuRow row = FuDataSet.Fus.NewFuRow();
row.Someting = "Some initial Content";
row.SomethingElse = "More Initial Content";
...
FuDataSet.Fus.AddFuRow(row);

Saving user changes in current row in Dataset:

FuDataSet.FuRow row = (FuDataSet.FuRow) (((DataRowView) FuBindingSource.Current).Row);
row.Someting = someTextBox.text;
...

Save in Database:

Validate();
FuBindingSource.EndEdit();
FuTableAdapter.Update(FuDataSet.Fus); <-- Exception here

I'm using the standard DatagridView, Dataset, TableAdapter, BindingSource Scheme VS puts automaticly up after defining the database structure. There is only a single table involved and SQL Server compact 3.5 is used.

Now my problem is that I get a Concurrency Exception (DeletedRowInaccessibleException) each time I'm doing this (starting with an empty database): Creating a new row, delete this row, save in Database, new row, save in database, delete this row, save in database <- Exception

I think that there is some synchroniszing problem between the database and the dataset.

If I'm reloading the databse after each save via FuTableAdapter.Fill(FuDataSet.Fus) the problem is gone. However, this cannot be the intention I think.

I hope someone can help me out and spot a failure in the design or explain me what may go wrong.

Thank you!

Answer 1

What you have listed there is correct. When a new row is created in the dataset table, it creates it's own ID. When you save to the database, the database table creates it's own ID as well, which in most cases will be different to the one in the dataset.

When you created the table adapter for that table, you had to supply a sql state to create the dataset table. On the advanced Options button, there is a checkbox called "Refresh the data table". Check that to have a sql statement added after the insert and update to retrieve the identity column.

If the checkbox is disabled then I am not sure what else you could, other than reload the data after each save, which will not be optimal.

Sorry I cannot be of more assistance. Best of luck

Answer 2

Does your table have an auto increment identity column as the primary key? If so it might not be updating the dataset table with the new value after the insert, so when you come to delete it, it cannot find the row in the database. That could explain why it works once you called the Fill() method.

You will need to somehow return the primary key on the insert so that the dataset table stays in sync with database. If you are using a store procedure to do the inserts, then primary key can be returned using an out parameter. Not sure what the best way is if you are using an SQL insert statement in the command, but you will then have to get the primary key back from the database table and assign it to the database table row.

Not sure if you are doing this after the saveing, but calling FuDataSet.AcceptChanges() will help the dataset track new changes after the database has been updated.