How do you trap the deadlock error code 1205 in VB

I am writing an app in VB and using SQL Server 2000 as the DBMS. I want to trap the deadlock error code 1205 in VB. Do I use the normal Err object in VB or the ADODB.Err objects in the connection object to trap it. eg

Option 1

if ABS(Err.Number) =  1205 then
   ...
   ..., etc.

////////////////////////////////////////////////////////////////////////////////
Option 2

Dim Cn as ADODB.Connection
Dim ADOErr As ADODB.Error
   ...
   ...

For Each ADOErr In Cn.Errors
      If Abs(ADOErr.Number) = 1205 Then
         ...
         ..., etc.

I am not in a position to test this before I install the app with the client, so I would appreciate any input from someone who has actually written code to handle this in a real app.

Thank you

Vincent_MonaghanAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The VB6 way, not sure if you are using.NET, and does not take into consideration errors off of connection objects...

on error goto error_handler

'Your code goes here

exit_function:
  on error resume next
  'Destroy any objects here
  exit sub

error_handler:
  Select Case err.Number
     Case 0
         'Not an error, ignor.
     Case 1205
         'You now have the error trapped, handle it here.
     Case Else
         'Handle it gracefully.
   End Select
   resume exit_function

end function

Hope this helps.
-Jim
0
 
jkaiosIT DirectorCommented:
Check the "Source" of the err.  If its from MS SQL Server then better trap the error object in ADO
otherwise, use the VB err object.
0
 
Vincent_MonaghanAuthor Commented:
These are good comments, but on consideration what I need is a slick way of replicating a deadlock situation on my standalone development PC to test that my code will handle a deadlock gracefully.
0
 
Vincent_MonaghanAuthor Commented:
Thanks
0
 
giyyuniCommented:
Use err.raise to create a deadlock error. But make sure you comment it out in prod.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.