Expert: Srini Nagarajan - 3/26/2008

Question
I have ASP front end application and SQL Server 7.0 backend database. When a user updates a record on the ASP page, I want that record to be pessimistically record level locked so that no other person can edit or update the record. But other users can edit and update other records in this SQL table.  Can you direct me to some sample code that will carry out this operation?  Or tell me at what part of the ASP code I need to add new codes?  I have one ASP page where you enter the customer id number and then the request is sent to SQL Server where it returns the particular recordset and populates a 2nd ASP page.  When a user is editing this page, the record level lock will prevent other users from editing the same record.  Thank you.

Answer
Hi,

You need to do the following steps since you are using SQL server 7.0 and Classic ASP

1. First create a Application variable called "UpdateRecs"
2. When you retrieve the record for edit update the "UpdateRecs" something like this

      Application.Lock
      Application("UpdateRecs") = Application("UpdateRecs") & ", " & Customer_ID
      Application.Unlock

3. After retrieve record, check whether "UpdateRecs"  contains customerID if yes, display LOCK message else continue the edit

4. While you update the record remove the customerID from Application Variables.

this will be quick and easy.  Use application.lock and unlock on every instance.

Thanks

-Srini