[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Update Table Multiple Users - DataAdapter

Posted on 2013-11-20
Medium Priority
Last Modified: 2014-01-04

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.

Question by:BoltonWanderer
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 21

Expert Comment

ID: 39664006
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

Expert Comment

ID: 39664078
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

Author Comment

ID: 39664300
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

LVL 40
ID: 39664646
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.

Author Comment

ID: 39665223
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

LVL 40
ID: 39665652
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


Author Comment

ID: 39668312
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.

LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 1500 total points
ID: 39669329
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.

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question