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

[malatesh kumar]

Hi
I am working on vb.net.
I am facing problem when i update the record which i changed in the Textbox on the runtime.

My Code as follows:

 Private Sub btnPersonalcontact_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPersonalcontact.Click

        Dim contact As dbContactsDataSet.tblContactsRow
        contact = DbContactsDataSet.tblContacts.NewtblContactsRow
        With contact

            .ContactMedium = Me.cmbpersonal1.Text
            .ContactNumber = Me.txtperson1.Text
            .IDType = Me.duppersonal1.Text

        End With

        For i = 0 To DbContactsDataSet.tblContacts.Rows.Count - 1
            If Me.txtperson1.Text = DbContactsDataSet.tblContacts.Rows(i).Item(2) Then
                If Not Me.cmbpersonal1.Text = DbContactsDataSet.tblContacts.Rows(i).Item(1) Then
                    DbContactsDataSet.tblContacts.Rows(i).Item(1) = Me.cmbpersonal1.Text

                    MsgBox("Record Updated")

                    GoTo 2

                End If
                MsgBox("Sorry Contact already saved !!")
                Exit Sub
            Else
                GoTo 1
            End If
1:      Next i
        DbContactsDataSet.tblContacts.Rows.Add(contact)
2:      DbContactsDataSet.EndInit()
        TblContactsTableAdapter.Update(DbContactsDataSet.tblContacts)      
      Me.TableAdapterManager.UpdateAll(DbContactsDataSet)
        MsgBox("Contact Saved")
 End Sub

Please solve this......
I am not understanding what is that error??
"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records"

[jmcilhinney]

That exception means that you are trying to save data to the database where the original version you have doesn't match what's currently in the database. This is supposed to catch the situation where you retrieve the data, then someone else retrieves the data, then the other person edits and saves the data, then you edit and save the data. If your changes were saved without question then the changes made by the other person would be lost. By throwing a concurrency exception, the systems gives you the opportunity to give the user options, like retrieving the new data and editing again, maybe merging the two.

If this is not a multi-user system where that might be the case then it suggests one of two issues:

1. Your SQL code is broken.

2. You are editing, accepting changes, editing again and then saving.

[malatesh kumar]

Hi
This is not a multy user system and i am not using any SQL code while Updating.

For overcome this issue, where i want to change in my code??
or any additional code i required??

[Shaggy Hiker]

You are using SQL code, you just aren't writing it yourself. I have spent many hours studying this particular error message, and am not entirely convinced that it is not a compiler bug. If you search this forum on Concurrency Violation, or else on the whole error message, you will come up with one recent thread with some suggestions on a solution, and you will also probably come up with two threads I started where I was discussing what I had tried and what I had found as a result of this. I came to the conclusion that it was an error in the SQL provider, but I have considered investigating the problem from a different angle, though I haven't actually done so, yet.

[Socarsky]

You are using SQL code, you just aren't writing it yourself. I have spent many hours studying this particular error message, and am not entirely convinced that it is not a compiler bug. If you search this forum on Concurrency Violation, or else on the whole error message, you will come up with one recent thread with some suggestions on a solution, and you will also probably come up with two threads I started where I was discussing what I had tried and what I had found as a result of this. I came to the conclusion that it was an error in the SQL provider, but I have considered investigating the problem from a different angle, though I haven't actually done so, yet.

How about this case, is there any progress with it? I faced this issue since last a few days.

[Shaggy Hiker]

I haven't had reason to revisit the issue in years.

[Socarsky]

I haven't had reason to revisit the issue in years.

I see, I am in struggle to handle that. Can you help me?
Below causes that issue.

SqlDA.Update(TheDataTable)

[jmcilhinney]

One possible manifestation of the first cause I mentioned above:

1. Your SQL code is broken.

is that your InsertCommand doesn't retrieve auto-generated IDs from the database. A DataTable will generate temporary IDs that may not be the same as the final IDs generated when the data is saved to the database. If you save new records and then try to edit them and save again, the IDs will not match the records in the database so you will actually be trying to update a different record. That record's data will not match the original data that you have in your app so a concurrency exception is thrown. In that case, you need to retrieve those final IDs and refresh your DataTable when you save.

If you're using a typed DataSet, as the OP is, then this is probably just a matter of configuration. Open your DataSet in the designer, right-click each TableAdapter and select Configure, then click Advanced Options. One of the options should be "Refresh the data table", which is described thusly:

Adds a Select statement after Insert and Update statements to retrieve identity column values, default values, and other values calculated by the database.

If that box is unchecked then the CommandText of the adapter's InsertCommand will look something like this:

INSERT INTO [dbo].[Person] ([GivenName], [FamilyName]) VALUES (@GivenName, @FamilyName)

If the box is checked then the SQL code will look more like this:

INSERT INTO [dbo].[Person] ([GivenName], [FamilyName]) VALUES (@GivenName, @FamilyName);
SELECT PersonId, GivenName, FamilyName FROM Person WHERE (PersonId = SCOPE_IDENTITY())

That SELECT statement gets all the columns for a newly inserted record and sends them back to the DataTable. Most columns will be exactly the same but the PK column may well be different. This will therefore ensure that your DataTable contains the same data as your database, thus no concurrency violations should occur.

Note that, if you're not using a typed DataSet, you can simply add the extra query to the InsertCommand of your data adapter manually. In that case, you can probably omit all but the PersonId column from the column list of your query. Only those columns that might change need to be included.

That works for SQL Server and should for most other proper databases as well. If you're using Access then that option is disabled. That's because the Jet and ACE OLE DB providers don't support multiple SQL statements per command. The same will be true of some other databases too; mostly those that are not server-based. In that case, it's still possible to refresh your DataTable but it takes a bit more work. To learn how, follow the CodeBank link in my signature and check out my thread on Retrieving Auto-generated IDs From Access.

[larrycav]

One possible manifestation of the first cause I mentioned above:is that your InsertCommand doesn't retrieve auto-generated IDs from the database. A DataTable will generate temporary IDs that may not be the same as the final IDs generated when the data is saved to the database. If you save new records and then try to edit them and save again, the IDs will not match the records in the database so you will actually be trying to update a different record. That record's data will not match the original data that you have in your app so a concurrency exception is thrown. In that case, you need to retrieve those final IDs and refresh your DataTable when you save.

If you're using a typed DataSet, as the OP is, then this is probably just a matter of configuration. Open your DataSet in the designer, right-click each TableAdapter and select Configure, then click Advanced Options. One of the options should be "Refresh the data table", which is described thusly:If that box is unchecked then the CommandText of the adapter's InsertCommand will look something like this:

INSERT INTO [dbo].[Person] ([GivenName], [FamilyName]) VALUES (@GivenName, @FamilyName)

If the box is checked then the SQL code will look more like this:

INSERT INTO [dbo].[Person] ([GivenName], [FamilyName]) VALUES (@GivenName, @FamilyName);
SELECT PersonId, GivenName, FamilyName FROM Person WHERE (PersonId = SCOPE_IDENTITY())

That SELECT statement gets all the columns for a newly inserted record and sends them back to the DataTable. Most columns will be exactly the same but the PK column may well be different. This will therefore ensure that your DataTable contains the same data as your database, thus no concurrency violations should occur.

Note that, if you're not using a typed DataSet, you can simply add the extra query to the InsertCommand of your data adapter manually. In that case, you can probably omit all but the PersonId column from the column list of your query. Only those columns that might change need to be included.

That works for SQL Server and should for most other proper databases as well. If you're using Access then that option is disabled. That's because the Jet and ACE OLE DB providers don't support multiple SQL statements per command. The same will be true of some other databases too; mostly those that are not server-based. In that case, it's still possible to refresh your DataTable but it takes a bit more work. To learn how, follow the CodeBank link in my signature and check out my thread on Retrieving Auto-generated IDs From Access.

This is great information. I think what you are saying about auto generated id's could be causing the concurrency issue that has suddenly cropped up in my application. I looked at the table adapter configuration as suggested. NOTE my db is Access and my connection string was generated by VB and says it's OLEDB.

In looking at the table adapter configuration screen I noticed an Advanced Button. Took a screen shot of it but believe it or not when I try to screen shot it and paste it into Picture Manager so I can post it, the paste operation yields a blank image..... NOT HELPFULL.

Regardless, there is an option setting to USE Optimistic Concurrency that IS CHECKED. I"m thinking about un-checking that option but have concerns about what may get broken as the result of that route. I have many queries built that use that table adapter that I just can't risk being affected.

Thoughts on that possible solution??

[larrycav]

Mr. Impatient, [me] couldn't wait or an answer so I copied my project to my archives folder and fired up that copy. Looks like we have a solution.

Removing the check mark on the table adapter [Dataset Designer Screen \ Rt click on table adapter \ choose configuration \ click advanced button \ uncheck "Use Optimistic Concurrency"] Fixed my problem. NOTE...this is a single user, Access Database. For multi user projects that solution may not be the best one.

Thanks for posting the initial post on the concurrency issue JM and most especially for digging into it and providing alternatives. I searched a good bit of the evening last night on the issue and for some reason got no hits on this forum for solutions. Only after I posted my question about the concurrency visualization tool this morning did I discover this thread.

[Shaggy Hiker]

I got to encounter this one again in a demo. The code that was running had been used a few hundred times. In the demo, it broke with this exception. I repeated the exact same example with the same code....and it worked fine, just as it had for the few hundred times before.

There are things that will cause this exception, but from my experience, there are also times when it will happen without any clear cause.

[larrycav]

I got to encounter this one again in a demo. The code that was running had been used a few hundred times. In the demo, it broke with this exception. I repeated the exact same example with the same code....and it worked fine, just as it had for the few hundred times before.

There are things that will cause this exception, but from my experience, there are also times when it will happen without any clear cause.

Was it an Access Database?

I've been testing my code pretty pretty thoroughly today and so far...fingers crossed, all is good...knock on wood.

[Shaggy Hiker]

No, SQL Server. I don't think the DB has anything to do with it, though. On the other hand, I have no particular reason to say that, and I don't have enough data points, either, so it's just a hunch.

[larrycav]

No, SQL Server. I don't think the DB has anything to do with it, though. On the other hand, I have no particular reason to say that, and I don't have enough data points, either, so it's just a hunch.

Hunches often pay off. I will report back if I run into any quirks. I have seen ACCESS do some bizarre things that defy explanation. 9 times out of 10, when I run into a snag with the DB. I find solutions on this forum.