Concurrency Violation

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
129
Programming Experience
1-3
Hi Guys,

I am using VB.net 2010 and SQL Server 2008 R2 express. I've been reading all about concurrent violation on the web as its an occasional problem on my system too.

I think I understand why its happening i.e. data changed in datagridview and dataset doesn't match underlying data in sql server table.

But what I cannot find online is a good example I can follow that will work and help me avoid this problem, or even if it not avoidalbe the best way to handle it for the user!

Here is my shortened code that populates my dataset.
VB.NET:
Private conn As New SqlClient.SqlConnection(connStr)
Private cmd As SqlCommand
Private ds As New DataSet
Private da3 As SqlDataAdapter
Private bs3 As New BindingSource
'
'
'

cmd = New SqlCommand("SELECT * FROM TblService WHERE ClientID=@ClientID", conn)
cmd.Parameters.AddWithValue("@ClientID", ClientID)
da3 = New SqlDataAdapter(cmd)
da3.Fill(ds, "ClientServices")
bs3.DataSource = ds.Tables("ClientServices")
Me.BindingNavigator3.BindingSource = bs3
Me.DataGridView3.DataSource = Me.BindingNavigator3.BindingSource

and then the code to save any changes the user makes.

VB.NET:
Private Sub SaveServiceChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton3.Click
        'Code to Save Changes to Client Service in DatagridView 3!
        Try
            
            Dim objCommandBuilder As New SqlCommandBuilder(da3)
            Me.Validate()
            Me.bs3.EndEdit()
            da3.Update(ds, "ClientServices")
            ShowCloseDialog("Client Services records successfully updated")
         

        Catch ex As Exception
            MessageBox.Show(ex.Message, "HCS - Error!")
        End Try
    End Sub

All help appreciated.
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
The only legitimate reason for a concurrency violation to occur is that you retrieved data from the database and then tried to save changes but, in between, someone else saved changes to the same record(s). Concurrency violations exist specifically to prevent you blindly over-writing the other user's changes in that scenario. If that is your scenario then what you need to do is inform the user and what is generally done is that the current data is retrieved and merged with the pending changes before saving again.

If that's not your scenario then you're doing something wrong and you need to determine what it is and fix it. One of the most common causes for illegitimate concurrency violations is that an application inserts new records into a database that then generates IDs for those records but the application doesn't retrieve those IDs and still tries to update the records again. If you don't have the ID for the record in the database then you can't update it and the ID you do have either doesn't correspond to any record or it corresponds to the wrong record.

So, we need to know EXACTLY what you're doing that is getting the data in your DataSet out of sync with the data in your database. Is it another user or is it something you're doing wrong in your application? If the latter, is it what I said or something else?

Also, I'd adjust the way you're creating your objects a bit. Firstly, add the BindingSource in the designer and associate it with the BindingNavigator there. Secondly, don't create a command builder where you're saving but rather where you create the data adapter. I suggest that you take a look at the data adapter examples here.
 

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
129
Programming Experience
1-3
Hi jmcilhinney

,

As usual a very concise and excellent reply. It is only occasionally I get the concurrency violation and it seems to be if after the user adds a new row to the dataset and then changes an existing row, before saving! (if that makes sense).

I would also be interested in knowing how best implement a way of notifying a user if another user has the record open?

Would that be through storing a flag with the record ID and user ID in a table and checking the table before opening another record type of thing!

I am looking forward to reading and implementing the changes you mention above and in your examples and see if that improves things.

kind regards

Gerry
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
It is only occasionally I get the concurrency violation and it seems to be if after the user adds a new row to the dataset and then changes an existing row, before saving!
That sounds rather like exactly what I said.
One of the most common causes for illegitimate concurrency violations is that an application inserts new records into a database that then generates IDs for those records but the application doesn't retrieve those IDs and still tries to update the records again. If you don't have the ID for the record in the database then you can't update it and the ID you do have either doesn't correspond to any record or it corresponds to the wrong record.
The solution is to retrieve the auto-generated ID from the database when you insert a record. As you're using SQL Server, that's dead easy. You'll need to create your own action commands rather than using a command builder. That's not difficult and you can find an example using the link I provided earlier. You would then add a query to the end of your InsertCommand to retrieve the ID, e.g.
VB.NET:
INSERT INTO MyTable (MyColumn) VALUES (@MyColumn); SELECT ID = SCOPE_IDENTITY()
You can replace ID with the name of your PK column if it's different. That will get the value returned by the SCOPE_IDENTITY function, which is the last identity value generated in the current scope, and push it back into the ID column in the DataTable.
 

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
129
Programming Experience
1-3
Hi jmcilhinney ,

Thanks again for your patience.

I sure its simple too you, but unfortunately its not that simple for me.

What I find is happening is the users begins adding a new row to a datagridview (with 5 columns say) in the middle of entering the new data into the new row they decide to change the data in another row. If they continued to enter the data in the new row and saved there changes and change an existing row after that, there is no concurrency error !

To counteract that your saying is, as soon as they click on add new row to the datagridview run your code below

INSERT INTO MyTable (MyColumn) VALUES (@MyColumn); SELECT ID = SCOPE_IDENTITY()

I then insert into the new row in datagridview, column ID, the new integer ID, so now the table in the database and the dataset in the datagridview match.

Which event is the best to use to do this ? RowsAdded does always fire when user add news row for some reason?

It does seem a bit convoluted way, but obviously there is no simpler way otherwise it wold be all over the internet!

Maybe Im expecting too much but with such a common problem would MS not have it already fixed with a simple fix!

kind regards
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
You're trying very hard to complicate this. You create a data adapter with all four commands: SelectCommand, InsertCommand, UpdateCommand and DeleteCommand. You call Fill on that adapter to populate a DataTable and you bind that to your DataGridView, preferably via a BindingSource. The SQL code of the InsertCommand includes a SELECT statement to retrieve the auto-generated ID as I showed earlier. The user can make whatever edits they want in whatever order they want., It matters not. At any point, they can click the Save button you provided for them and you call Update on the same data adapter to save the changes from the DataTable to the database. All the edits are saved together and the IDs for any new rows are automatically pulled back from the database into the DataTable. The user can then continue editing as they like and save again later with no concurrency issues. If you do other than that then you're breaking it for no good reason.
 

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
129
Programming Experience
1-3
Ahh so,

When you define the INSERT statement and add the SELECT ID... at the end it will add this to the new row in the table and the dataset?

OK it sounds simpler than I was thinking, do you have a straight forward worked example of this I can use as a template?

kind regards
 
Top Bottom