?
Solved

Update Table Multiple Users - DataAdapter

Posted on 2013-11-20
8
Medium Priority
?
1,095 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
[X]
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
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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
 

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 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.
0

Featured Post

How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

777 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