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

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



i have seen this error many time and found some cure but can't figure out why now its happening..

i have a table named discount and passing text value every time when text changes and save when click a button .. i know it happens because of this type of situation.

This type of problem also happen if dataType is not matched correctly or some table was opened after working on current form...


but can some one give me the cure... please help me.. great trouble...

Your program executed an UPDATE statement and expected it to return that it had updated 1 row, but it returned that it had updated 0 rows.

You should now find out why. We cannot tell you because we dont have the ability to look at your data and say "oh, look.. it's because Column X has changed recently"
Note that this is called optimistic concurrency; you send the update HOPING that no-one else has changed the data youre trying to change. If you get this exception. It means someone else changed the data youre trying to change in the time between you SELECTing it and you UPDATEing it. How you proceed with that is up to you. Personally, I'd show the user: "Someone else changed the data to this: and here are your changes also: ... Chose which to keep/merge/overwrite/ignore"

If youre still puzzled, do let me know why; I can't quite understand how it's a puzzle, you see.. If you tried to save a Word document and got the message "Another user already has this document open and you cannot save your changes" it would be fairly obvious what the problem is.. I find this error message to be similarly obvious and self explanatory, so knowing what is difficult or puzzling about it would really help me help others

your concept and my concept is pretty similar but in one form i am the one who is working in one table and there is no one else.. so why this problem occur. i tried to change the Discount Column every time when a textbox textchange event raise... so do you think this is a cause for this exception?

can you give me a sample or link for solve type of errors..

and this view is not saving or working form view.. its a error details view of datatable..
i m changes this tables discount on some other form where the Discount value changes everytime on textchange.. that all.. any suggestion/

I don't know enough about your program to answer your question.. All I'm saying is:

Between the time that you downloaded data into the datarow, and the time youre uploading the changed row, something else (it could even be your own program) has crept in and chenged the data in the db in such a way that the UPDATE statement cannot find any data to update.

If every item of data that was downloaded is not in exactly the same value when the update comes, the update will fail. Have a look at the UPDATE statement in the adapter and you will understand

i have solved my problem with saving those records each time textchanges..


thanks
alim

If you create a record, then delete it without saving between the two, it creates a DataRow in the DataTable with the RowState of Deleted even though it has never existed in the database. It was only created in your local DataTable.

When you try to update that DataRow, the TableAdapter will complain that the row's deletion could not be completed because it does not exist in the database. It will think the row was actually deleted by someone else while it was not looking when it actually never existed.

I do not know if it is the source of your problem, but saving after every action would make the problem disappear (although you lose the benefits of a disconnected architecture) so I think it might be it.

There are other ways to go around the problem, none really perfect, but you can put a condition to your deletion code to do table.Rows.Remove(myRow) instead of myRow.Delete() when its RowState is Added.

Stonkie, that's the biggest load of nonsense you've ever written. Deleting a row that is in rowstate Added causes it to be removed from the table.

As an aside, where does your advice actually fit into the problem? The error message says "UpdateCommand affected...", and I'm sure you know that UPDATE is not used to remove rows from a table ergo discussing deletion of rows it not relevant to this context..

I'm sure I actually solved an erroneous concurrency exception by adding a condition that does just that. I have no idea why or how it worked and I can't find that code anywhere now...

I just spent an hour doing tests on the matter and I can't reproduce the behavior I was expecting! It's really my wrong, sorry.

I believe ya. Thousands wouldnt ;')

Its Simple.. You just need to update the Parent saperatly when there is this error.

Consider the follwing example...

Public Sub SaveItems()
Try
Me.Validate()
Me.ProductsBindingSource.EndEdit()
Me.ProductSuppliersBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.DsProducts)

Catch DbconExp As System.Data.DBConcurrencyException
Try
Console.WriteLine("Fixing Concurrency Error...")
Me.Validate()
Me.ProductsBindingSource.EndEdit()
Me.ProductSuppliersBindingSource.EndEdit()

Me.ProductsTableAdapter.Update(Me.DsProducts.Produ cts)
Me.TableAdapterManager.UpdateAll(Me.DsProducts)

Catch ex As Exception
MsgBox(ex.Message)
End Try
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub



Hope it will help someone like me >>>>>>>

Just because an error message has a simple (to some people) meaning, doesn't mean that debugging the issue is simple.
In my case it certainly wasnt anything going on at the database that caused this error, it was not rubbing the ADO.NET objects the way they need to be rubbed,
which is not necessarily intuitive.

For example, You will also get this error if for whatever reason you don't call update on the data source datatable.
the datatable has to be "reset" before calling update again.

If you do something like:

Code:

        Dim DataTableChanges = DataTable.GetChanges()
        If DataTableChanges IsNot Nothing Then
          SqlDataAdapter.Update(DataTableChanges)
        End If

you will get the error.

not 100% convinced that your code sample will always cause the error, what with that code being one example of a standard way to update a database and all - if you see this over message it really is because the database couldn't find any records to update

..PS the thread is 2 years old