[RESOLVED] DBConcurrencyException - Access

[Quasar6]

(Using C# Professional 2008 and an Access 2003 *.mdb database)

I'm getting a fairly standard concurrency violation...

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

... when I attempt to update my table adapter (built in the designer). I've researched the subject, so I know what a concurrency error means (database is different to what the application expects it to be) but I can thus far find no reason it to occur. It is not connected to a network, so it cannot be a valid concurrency violation.

The function is quite complex: After performing a custom Fill on the data (I generate an OleDbDataAdapter, feed it with an SQL string and run it on the Table), it trawls the DataGridView, makes changes to a single column (removes some values, modifies some, and leaves the rest alone) using a "row[x].cell[y].Value = newValue" mechanic.

Once this is done, I call BindingSource.EndEdit(), and TableAdapter.Update(Table). I then get the aforementioned error, every time this is run on this set of data. Interestingly, I have had no problems running this on different sets of data in the past.

The row returned by the error appears to be the top-most one, where cell[y].Value has been removed (replaced with an empty string).

I'm hoping someone can give me some information on this: thus far, I can find nothing that can help. Any help or information is sincerely appreciated.

Thanks,
Qu.

[jmcilhinney]

If you have TableAdapters then why are you creating a DataAdapter? How exactly does that figure into this?

[Quasar6]

If you have TableAdapters then why are you creating a DataAdapter? How exactly does that figure into this?

In short, I took the whatever-I-can-get-to-work approach to filtering the database. What I do is create a string like this:

MainSQLFilter = @"SELECT    ID,Field1, Field2, Field3, Field4, etc
FROM         Table
WHERE     ([ID] IN (""";

Append it with all the ID's I want to retrieve (with a for loop going through my ID list), then create my DataAdapter and fill the table with it:

OleDbDataAdapter da = new OleDbDataAdapter(MainSQLFilter, connectionString);
myDatabaseDataSet.Table.Clear();
da.Fill(myDatabaseDataSet.Table);

This retrieves the ID list I need. I've used the method multiple times, and not had any problems with it... (well, until now ) I took this route because I couldn't work out how to get a specific list of records using the designer TableAdapters.

Then I do the other stuff: updating the cell values in the datagrid view and calling EndEdit() and Update().

Thanks for replying, BTW.

[jmcilhinney]

That sounds legitimate enough. The two most common causes of illegitimate concurrency errors that I've seen is calling AcceptChanges at a bad time or messing up parameters in UPDATE statements. It doesn't sound like you're doing the first and the second is unlikely with a TableAdapter. All I can suggest is to try to save one record at a time and then examine everything closely when the error occurs.

[Quasar6]

What do you mean by "try to save one record at a time"? Call update after each change?

Is there anything else you can give me? I'm at my wits end here...

[Quasar6]

I tried putting an EndEdit() and Update() after every change. It happens on the very first row, when I attempt to blank out the cell (previously, the cell (a text cell) contained the value "1405"). Here's the code:

                        foreach (DataGridViewRow row in dataGridView.Rows)
                        {
                            foreach (int id in rowIDtoRemove)
                            {
                                if (row.Cells[15].Value != null)
                                {
                                    if (int.Parse(row.Cells[15].Value.ToString()) == id)
                                    {
                                        row.Cells[7].Value = "";
                                        myBindingSource.EndEdit(); 
                                        myTableAdapter.Update(virtualModelDataSet.Table);

Nothing done between here and the da.Fill() command affects the database or DataGridView, and as I mentioned before the 'base itself is on C:\, and not being touched by me or anyone.

[Quasar6]

Okay, thanks for all your help, JMC. I found the problem: I had added an extra column to the database and dataset and not updated the MainSQLFilter string to match. In my defence, a concurrency error isn't particulary helpful when trying to diagnose this, but nevertheless...

The quote in my signature feels rather appropriate right about now. RESOLVED!