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

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.

Answer 2

Hi NR,

yes I'm using an auto increment identity column as the primary key. I'm using no stored procedures however.

Calling FuDataSet.AcceptChanges() after FuTableAdapter.Update(FuDataSet.Fus) makes no difference (as far as I know AcceptChanges will be called automaticaly with FuTableAdapter.Update).

Answer 3

Hi Helon

Do you have the primary key in the FuDataSet.Fus table? If so what is the value after the update and what is the value in the database? Are they the same? If so, then this is not the cause of your problem.

Answer 4

Hi NR,

I looked into the table after the second delete and the primary key has '0' as value. In my understanding it should read '1' because I inserted another row (which was then deleted). The auto increment identity column should count up regardless what I do. Is that correct?

For clarification: 1. New Row, Identity '0' 2. Delete row 3. save -> nothing in databse 4. New Row, Identity '0' (should be '1'?) 5. save -> row with identity '0' in database-file 6. Delete row 7. save -> exception, row with identity '0' still in database

This is what I'm using to generate the pk-column: FuID INTEGER not null IDENTITY(0, 1) PRIMARY KEY

I looked into the database scheme and 'unique' was set to 'false' on pk which should not be the case (I thought this would be implicitly set to 'true'). However, I set it manually to 'true' but the problem still exists. By the way: Do you know the sql-command to set it to 'unique'?

Thanks!

Answer 5

Ok,

further research lead me to the assumption that not my above code is the problem, but that I'm missing a general concept here.

It seems that I have to manually re-synchronize the id collumn in the dataset. So if I add a new row and save it to the database, I have to querry the database for the 'real' id and update the id in the dataset. Is this correct?

It would be nice if you could tell how I can do this without using stored procedures.

Am I on the right path and am I correct assuming that there is no easier way to do this?

Thanks!

Answer 6

Ok,

just to make sure I understand the problem correctly, what happens seems to be:

1. I create a new row, which gets ID 0 in dataset
2. I delete this row
3. I save changes to the database. For the database it seems that nothing has happend.
4. I create a new row which gets now ID 1 in dataset (correct?)
5. I save changes to the database. In the database this row gets ID 0 (in dataset it has ID 1). Correct ?
6. I delete row in dataset which has ID 1.
7. I save changes to the database. Database tries to delete row with ID 1 (instead of 0), which is not existent. -> Crash!

[Is there any flag I could set so that this (common) problem will be addressed automaticaly?]

If this is the problem then I assume I have to querry the database after step 5 for the right ID (so 1 in dataset gets overwritten with 0).

How can I do this without stored procedures? A code snipped (prefferably in C#) would be very welcome.

Thank you!

Answer 7

Hi Helon,

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 8

Thank you NR,

now as I understand the problem I know where I have to look in the future.