asked on

Restrict an SQL record while editing

We have a VB.NET application that displays customer records from SQL 2016 database. We can edit or delete the customer records from there. About 50 users use that application. When a user has one customer record open, I do not want any other user to be able to access it. Instead a message should be displayed that another user is accessing this record.

What is the best way to do this? Does SQL have something on a locking record?

Parth Malhan

Hi,

Method 1:
You need to maintain state of the record that you opened in Database,
for that you need to create some table and insert row while it is opened in UI and you need to change the state in that table after it is closed.

So every UI Query, to access customer_table will first go to this state_table and check if that particular row is in use or not.

Cons: if connection between UI and server breaks, then that particular row will become invalid, you need to find that row in some
background process and clear that record from state_table.

Method 2:
you can handle that in UI as well,
you need to create a datatable on UI server and populate it same as above method.

hope it will help

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

Does SQL have something on a locking record?

Yes, as ste5an mentioned, use sp_getapplock(). This would be very complex code to try to do yourself, so don't bother, use what's already available to you.

Vitor Montalvão

Add a column to the table (e.g "Editing") and when an user is editing the record set this flag to true and when exiting the edit screen set it back to false, so when somebody is trying to edit a record you just need to test if the flag is true or false and act according.

Angel02

ASKER

@ste5an
Yes. I do not want want other users to even view the record that is locked.

Thanks for all the responses.