getting process id of a locked record with VB 1

[violetstarlight]

I was wondering if there was a way to get the process ID on a record that locked using VB.

Allow me to explain more...

I am using ADO, server side cursors, and pessimistic locking to lock a row when a user accesses it. If another user tries to access the record, I can capture the error to see that it's locked. What I am looking for, is if there is a way to then see what process ID has the record lock to determine the user name who has the record locked.

Help?

 

[CCLINT]

What dbms are you using? JET?

 

[violetstarlight]

I'm using SQL Server 7.0 and ado is connecting via OLE DB driver for SQL Server.

 

[CCLINT]

Check the Connection Errors collection for the error number and if a NativeError is returned.

I would recommend not using the pessimistic locking, (or a server side cursor), but rather use a TimeStamp and when a user tries to update a record which has been changed since the last (re)query, comparing the TimeStamp to see if the data has changed since.
This can be done effectively using a stored proceedure.

 

[violetstarlight]

A NativeError is returned. To check and see if a record is locked, I am checking for an error code 1222.

per requirements, I have to use locking thru sql server and pessimistic locking for this specific functionality. The majority of the application is written to use client side cursors with optimistic locking. Previously I had locking handled programmatically, but the technical project manager requires that sql server locks be used.