tableAdapter Update only SQL table fields that where modified in web page

Hi,

I'm designing a web application where different users could be updating the same records.
I would like to make sure that only the changed fields by one user are updated one by one so there is no concurrency violation.

What is the best method to design this?

Its my first time working with SQL 2005 so i'm not sure about the tableAdapters "optimistic concurrency" option. Will this take care of it by itself?

thank you
LVL 1
carlosmonteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BirkyCommented:
Hi,

Being a web application the edit page could letf open for some time before they decide to edit a record. Thus they may overrite someone elses changes. SQL will protect this from happening when you modify the data directly in the database but chances are you web application only has a connection open when it is accessing the data (either being read or written to) and, kind of, caching the data in the interem.

I would suggest some form of locking of records to ensure that data isn't lost. I have done this a couple of ways but I cannot say which is the best for you. It all depends on how your data is accesssed and by what application. You could have an extra field in your database called "locked" that is set when a user edits a record and released when they are finished, or you might code it all in the application so that it does all the locking (look up semaphores or mutex) or you could design you own. Being a web application you may also want to check out the global.asx file if you are going for the software option.

Hope this helps.
0
theplonkCommented:
In some of my projects in the past I've implemented a record version number that is updated every time a record is update, and is checked before any update is applied.

For instance:
The original record id = 1. (generally i would use a guid, but for simplicity i've just used integers)

When person A and person B open up the record with out editing they will get the recid 1.

If person A updates the record, the recid 1 will be checked against the current rec id, which is 1, then the update will be applied and the recid will be changed to 2.

Then if person B tries to update the rec order, it will fail since his recid of 1 doesn't match the new recid of 2.

A simple way would be to create a stored procedure to accept the update parameters and the clients recid, to perform the validation and update the record.
0
carlosmonteAuthor Commented:
Birky:

In your suggestion of locking using a field in the DB what happens if the app crashes on a user that is in edit mode in a page. this means the record will stay locked for other users.
how could this be avoided?
0
BirkyCommented:
Hi,

If you check out the Global.asax file you can add an event on session end. If you setup an event here to release all the users current locks then you wont have this problem. If the page has an error then yes the record will be locked but only until the users session expires

I have a page that shows all the locked records and allows them to be unlocked but I havent needed to use it yet.

Hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carlosmonteAuthor Commented:
these are good comments but i want to research these options first.
Experts, is it ok if i leave it open a little longer? thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Theory

From novice to tech pro — start learning today.