sql error not coming back to VB for handling 3

[joeschoe]

I have placed a constraint on a table to avoid inserting duplicate records.
When I create a duplicate I get an unfriendly error message box complaining about the constraint violation.

I would like to trap that error event, give a nice 'please be careful' message to the operator and then carry on processing.
The following code just ignores the error.

    On Error Resume Next
    Set Rs = connDB.Execute(Qstr)
    If Err.Number > 0 Then MsgBox "Bla bla etc"

There must be a way to pick up the sql generated error in neat way.
Any suggestions?

 

[JoeAtWork]

Check the ADO Errors collection, I believe the syntax is connDB.Errors().

 

[Golom]

The error number may not be greater than zero. Many errors raised by external processes have negative error number values. Try using

If Err.Number <> 0 Then MsgBox "Bla bla etc"

 

[mattKnight]

Look into the errors collection of the connection

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[mattKnight]

Arrgh Post clash

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[dglienna]

You should use an error trap so that you can handle certain erros, rather than to rely on Resume Next. It can cause real problems, if there are other errors in the module, causing the code to be skipped, and then pass to another module.

David

 

[joeschoe]

David, thanks for your suggestion.
I used to use a general error trap, but I found it not flexible enough for specific error handling.
I often find I have to respond differently to the same error such as "file not found" depending on the usage of the file.
I would be happy to discuss this in a separate thread.

 

[dglienna]

Error traps are specific to each form, but you can "promote" them to a different handler. You just "raise" the error. Resume Next can generate many problems.

David

 

[LazyMe]

Error traps are specific to each form

No they're not; they bubble up until a handler has been found or show you the runtime error box if not.

Resume Next can generate many problems.

I would like to know what kind of problems? In-line error handling, such as joeschoe is using sometimes is quite usefull as opposed to jumping from label to label which will be necessary for the same scenario with an On error goto XX handler.

Greetings,
Rick

 

[dilettante]

You just need to restore normal error handling ASAP by on error goto 0 or by exiting the current scope, as appropriate.

 

[dglienna]

I always reset the error trap in the error handler. If I want to raise the error to a more general handler, I can do that.
I meant that an error trap in one form won't handle errors in another form.
I posted that resume next causes problems if there is a calculation that returns a value, that might get skipped. In the next sub, the value is not right.

David

 

[strongm]

>resume next causes problems

Erm ... only if - as with many programming constructs - you don't really understand what you are doing. resume Next is a very powerful tool in VB

 

[dglienna]

True, but it can easily be misused. That's when it causes problems.

David

 

[BobRodes]

Googling to "inline error handling" will give much info on the subject of on error resume next. Have a look at this:

http://msdn.microsoft.com/library/d...-us/vbcon98/html/vbconinlineerrorhandling.asp

Golom's post is important, and you might want to read up on "vbObjectError". This link

http://msdn.microsoft.com/library/d...l/vbconerrorhandlingwithactivexcomponents.asp

should give some useful information.

HTH

Bob