Update Table Multiple Users - DataAdapter


I am using vb.net and the SQLDataAdapter to display a query results from MS SQL into a datagridview so that the user can make changes the data and update it accordingly. They prefer the table view as its quick than using a form so I am just testing at this stage.

My problem is if person A loads the data, then person B does it just after. Then person A changes data to row 1, then person B also changes data in row 1. Person A saves the data but when person B saves it, the application errors out saying {"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."} - which totally makes sense however how can I lock a record or notify the user the row has changed before hitting save?

My idea was when a user clicks onto a row within datagridview, it will send a sql statement to a lock table which inserts the row id and user but not sure how person B would find this out?

Just wondering what other users have done to solve this - apart from create an entry form which locks records one by one and as users are going through the data it tells them this is in read only mode.

Who is Participating?
Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
If I understand properly, you want the second user to overwrite what the first one did.

If this is the case, simply change the command that is used to Update so that it does not check for concurrency.

If you do not provide your own commands and use a CommandBuilder instead, change its ConflictOption property to OverwriteChanges.
it's a  most common problem with sql server as there's no consistent locking on the database level You have to handle it in application , vb.net should have built in methods of handling this
The SQL Server is not designed to help you with this since it does not provide a way for one session to immediately notify other sessions.

Inter Process Communication
There are multiple IPC technologies available to you so it depends on how your application is deployed as to which ones you can use.
Basically each process will have to subscribe or listen for relevant messages and broadcast messages to the other processes based on user actions.
I would create an extended SQLDataAdapter and embed this stuff within it so you dont have to implement the messaging for each form.

Take a look at this
You could make your SQLDataAdapter both a publisher and a subscriber for a particular topic named <Database>.<Table> or something equivalent
Then you would just publish messages to the subscribers on the users edit movements
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

BoltonWandererAuthor Commented:
Thanks for the suggestions guys.

Do you know how I could achieve something like this: http://www.vbdotnetforums.com/ado-net/31422-concurrency-violation-updatecommand-affected-0-expected-1-records.html - on the 2nd post it has suggested to "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".
Would I need to reload the data and then do a compare of the changes then loop through them all filtering it to the unique ID's of the records? Or is there a better way for this?

 Dim Changes As DataSet = Me.sDs.GetChanges()

Open in new window

Jacques Bourgeois (James Burger)PresidentCommented:
Microsoft decided not to go with disconnected data sets in ADO.NET, and locks are useless that way, because you are working with a copy of the data instead of being connected to the database all the time.

Web programmers have been working that way for years, and its been found out that in todays system, dealing with concurrency is a better way than locks to handle multiusers conflicts. Huge sites that performs thousands of transactions per second without locks, such as Amazon, are proof of that. So they force Windows Applications programmers to work as Web programmers do.

There is indeed a better way, in my opinion, than reloading the data and looping through it.

There are events associated with the DataAdapter.

Simply declare your DataAdapter WithEvents in the Declarations section of the form, and you will be able to subscribe to these events the same way you do with the controls.

Dim WithEvents dad As New SqlDataAdapter

Open in new window

If you set the ContinueUpdateOnError property to True on the DataAdapter before calling the Update, you will be able to use its RowUpdated event for your purpose.

This event is called after each row has been submitted to the user. You can then query e.Row.HasErrors to know if there was a problem with the row, and use e.Row to get information in the row to display a message to the user with optional processing if desired, or to record the rows that did not get through. Use the e.Status property to decide what to do after each row is processed.
BoltonWandererAuthor Commented:
Thanks for the idea James. I have been taking a look at what has been suggested (I think anyway) and this is the code I have got. However I'm not sure where to put the e.Row.HasErrors, which event should I trigger for this? I tried the leave event but I don't think that is the correct one.

The form is very simple.
Button1 = load data
Button2 = Save data
DataGridView1 = the grid of all the data.

Any help would be very appreciated please.

Imports System.Data.SqlClient
Public Class Form1
    Dim sCommand As SqlCommand
    Dim WithEvents sAdapter As New SqlDataAdapter
    Dim sBuilder As SqlCommandBuilder
    Dim sDs As DataSet
    Dim sTable As DataTable

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        sAdapter.ContinueUpdateOnError = True
        Dim Changes As DataSet = Me.sDs.GetChanges()
        If Changes Is Nothing Then
            '   MsgBox(Changes.Tables.Count)
            '  MsgBox(Changes.Tables(0).Rows.Count)
        End If
    End Sub
    Private Shared Sub OnRowUpdated(sender As Object, args As SqlRowUpdatedEventArgs)
        If args.Status = UpdateStatus.ErrorsOccurred Then
            args.Status = UpdateStatus.SkipCurrentRow
            args.Row.RowError = args.Errors.Message
        End If
    End Sub
    Private Shared Sub OnRowUpdating(sender As Object, args As SqlRowUpdatingEventArgs)
        If args.StatementType = StatementType.Delete Then
            Dim tw As System.IO.TextWriter = System.IO.File.AppendText("Deletes.log")
            tw.WriteLine("{0}: Customer {1} Deleted.", DateTime.Now, args.Row("ID", DataRowVersion.Original))
        End If
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim connectionString As String = "Data Source=sqlserver\instance;Initial Catalog=AUDIT;Integrated Security=True"
        Dim sql As String = "SELECT *  FROM REPORT order by id ASC"
        Dim connection As New SqlConnection(connectionString)
        Dim txn As SqlTransaction
        sCommand = New SqlCommand(sql, connection)
        sAdapter = New SqlDataAdapter(sCommand)
        sBuilder = New SqlCommandBuilder(sAdapter)
        sDs = New DataSet()

        txn = connection.BeginTransaction(IsolationLevel.RepeatableRead)
        sAdapter.SelectCommand.Transaction = txn
        sAdapter.Fill(sDs, "CC")
        sTable = sDs.Tables("CC")
        DataGridView1.DataSource = sDs.Tables("CC")
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        AddHandler sAdapter.RowUpdating, New SqlRowUpdatingEventHandler(AddressOf OnRowUpdating)
        AddHandler sAdapter.RowUpdated, New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
    End Sub

End Class

Open in new window

Jacques Bourgeois (James Burger)PresidentCommented:
First, do not declare the variable WithEvents if you use AddHandler. You use one or the other, not both. WithEvents is an alternative to AddHandler that enables you to generate the event methods through the dropdowns at the top of the code window, just as you do with controls. It is usually the one preferred by most programmers (many of whom do not know about AddHandler), because it lets them work with events the way they are used to do it for controls. This is why I presented it to you.

If you want to stick to addHandler, your way of defining your events procedures is not good and go against standards. Look at other events such as the Click of your button. It is not declared as Shared and it is not called OnClick.

Your use of OnRowUpdating and OnRowUpdated as names could confuse some programmers. The "On" prefix is a convention to name the procedure that triggers the event, not the procedure that reacts to it. It is usually used when you create a class that inherits from another one and overrides one of the trigger methods of the base class.

And the standard name for an event arguments parameter is "e", not "args". This is why you did not understand where to use e.Row.HasErrors. In your case, it would have been args.Row.HasErrors.

Now to the question.

RowUpdated is automatically triggered by your call to Update in Button2_Click. Update sends an UPDATE command to the database for each of the rows, and RowUpdated is called after each of these UPDATE.

If you were to remove your AddHandler, stick with the WithEvents declaration and create the procedure by selecting your DataAdapter in the leftmost dropdown at the top of the code window, and then the RowUpdated event in the right dropdown, you would end up with code such as the following:
	Private Sub dad_RowUpdated(sender As Object, e As SqlRowUpdatedEventArgs) Handles dad.RowUpdated
		If e.Row.HasErrors Then
			'Handle the row, either by logging it somewhere, by opening a form in which the user can
			' decide what to do, using custom rules or by showing an error message as I do here
			MessageBox.Show("Could not update record number " & e.Row.Item("ID_FieldName").ToString & " probably because another user updated it before you...")
			e.Status = UpdateStatus.SkipCurrentRow
			'The current row went through without problem, let's continue to the next one
			e.Status = UpdateStatus.Continue
		End If
	End Sub

Open in new window

BoltonWandererAuthor Commented:
Thanks for the help there - I will make sure I do it one way or another - not both.

I have adjusted my code however when one user enters data before the other user and the first user saves the data, then the second user tries to save it, it just says "this row has no errors but doesn't save it" and the only way I can get them to enter the data they want is by them reloading all the data and removing the changes they have made for that one row.

Is there anything which allows me to say "Reload Dataset and then update?" If that makes sense.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.