DBC Exception Error Exception When Upgrading Using the Dataadapter

Question

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

Then I get this error:

DBConcurrencyException error occurred

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 only call two fields in a select statement. Is this the reason for DBConcurrency error? But I can update a table with the same parameters (three fields as primary key) in SQL Server 2005.

UPDATE:

I found a solution, and a solution I created and used a second DataAdapter to update the data. I used getAllData strong> (NpgSqlDataAdapter). To populate the 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 the following 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 a lot and found that NpgsqlDataAdapter was having a problem with the column code. When I overshadowed this, it worked. The column code's DataType is varchar. I do not know why this happened. Does anyone have an idea?

Answer 1

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

Possible scenarios :

• Between you, selecting data in the client and sending the update, another user removes or updates this line from his application.
• Perhaps you are deleting data from another place in your application.

For example :

• Fill in the DataTable that will be used for the update.
• Deletes a row using Code = 1101 (for example) directly from the database, i.e. you are not using a DataTable . This is an emulation of another user deleting a line using Code = 1101 from another application. Or some other part of your code that deletes a line using Code = 1101 .
• Selects a row with Code = 1101 from the DataTable , it just means that it still exists, even if you deleted it from the database itself.
• Edits the Quantity column in a row with Code = 1101 in a DataTable . This must be done, otherwise the call to Update will ignore this line when updating.
• Performs an update, this will throw an 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; 

There is also another possible thing: if you have Decimal / numeric as columns in the database, they can cause this error even if the data looks the same. This is due to rounding rounding error.

Important Note : You should always use parameterized queries . This type of string concatenation is open to SQL Injection .