Catch SqlServer Errors in VB6

[CVDpr]

Hey there, vb6 automatically open a message box when executing a Store Procedure (recordset.open etc..) but how i catch that error and create a custom one because i cant catch the error with the err.number(-2147217873).

I'm currently using this method to catch unique Key constraint

        set rsC = rs.clone
        rsC.Requery
        rsC.Filter = "CDMId =" & cboCDMID & " And " & "InsId =" & cboInsID

        If rsC.RecordCount > 0 Then
        Msg "CDMID '" & cboCDMID & "', InsID '" & cboInsID & "'  is already registered", ""
            cboCDMID.SetFocus
            Exit Sub
            Else
                rsN.Open nSelect, CnString, adOpenDynamic, adLockOptimistic
                FColorW
                cboCDMID.SetFocus                
       End If

The problem is that the requery is slow with large data.

How to catch in VB6 an create a custom/friendly Msbox error from a SqlServer/Store Procedure? Thanks

[TysonLPrice]

I think you need to try and catch it in the stored procedure not VB. Take a look at this link. About three pages down it's catching a constraint error.

http://www.simple-talk.com/sql/t-sql...ing-workbench/

[si_the_geek]

Does "i cant catch the error" mean that an error handler doesn't help, or that you don't know how to make one?

If you don't know how, see the article about it in the "Dealing with Errors" section of our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

[CVDpr]

I think you need to try and catch it in the stored procedure not VB. Take a look at this link. About three pages down it's catching a constraint error.

http://www.simple-talk.com/sql/t-sql...ing-workbench/

I have a begin transaction, try/catch in the store procedure but when there is a
contrainst etc.. only show sysytem message not a custom one.

[TysonLPrice]

I have a begin transaction, try/catch in the store procedure but when there is a
contrainst etc.. only show sysytem message not a custom one.

Try looking at the SQL RaiseError statement...

http://weblogs.asp.net/guys/archive/...07/226399.aspx

[CVDpr]

Now i can catch the constraint with this

On Error GoTo errHandler

errHandler:
If adRecIntegrityViolation Or adErrIntegrityViolation Then
        Msg "Ya existe un record similar", ""
        Exit Sub
End If

is this is ok or it just luck? is both of this are the same thing? "adRecIntegrityViolation Or adErrIntegrityViolation "

[si_the_geek]

That's just luck I'm afraid - you aren't checking the error code at all (what you are accidentally doing there is actually a Bit manipulation and conversion to boolean, which will always be true).

It should be like this:

If Err.Number = adRecIntegrityViolation Or Err.Number = adErrIntegrityViolation Then

[CVDpr]

It should be like this:

If Err.Number = adRecIntegrityViolation Or Err.Number = adErrIntegrityViolation Then

Now it doesnot work. is till showing this message

Violation of UNIQUE KEY constraint 'IX_AppInformationOfBillingByInsuranceAndServiceArea_1'. Cannot insert duplicate key in object 'AppInformationOfBillingByInsuranceAndServiceArea'.

[si_the_geek]

Can you show us a screenshot of the error?

(you can upload it here, by going to "Post Reply" then "Manage Attachments")

[TysonLPrice]

Have you considered the business logic? I don't know what your app is doing but it seems you should be able to handle not allowing duplicates. Why would someone be picking the DB keys?

[CVDpr]

ok..

[CVDpr]

Have you considered the business logic? I don't know what your app is doing but it seems you should be able to handle not allowing duplicates. Why would someone be picking the DB keys?

how to check for duplicate without picking the db?

[si_the_geek]

That looks like an error within your application, but being handled by a different part of the program, because your current error handler doesn't deal with it (so the error gets raised to the caller).

I should have thought of this before:

errHandler:
If Err.Number = adRecIntegrityViolation Or Err.Number = adErrIntegrityViolation Then
        Msg "Ya existe un record similar", ""
Else
        Msg "Error " & Err.Number & ": " & Err.Description
End If

Once you know the actual error number, you can use that in the If statement.

[CVDpr]

Once you know the actual error number, you can use that in the If statement.

The error number is -2147217873
i cant use this number, doesnot work and vb6 always show this number when dont know a vb number

[si_the_geek]

What do you mean by that?

I have used numbers similar to that in my error handlers lots of times (eg: If Err.Number = -2147217873 Then ), and it has always acted correctly.

[CVDpr]

done thanks.