VB6 SQL Server 7 Multiuser ADO and pessimistic locking 
Author Message
 VB6 SQL Server 7 Multiuser ADO and pessimistic locking

I am working with VB6, SQL Server 7 and I use ADO for accede to data

For example, I have the following table

Table1

Code : varchar(6)
Label1 : varchar(25)
Label2 : varchar(25)
CtrlAc : timestamp

I need to be able to acquire a rowlock at the time.

With the first application, I  accede to arow for update and I

create and Open an ADO-Connection (cn_ado)
create and Open an ADO-Recordset for a row of Table1 (rsado)

lSQL= SELECT * FROM Table1 WHERE Code=Code1
rsado.Open  lSQL , cn_ado , adOpenDynamic , adLockPessimistic

rsado is being open.

With the second application, I want to accede to another row for update
and I

create and Open an ADO-Connection (cn_ado)
create and Open an ADO-Recordset for another row of Table1 (rsado)

lSQL= SELECT * FROM Table1 WHERE Code=Code2
rsado.Open  lSQL , cn_ado , adOpenDynamic , adLockPessimistic

I cant open ADO-Recordeset.  I have to close the first ADO-Recordset in
first application for open the second ADO-Recordeset.

But, I want to lock a row without locking a page or a table.

In SQL Server, I see

Before first locking, I have:

exec sp_lock

spid    dbid    ObjId        IndId    Type Resource          Mode
Status
------ ------  -----------  ------ ----  ---------------- --------
------
1       1       0             0        DB                      S
GRANT
6       1       0             0        DB                      S
GRANT
7       10      0             0        DB                      S
GRANT
8       10      0             0       DB                      S
GRANT
9       10      0             0        DB                      S
GRANT
10      1       0             0        DB                      S
GRANT
10      2       0             0        DB                      S
GRANT
10      10      0             0        DB                      S
GRANT
10      1       117575457    0        TAB                    IS
GRANT

After first locking, I have:

exec sp_lock

spid    dbid    ObjId           IndId   Type  Resource        Mode
Status
------  ------  -----------  ------  ---- ----------------  --------
------
1       1      0                       0
DB                             S        GRANT
6       1       0                      0
DB                             S        GRANT
7       10      0                     0
DB                             S        GRANT
8       2       0                      0       EXT
1:8                    X        GRANT
8       2       0                      0
DB                             S        GRANT
8       2       0                      0
DB                             S        GRANT
8       10      0                     0
DB                             S        GRANT
8       10      901578250     0       PAG  1:663                IU
GRANT
8       10      901578250     0       RID  1:663:0
U        GRANT
8       10      901578250     0       TAB
IX       GRANT
9       10      0                     0
DB                             S        GRANT
10      1       0                     0
DB                             S        GRANT
10      2       0                     0
DB                             S        GRANT
10      2       0                     0
DB                             S        GRANT
10      10      0                    0
DB                             S        GRANT
10      1       117575457     0       TAB
IS       GRANT

901578250 : Table1

It seems to me that, I lock the table and the page.

I test with the following syntax

lSQL= SELECT * FROM Table1 With(ROWBLOCK) WHERE Code=Code1

I obtain so result.

May anybody help me?

Massoud



Thu, 21 Mar 2002 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. VB6 Multiuser ADO DED and pessimistic locking

2. VB6 Multiuser ADO DED and pessimistic locking

3. Pessimistic Locking Using RDO & SQL Server

4. multiuser handling ( locking ) in SQL Server

5. need ADO Help Pessimistic Locking + timing out

6. ADO 2.1, JET & pessimistic locking

7. need ADO Hcelp Pessimistic Locking + timing out

8. ADO, pessimistic lock

9. Problem with pessimistic locking in ADO 2.5

10. VB6 - CR7 - SQL Server - Want to send either SQL or ADO recordset to report

11. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL

12. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

 

 
Powered by phpBB® Forum Software