Solved

Update Table Multiple Users - DataAdapter

Posted on 2013-11-20
8
946 Views
Last Modified: 2014-01-04
Hello,

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.

Thanks,
Alex
0
Comment
Question by:BoltonWanderer
8 Comments
 
LVL 21

Expert Comment

by:oleggold
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
0
 
LVL 9

Expert Comment

by:Beartlaoi
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
http://www.codeproject.com/Articles/22796/WCF-Implementation-of-the-Publisher-Subscriber-Mod
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
0
 

Author Comment

by:BoltonWanderer
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

0
 
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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:BoltonWanderer
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("blank")
        Else
            '   MsgBox(Changes.Tables.Count)
            '  MsgBox(Changes.Tables(0).Rows.Count)
            sAdapter.Update(sTable)
        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))
            tw.Close()
        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
        connection.Open()
        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")
        connection.Close()
        DataGridView1.DataSource = sDs.Tables("CC")
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        Me.sDs.AcceptChanges()

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



End Class

Open in new window

0
 
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
		Else
			'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

0
 

Author Comment

by:BoltonWanderer
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.

Thanks,
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now