Strange problem with updating database from dataset
Operations:
Remove the selected row from the dataset in the DataGridView:
FuDataSet.FuRow row = (FuDataSet.FuRow) ((DataRowView)FuBindingSource.Current).Row; row.Delete();
To add a new line I am doing:
FuDataSet.FuRow row = FuDataSet.Fus.NewFuRow();
row.Someting = "Some initial Content";
row.SomethingElse = "More Initial Content";
...
FuDataSet.Fus.AddFuRow(row);
Save user changes to the current row in the dataset:
FuDataSet.FuRow row = (FuDataSet.FuRow) (((DataRowView) FuBindingSource.Current).Row); row.Someting = someTextBox.text; ...
Save to database:
Validate();
FuBindingSource.EndEdit();
FuTableAdapter.Update(FuDataSet.Fus); <-- Exception here
I am using the standard DatagridView, Dataset, TableAdapter, BindingSource Scheme VS, which are automatically set after defining the database structure. There is only one table and is using SQL Server compact 3.5.
Now my problem is that I get a Concurrency Exception (DeletedRowInaccessibleException) every time I do this (starting from an empty database): Create a new row, delete this row, save to database, new row, save to database, deleting this line, saving in the database <- Exception
I think there is some synchronization issue between the database and dataset.
If I reload the database after every save via FuTableAdapter.Fill (FuDataSet.Fus) the problem goes away. However, this may not be the intention, I think.
I hope someone can help me and spot the design failure or explain to me what might go wrong.
Thank!
Does your table have an auto-increment id column as the primary key? If so, it may not update the dataset table with the new value after insert, so when you come to delete it, it cannot find the row in the database. This might explain why it works once you've called the Fill () method.
You need to somehow return the primary key in the insert to keep the dataset table in sync with the database. If you are using a store procedure to insert, then the primary key can be returned using the out parameter. Not sure what is the best way if you are using the SQL insert statement in the command, but you will need to return the primary key from the database table and assign it to the row of the database table.
Not sure if you are doing this after saving, but calling FuDataSet.AcceptChanges () will help keep track of new data changes after database update.
source to share
What you put there is correct. When a new table is created in a dataset table, it creates its own ID. When you save the database, the database table also creates its own ID, which in most cases will be different from the dataset.
When you created the table adapter for this table, you needed to specify the sql state to create the dataset table. On the More Options button there is a checkbox for Update Data Table. Make sure to add the sql statement added after insert and update to add identity column.
If the checkbox is disabled, I'm not sure what else you can do other than reloading the data after every save, which won't be optimal.
Sorry, I can't help anymore. Good luck.
source to share