How to properly commit bindingSource changes to source DB?

Question

I setup DataGridView and other UI components to allow user to edit data from SQLite DB. But these changes (even they are properly shows in application) doesn't saves to DB. I have tried this code

aBindingSource.EndEdit();
dbDataSetA.GetChanges();
aTableAdapter.Update(dbDataSetA.Accounts);

but there are concurrency exception:

System.Data.DBConcurrencyException was unhandled Message=Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

So how should I commit bindingsource changes to DB, guys?

POST EDIT I got this exception when I start the program, then click on second row in DataGridView and then on third and this time program raise a concurrency exception. Hope his help get this issue more detailed.

Thanks in advance, guys!

Concurrencyexception means: someone or something else has changed your data in the time between you've read it and the time you try to update it. Basically, in this case, you either need to load the data again and show the new data to the user so he can do his changes again, or you need some conflict resolution strategy to merge your new changes into the database. — marc_s, Jan 07 '12 at 10:35
@marc_s Ok, thanks, Marc! So how can I load the data again to show use changes? — kseen, Jan 07 '12 at 10:37

score 3 · Answer 1 · answered Jan 13 '12 at 18:53

Something like this?

try
{
   aBindingSource.EndEdit();
   dbDataSetA.GetChanges();
   aTableAdapter.Update(dbDataSetA.Accounts);   
}
catch (DBConcurrencyException exc)
{
   MessageBox.Show("original data changed, please redo updates");
   aTableAdapter.Fill(dbDataSetA);
}

Then reassign dbDataSetA as DataSource if needed and user has to enter data again.

score 3 · Answer 2 · edited Dec 10 '15 at 13:33

3

Had the same problem. Trick is, once you update table, you should "empty" GetChanges(). You do that by calling method AcceptChanges(). So...

aBindingSource.EndEdit();
dbDataSetA.GetChanges();
aTableAdapter.Update(dbDataSetA.Accounts); 
dbDataSetA.AcceptChanges();

It should work, provided it is the same problem.

edited Dec 10 '15 at 13:33

bluish

26,356
27
122
180

answered Feb 06 '12 at 13:46

TeM

31
1

score 1 · Answer 3 · answered Jan 07 '12 at 11:16

1

You could use an Adapter in combination with a command builder, something like this :

DataTable table = new DataTable();
var adapter = new SqlDataAdapter("SELECT * FROM ...", con)) 

//Load the dataTable and the bound datagridView
adapter.Fill(table);



    using (new SqlCommandBuilder(adapter))
    {
      //When done you can update the database using the Command builder
      adapter.Update(table);
    }

answered Jan 07 '12 at 11:16

aleroot

71,077
30
176
213

Please see my edit above. I think solution is more easier than you propose. Hope this edit will help you inderstand it more detailed. Thanks! – kseen Jan 07 '12 at 12:24

score 0 · Answer 4 · edited May 23 '17 at 11:59

0

Maybe oversize but can you try to use Transaction just read this Post, could be helpful :

Transactional sqlite

edited May 23 '17 at 11:59

Community

1
1

answered Jan 17 '12 at 02:55

3logy

2,634
8
46
99

How to properly commit bindingSource changes to source DB?

4 Answers4

Linked