We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Updating with multiple users

Medium Priority
212 Views
Last Modified: 2012-05-06
Hi all,

This is a bit of a general question but here we go.

I have a datagridview on a windows form (VS 2005). This DGV gets its data from a dataview which feeds off a dataset which gets its data from a SQLdataadaptor (Looking at a SQL Server 2000 database). The DGV is filled on open and any changes (deleted/inserted or changed records) are saved on exit.

My query is. What happens if three users have the form open. One adds a record, one deletes a record and one makes a change to a record. What can I expect to be the result? Will it crash or will all changes be written successfully? I'm trying to get my head around the fact different users may be looking at different data depending upon when they opened the form. Someone who leaves it open all day would not see any changes users have been making throughout the day.

How would the experts recommend dealing with such situations?

Thanks in advance

What I would like to know
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Basically this deals with Concurrency, And to tackle this out, you can do the following approach:

1. Have one additional column in your SELECT query which fills the datagridview (ideally it can be a timestamp column)
2. When any of the values changes, you will issue an UPDATE statement based on the primary key in that table right.
3. Add this additional column too in the WHERE clause of UPDATE statement.
4. Hence the first user who saves it will be able to modify the records and update that timestamp column to a new value.
5. If any other user tries to update that particular record with the old timestamp and primary key, it will fail.
6. Catch that as an exception and display a message box in the application that some other user have modified that record.

This is the very basic and successful approach of handling Concurrency in all Applications.
Kindly revert if you have any doubts in my approach.

Author

Commented:
But what problems could I run into with deletion and insertion? I can see how that will work for me with changes to existing data but if one user deletes a record and another adds one, will this not cause SQL Server some confusion when updating?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
<< I can see how that will work for me with changes to existing data but if one user deletes a record and another adds one, will this not cause SQL Server some confusion when updating? >>

No confusions at all, Because if an user is deleting a record, then it will be deleted and hence the other users wont be able to see that record at all. If any others are adding any record, then it is a new one and hence users can update that record based upon its timestamp column.

JFF.. Servers dont confuse unless we code it to confuse.

Author

Commented:
Sorry, what I meant was what happens if a user opens the form at 5pm and leaves it open. Another opens the form at 6pm deletes 2 records and adds 1, then closes (saves the changes). After these changes the 1st user is still looking at the data he saw when he opened at 5pm. If he then makes makes changes to a record that's already been deleted that will error wont it?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Yes. It will error out and this is the fundamental scenario for handling concurrencies.

If the user tries to change any, just catch out that exception of zero records updated or zero records deleted (You will get zero if you use that timestamp column too to filter out records) and show a message box that records have been modified by other users.

I hope that should suffice and have seen many applications with that logic implemented. Its the user's responsibility to delete / modify as soon as he can so that others can work in parallel. Kindly inform this to the users prior.

Author

Commented:
So am I right in thinking that I can't have two users in adding and deleting records in the same SQL Server table at the same time? Surely this can't be?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
FMabey,
    Your statements diverts me now from your requirement. Can you clearly restate your requirements along with your doubts so that I can help you out.

If you have any doubts over comment no 23658052, kindly point that one too.

Author

Commented:
Ok,

Thanks for you patience on this one!

On a windows form we have the following:
DA1 - A SqlDataAdaptor
DS1 - A Dataset
DV1 - A Dataview
DG1 - A DataGridV

DV1 is what displays the data in the DG1 and is used to filter the data accordingly.
We also have User1, User2 and User3. These users should be able to access the form at the same time. My issue is that because the data is loaded on the open of the form, how do I deal with all three users updating, inserting and deleting into the same table? If 2 users are using the form at the same time how would locks and modifications be handled. I cannot have a user locked out of the table because another user is say editting a record.

Any questions please ask.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Yes.. I need further more inputs:

1. For example, I opened a record in DV1 and I try to update it with (5 mins sleep time say). In that mean time, you are trying to update that record. Kindly tell me who should succeed either you or me ( Because that is the part we need to deal carefully)
2. If any one changes a record and the Other still sees the old record, what steps needs to be done.

Kindly tell me those so that I can change a little bit in my approach given earlier to handle the situations exactly what you need.

Author

Commented:
Thanks for your reply,

1. Two users shouldn't be changing the same record. A record should be locked so another user cannot edit it.
2. I would like a flag to be shown against the record to tell them that it has been updated and that they will need to refresh to see the updated data. If a user deletes a record I would ideally want that record to become uneditable and inform the user why.
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
<< Two users shouldn't be changing the same record. A record should be locked so another user cannot edit it.

In your SELECT query, add the hint WITH ROWLOCK like

SELECT * FROM urtable WITH ROWLOCK

<< I would like a flag to be shown against the record to tell them that it has been updated and that they will need to refresh to see the updated data. If a user deletes a record I would ideally want that record to become uneditable and inform the user why. >>

Follow my Comment 23658052 and when the second user tries to do anything on the Deleted record, it will not update any record and will throw an exception for it. Catch that and show it as a Message for the user.

<< I would like a flag to be shown against the record to tell them that it has been updated and that they will need to refresh to see the updated data.>>

Unless otherwise you automatically issue several SELECT s, you wont be able to face this one out. And hence that is not practical.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for your patience... Excellent explainations.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.