DBConcurrency Exception Occured While Updating Using Dataadapter

Question

I am trying to edit DataTable Filled by NpgsqlDataAdapter. After calling Fill() method, I have only one row in DataTable. Then I changed value of one column only and tried to update as below.

Then I am getting this error:

DBConcurrencyException occured

My code is:

NpgsqlDataAdapter getAllData = new NpgsqlDataAdapter("SELECT sn,
code,product, unitprice, quantity, InvoiceNo, Date FROM stocktable WHERE Code='" + product + "'
 ORDER BY EDate ASC", DatabaseConnectionpg);
DataTable ds1 = new DataTable();
ds1.Clear();
getAllData.Fill(ds1);

if (ds1.Rows.Count > 0)
{
    ds1.Rows[0]["Quantity"] = qty;// calculated value
}
ds1 = ds1.GetChanges();

NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(getAllData);
//getAllData.RowUpdating += (sender2, e2) => { e2.Command.Parameters.Clear(); };
//cb.SetAllValues = false;
getAllData.DeleteCommand = cb.GetDeleteCommand();
getAllData.InsertCommand = cb.GetInsertCommand();
getAllData.UpdateCommand = cb.GetUpdateCommand();
int x = getAllData.Update(ds1);
if (x > 0)
{
    ds1.AcceptChanges();
}

EDIT: I have three fields as primary keys and I am calling only two fields in select statement. Is it reason for DBConcurrency error? But I am able to update the table with same (three fields as primary key) parameters in SQL Server 2005.

UPDATE:

I found the solution and the solution is I created and used second DataAdapter to update data. I used getAllData(NpgSqlDataAdapter) To fill table as

NpgsqlDataAdapter getAllData = new NpgsqlDataAdapter("SELECT 
code,product, unitprice, quantity, InvoiceNo, Date FROM stocktable WHERE Code='" + product + "'
 ORDER BY EDate ASC", DatabaseConnectionpg);

And Also created next Adapter to update as

NpgsqlDataAdapter updateadap= new NpgsqlDataAdapter("SELECT sn, quantity FROM stocktable WHERE Code='" + product + "'
 ORDER BY EDate ASC", DatabaseConnectionpg);
NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(updateadap);
    //getAllData.RowUpdating += (sender2, e2) => { e2.Command.Parameters.Clear(); };
    //cb.SetAllValues = false;
    updateadap.DeleteCommand = cb.GetDeleteCommand();
    updateadap.InsertCommand = cb.GetInsertCommand();
    updateadap.UpdateCommand = cb.GetUpdateCommand();
    int x = updateadap.Update(ds1);
    if (x > 0)
    {
        ......
    }

I tried alot and found that NpgsqlDataAdapter had problem with Column Code. When i ommited it then it worked. DataType of column code is varchar. I don't know why this was happening. Anybody has idea about it?

Answer 1

This is because DataAdapter uses Optimistic Concurrency by default. This means that if you are trying to update a row that no longer exists in the database or changed, the update from the DataAdapter will fail with the exception above.

Possible scenarios:

• Between you selecting the data into the client and sending the update, another user is deleting or updating this row from his application.
• It can be that you are deleting the data from somewhere else in your application.

For example:

1. You fill the DataTable that will be used for the update.
2. Deletes the row with Code = 1101 (for example) directly from the database, i.e. you do not use the DataTable here. This is emulating another user deleting the row with Code = 1101 from another application. Or some other part in your code deleting the row with Code = 1101.
3. Selects out the row with Code = 1101 from the DataTable, this is just to show that it is still there even though you have deleted it from the database itself.
4. Edits the Quantity column in the row with Code = 1101 in the DataTable. This has to be done, otherwise the call to Update will ignore this row when updating.
5. Executes the update, this will throw the exception since you are trying to update a row that (no longer) exists in the database.

If you want to implement Last Writer Wins, Add the following code:

cb.ConflictOption = ConflictOption.OverwriteChanges;

Also there is one more possible thing : if you have Decimal/numeric as columns in the DB they may cause this error even though the data looks the same. This is due to a decimal rounding error.

An important note: You should always use parameterized queries by the way. This kind of string concatenations are open for SQL Injection.