Solved

Update Table Multiple Users - DataAdapter

Posted on 2013-11-20
8
914 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

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

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

771 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

13 Experts available now in Live!

Get 1:1 Help Now