SQL Server Performance Forum – Threads Archive

lock-flag in VB5???

Does anybody know: How can I get SQL error message/lock-flag? For example, If a user try to update one table, if the table is locked by others.
He should get a SQL error message/lock-flag.
How can I get the SQL error message/lock-flag. each time before he update table, he should find out whether there’s a lock on the table, like: select @@error The reason what I need a return-flag before updating table is to avoid the lock.
Sometime the applicaiton locks the table for some reason and nobody can update it. Paul’s idea seems better for me, could sombody please tell how to find the error-collection for those locking flag (row/table) in VB5? Could you provide a web-link?
Thanks

I’m not sure it would pay off to try and find out if a table has any locks before hand as locking can be pretty fast and it doesn’t neccesarily mean you won’t encounter a lock when you actually run your sql afterwards. If you’re using ADO which I’ll assume you are if you’re coming from VB5 then you could use the ADO Errors collection.
The thing with ADO that isn’t always clear is that you can actually recieve more than one error and the first error isn’t always the cause of the problem but can be a result of the problem occuring in the first place. Assuming you have a connection to the database, have run some sql or a command and it falls over, you can get at the errors like so (con is you connection object). Dim err as Error ‘ ado error object For Each err In con.Errors
Debug.Print err.Number, err.Description ‘log or write out error
Next Hope this helps. Cheers
Shaun World Domination Through Superior Software

Thanks.

I would advise setting the lock timeout to a reasonable time, perhaps 5-10s and then capturing any lock timeout errors that occur. The time that you specify the lock timeout to be will be the time that the front-end user sits there waiting for a response. It will be a trade-off between showing an error and waiting for a lock to release.

]]>