ADO Recordset locking

I have connected to the SQL Server 7 via ADOConnection. How can I implement a record/page lock in an ADO Recordset? Thanks.

There are several different ways to implement Locking with ADO and SQL 7.0.
1. After you have a connection to SQL with ADO and are retrieving your recordset, in the open statement for the recordset object you can use 4 different types of locking:
adLockReadOnly = This explains itself

adLockPessimistic = This will lock the record as you start editing, you don't have to worry about a conflict with another user's changes. However, this implies a permanaent connection to the data store. You also cannot use this locking with client side cursors.

adLockOptimistic = This gives you better resource management, but you have to cater for conflicts, as the lock doesn't actually occur until you try to Update the record.

adLockBatchOptimistic = For use with batch updates.

2. Using a stored procedure in SQL 7.0. This is the method that I prefer for a few reasons. All work is done on the server, and there is a better level of locking control. The locks available are as follows:

HOLDLOCK
UPDLOCK
TABLOCK
PAGLOCK
TABLOCKX
ROWLOCK
NOLOCK
READPAST
READUNCOMMITTED
READCOMMITTED
REPEATABLEREAD
SERIALIZABLE

Using these lock as hints when updating or retrieving data is a highly desireable way of programming and allows you to leverage VB/ADO and SQL Server. Check your SQL documentation for more info.

If you need more help, just let me know.

Allen Noakes
VB Programmer/Analyst
Dames & Moore Group
[email protected]

Thanks for the reply. Can you give me a sample code to handle the locking during editing. I'm quite new with ADO.
Where can I find those stored procedures you have mentioned. Thanks again, hope to hear from you again.

With
recordset.LockType = adLockPesimistic (or adLockOptimictic whichever suite your project)
End With

Stored Procedures are in SQL Server. They are not hard to write. Just takes a bit of time.

Here is a sample one that is on the enterprise:

CREATE PROCEDURE [sp_bulk_check_update] AS

CREATE TABLE [dbo].[alld_tmp] (
[DOCUKB] [varchar] (30) NOT NULL ,
[DOCUKG] [varchar] (30) NOT NULL ,
[UPD] [varchar] (1)
) ON [PRIMARY]


bulk insert LAVA5.dbo.alld_tmp From 'd:\lava5\apmiup.dat'
with(
FORMATFILE = 'd:\lava5\alld_tmp.fmt'
)

UPDATE dbo.alld_tmp
SET [UPD]='F'


declare
@rCount [int],
@tmpukb [varchar](10),
@tmpukg [varchar](10)

SET @rCount=1

WHILE (@rCount > 0) BEGIN
SELECT @tmpukb=[DOCUKB], @tmpukg=[DOCUKG]
FROM dbo.alld_tmp
WHERE [UPD] != 'T'
select @rCount=@@ROWCOUNT

UPDATE dbo.alld_tmp
SET [UPD]='T'
WHERE [DOCUKB]=@tmpukb

UPDATE dbo.alld
SET [DOCUKG]=@tmpukg, [test]='u'
WHERE [DOCUKB]=@tmpukb

IF (@rCount = 0)
BREAK
ELSE
CONTINUE
END


I don't need locks on this because no user will access the data here.

Hope this helps you out.

Allen Noakes
VB Programmer/Analyst
Dames & Moore Group

I have the same problem w/ locking, I'm a newbie in SQL/Server.
I'm also using ADO to access the record.
I guess your suggestion to run a stored procedure makes sense, problem is I don't know how to actually do it.
Here's my problem.I need make a stored procedure that can get the first available record of a table w/c is not being accessed by anybody.
When I got that record, I will lock it(record level lock) so that nobody else can read it.Hence if this stored procedure is run by multiple users,
each one would get the next available unlocked record. Only one record should be returned, I tried doing it but to no avail. please help!!!
The table is for a control number w/c can only be accessed by one user at a time.This used to be an easy task in other RDBMS, w/c has a locked() function that can be included in the where clause of the select command.

Thanks in advance.
Drake