Row Locking in SQL via ADO (VB 6)

Hi,

I'm trying to use the pessimistic row locking of SQL to get following result.

When a customer form is openend, the row should be locked for writing.
This lock should be left open until the user closes the customer form.

I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.

How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )?
And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?

For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.

I hope this is clear enough.I've often wondered if there is any reason that justifies pessimistic locking. So far I haven't found one. I recommend shutting down the SQL Server to get pessimistic locking... If the box is off, no other user can modify your data, and it makes the scaling problems caused by pessimistic locking less of a problem.

To answer your question more directly, yes pessimistic locking can be done using ADO. It has been a long time since I've had any reason to try to hurt myself that badly after I established that it was possible, so I'm fuzzy on the details.

-PatP|||This may be what you're looking for:

http://www34.brinkster.com/a213855/|||Pat,

I'm convinced that pessimistic locking is not the ideal solution.
How should i take care of the record-locking then?

Regards,

Sven Peeters|||Ummmm, optimistic locking (http://search.microsoft.com/search/results.aspx?qu=%22optimistic+locking%22&View=msdn&st=b&c=0&s=1&swc=0)? I especially reccomend An update on UPDATing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev00/html/vb00e1.asp), but there are lots of good articles to read!

-PatP