How do I apply row-level locking in SQL server with ADO 
Author Message
 How do I apply row-level locking in SQL server with ADO


    I am having a hell of a time trying to apply row-level locking in a VB6
application. I am creating an ado recordset and populating it by a query of
an sql server database. Here is my code:

strDataSource = "DSN=MyDSN"
Set cn = New ADODB.Connection
cn.Open strDataSource, "sa", ""

strOrderDetails = "select * from Table_A with (rowlock) where A = " & strA
Set rsOrderReportDetails = New ADODB.Recordset
rsOrderReportDetails.Open strOrderDetails, cn, adOpenKeyset,
adLockOptimistic

This applies a lock to all records in the table. What am I doing wrong? The
automatic lock de-escalation is not working when I omit the "with (rowlock)"
clause.



Fri, 19 Apr 2002 03:00:00 GMT  
 How do I apply row-level locking in SQL server with ADO
I'd like to hear any feedback that you get regarding this issue.

I've been looking for ways that a VB program can tell if the records
it's selecting are locked.  Then, the program could tell the user that
the records are locked, and that they will only be able to view them.

The only way I know to determine if records are locked is to try to
modify a record, and catch an error.  This is ludicrous, since you
wouldn't want user 1 to lock records, then user 2 would type in an
hour's worth of work, only to try to save, and be informed that user 1
is editing the records.  Do you know of a way to see if SQL Server 7.0
records are locked?

Thanks,

Took



Quote:

>     I am having a hell of a time trying to apply row-level locking in
a VB6
> application. I am creating an ado recordset and populating it by a
query of
> an sql server database. Here is my code:

> strDataSource = "DSN=MyDSN"
> Set cn = New ADODB.Connection
> cn.Open strDataSource, "sa", ""

> strOrderDetails = "select * from Table_A with (rowlock) where A = " &
strA
> Set rsOrderReportDetails = New ADODB.Recordset
> rsOrderReportDetails.Open strOrderDetails, cn, adOpenKeyset,
> adLockOptimistic

> This applies a lock to all records in the table. What am I doing
wrong? The
> automatic lock de-escalation is not working when I omit the "with
(rowlock)"
> clause.

Sent via Deja.com http://www.deja.com/
Before you buy.


Sun, 21 Apr 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. ADO SQL Server Row Locking

2. Row level locking use system stored procedure via ADO

3. ADO not releasing row level lock after .Update

4. row locking with MS SQL Server

5. Row Locking Using ADO 2.0 to an SQL 7 database

6. Access 97 Row-Level Record Locking

7. row-level locking with dao and access 2000

8. Command Object vs Connection Object for Row Level Locking

9. Command Object vs Connection Object for Row Level Locking

10. Row Level Locking Help???

11. Row level locking with an Access 97 DB ?

12. Row Level Locking

 

 
Powered by phpBB® Forum Software