SQL Server ADO Error Handling 
Author Message
 SQL Server ADO Error Handling

Howdy All!
I have the following problem. I developed a COM object for use within an
ASP application.  Within this application I am accessing a Microsoft SQL
Server 7 database and manipulating the data via an object level (private
property variable) connection (ie, Private mvardbConnection as
connection).  The problem I am experiencing comes with the fact that at
some point data must be deleted and sometimes the data will have
dependent records which prevents it from being deleted.  As a result, of
course, I am receiving the following error:

Number: -2147217900
Description: DELETE statement conflicted with COLUMN REFERENCE
constraint 'FK_tblMemberships_tblGroups'. The conflict occurred in
database 'DocuStore', table 'tblMemberships', column 'groupid'.

Since I am passing the error onto the ASP application, I would like to
be able to check for a SPECIFIC error number and make a decision based
on it.  Unfortunately, and I am sure many of you already know what I am
about to say, I am receiving the NON-SPECIFIC -2147217900 error number
along with the error description passed back by SQL Server.

Does anyone have any idea what I can do to resolve this?

Pretty much in the end I would like to use something along the following
lines within the ASP application. Incidentally, vbObjectError is defined
elsewhere in the ASP application globally.  I am not sure about this
either.

if Err.number <> 0 then
   intErrorNumber = Err.number - vbObjectError
   select case intErrorNumber
      case "Specific Error Number"
          strErrorMsg = "LESS CRYPTIC ERROR MESSAGE HERE!"
          strPageMode = "delete"
      case else
          strErrorMsg = Err.Description
          strPageMode = "delete"
   end select
else
   strPageMode = "list"
end if

Please help!
I am sorry about the long post, but I wanted to be thorough.
Again, thanks for all of your help - past, present, and future.

*** Sent via Developersdex http://www.*-*-*.com/ ***
Don't just participate in USENET...get rewarded for it!



Sat, 21 May 2005 17:09:38 GMT  
 SQL Server ADO Error Handling
Well, I don't know if this will get you what you need but at least it will
give you ALL the detail the db vendor is providing:

db specific messages (errors and otherwise) are posted to the connection
object and can be accessed as
follows:

if oConn.Errors.Count <> 0 then
For Each oErr In oConn.Errors
  debug.print "ErrorNumber: " ; oErr.Number
    debug.print "Description: " ; errLoop.Description
    debug.print "Source: " ; errLoop.Source
    debug.print "SQL State: " ; errLoop.SQLState
    debug.print "Native Error: " ; errLoop.NativeError
Next
end if

SQL State and Native Error will give you as much detail as the db vendor is
supplying.  .Number and .Description are the codes passed to the VB error
object.


Quote:
> Howdy All!
> I have the following problem. I developed a COM object for use within an
> ASP application.  Within this application I am accessing a Microsoft SQL
> Server 7 database and manipulating the data via an object level (private
> property variable) connection (ie, Private mvardbConnection as
> connection).  The problem I am experiencing comes with the fact that at
> some point data must be deleted and sometimes the data will have
> dependent records which prevents it from being deleted.  As a result, of
> course, I am receiving the following error:

> Number: -2147217900
> Description: DELETE statement conflicted with COLUMN REFERENCE
> constraint 'FK_tblMemberships_tblGroups'. The conflict occurred in
> database 'DocuStore', table 'tblMemberships', column 'groupid'.

> Since I am passing the error onto the ASP application, I would like to
> be able to check for a SPECIFIC error number and make a decision based
> on it.  Unfortunately, and I am sure many of you already know what I am
> about to say, I am receiving the NON-SPECIFIC -2147217900 error number
> along with the error description passed back by SQL Server.

> Does anyone have any idea what I can do to resolve this?

> Pretty much in the end I would like to use something along the following
> lines within the ASP application. Incidentally, vbObjectError is defined
> elsewhere in the ASP application globally.  I am not sure about this
> either.

> if Err.number <> 0 then
>    intErrorNumber = Err.number - vbObjectError
>    select case intErrorNumber
>       case "Specific Error Number"
>           strErrorMsg = "LESS CRYPTIC ERROR MESSAGE HERE!"
>           strPageMode = "delete"
>       case else
>           strErrorMsg = Err.Description
>           strPageMode = "delete"
>    end select
> else
>    strPageMode = "list"
> end if

> Please help!
> I am sorry about the long post, but I wanted to be thorough.
> Again, thanks for all of your help - past, present, and future.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sun, 22 May 2005 00:02:59 GMT  
 SQL Server ADO Error Handling
FZ, thanks for the reply.  You know, I actually tried that method (all
except for the NativeError property) and still got the same -2147217900
error number.  Is there no specific error number returned from the SQL
Server through ADO that I can trap and perform some kind of conditional
response?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 22 May 2005 01:29:16 GMT  
 SQL Server ADO Error Handling
NativeError and SQLState are the end of the road.

FZ


Quote:
> FZ, thanks for the reply.  You know, I actually tried that method (all
> except for the NativeError property) and still got the same -2147217900
> error number.  Is there no specific error number returned from the SQL
> Server through ADO that I can trap and perform some kind of conditional
> response?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sun, 22 May 2005 21:39:22 GMT  
 SQL Server ADO Error Handling
I was recently looking into the same thing.  In my case I have
resorted to looking at the Err.HelpContext which does seem to refelct
the actual error condition, ie. duplicate key, locked record, etc.

These are the only two I've need so far:

Public Enum ADOErrors
    TableLockedExclusive = 5003008
    DuplicateRecord = 5003022
End Enum

The Err.Description string and Err.HelpContext always appeared to be
accurate even when Err.Number stay the same.

I'm not sure if this would be different for an ASP application.  But
it may be what you're looking for.



Mon, 23 May 2005 10:16:14 GMT  
 SQL Server ADO Error Handling
Mike,
    Interesting.  Where did you get the text values associated with the
HELPContext constants?  Here is the map as I see between the
VB ERR Object and the Connection Object Error Array

VB Err Object                       Connection Object
Number                  =             Number
Description            =             Description
HelpContext           not =        HelpContext
-----------------------------------
Plus
Connection Object Gives SQLState and NativeError of which there are no VB
Err Object equivalents.
Native Error is documented in BOL for SQL Server. VB Error Object
HelpContext must map
to NativeCode in SOME WAY.

dlr


Quote:
> I was recently looking into the same thing.  In my case I have
> resorted to looking at the Err.HelpContext which does seem to refelct
> the actual error condition, ie. duplicate key, locked record, etc.

> These are the only two I've need so far:

> Public Enum ADOErrors
>     TableLockedExclusive = 5003008
>     DuplicateRecord = 5003022
> End Enum

> The Err.Description string and Err.HelpContext always appeared to be
> accurate even when Err.Number stay the same.

> I'm not sure if this would be different for an ASP application.  But
> it may be what you're looking for.



Mon, 23 May 2005 21:59:42 GMT  
 SQL Server ADO Error Handling

Quote:
> HelpContext must map to NativeCode in SOME WAY.

Yeah.. you would think so. :-)

I found this page through Google and the errors seem to match up, (if
you add the 500 prefix) with the conditions I was testing for.

http://www.rangersuite.com/support/kb/Jet%20Db%20errors.htm

Admittedly I haven't tested this hypothesis extensively, so I could be
totally wrong about what "HelpContext" does, but I have read a few
other articles that I take to mean you can rely on that property for
our desired purpose. (I don't have the links handy, but they were on
the topic of ADO Error checking.)

Also, I should mention that for the app I am currently writing, so far
I am only using ADO on an Access 2000 database with the
Microsoft.Jet.OLEDB.4.0 provider.  Ultimately I will be adding support
for MS-SQL, so I may get totally different results then.  I haven't
had to cross that bridge with this app yet, so I haven't tested it.

If you find out anything let me know.

Mike

Quote:

> Mike,
>     Interesting.  Where did you get the text values associated with the
> HELPContext constants?  Here is the map as I see between the
> VB ERR Object and the Connection Object Error Array

> VB Err Object                       Connection Object
> Number                  =             Number
> Description            =             Description
> HelpContext           not =        HelpContext
> -----------------------------------
> Plus
> Connection Object Gives SQLState and NativeError of which there are no VB
> Err Object equivalents.
> Native Error is documented in BOL for SQL Server. VB Error Object
> HelpContext must map
> to NativeCode in SOME WAY.



Tue, 24 May 2005 09:31:54 GMT  
 SQL Server ADO Error Handling
Mike,
    Please.  The reference you provided was for MS Access Errors.  they may
indeed "seem to match up, (if you add the 500 prefix)" but the topic here
was SQL Server error messages. I don't think we want to bet the farm on
those numbers.  Please see SQL Server BOL for decoding NativeError Messages
from SQL Server.  Strangely HelpContext in the VB Error Object is intended
to point to Topic within the HelpFile pointed to by the HelpFile property
(of the error object), so the numbers in HELPCONTEXT seem to point to a
hypothetical MS Helpfile. not much, er, help, really.

dlr

Quote:
> > HelpContext must map to NativeCode in SOME WAY.

> Yeah.. you would think so. :-)

> I found this page through Google and the errors seem to match up, (if
> you add the 500 prefix) with the conditions I was testing for.

> http://www.rangersuite.com/support/kb/Jet%20Db%20errors.htm

> Admittedly I haven't tested this hypothesis extensively, so I could be
> totally wrong about what "HelpContext" does, but I have read a few
> other articles that I take to mean you can rely on that property for
> our desired purpose. (I don't have the links handy, but they were on
> the topic of ADO Error checking.)

> Also, I should mention that for the app I am currently writing, so far
> I am only using ADO on an Access 2000 database with the
> Microsoft.Jet.OLEDB.4.0 provider.  Ultimately I will be adding support
> for MS-SQL, so I may get totally different results then.  I haven't
> had to cross that bridge with this app yet, so I haven't tested it.

> If you find out anything let me know.

> Mike




- Show quoted text -

Quote:
> > Mike,
> >     Interesting.  Where did you get the text values associated with the
> > HELPContext constants?  Here is the map as I see between the
> > VB ERR Object and the Connection Object Error Array

> > VB Err Object                       Connection Object
> > Number                  =             Number
> > Description            =             Description
> > HelpContext           not =        HelpContext
> > -----------------------------------
> > Plus
> > Connection Object Gives SQLState and NativeError of which there are no
VB
> > Err Object equivalents.
> > Native Error is documented in BOL for SQL Server. VB Error Object
> > HelpContext must map
> > to NativeCode in SOME WAY.



Tue, 24 May 2005 23:08:04 GMT  
 SQL Server ADO Error Handling

Quote:
> Please see SQL Server BOL for decoding NativeError Messages

Isn't how this all started? Can you point me to the SQL BOL that will
tell me how to decode ADO errors, since the error code it returns
seems to be -2147217900, even when it's a different context?

Quote:
> from SQL Server.  Strangely HelpContext in the VB Error Object is intended
> to point to Topic within the HelpFile pointed to by the HelpFile property

*Sarcasm noted*

Then again in the context of an "Error" presumably the "HelpContext"
would provide some insight in to the cause of the error, or it
wouldn't really be providing much of a "context".  All I was looking
for was a value by which I could differentiate the different errors
since Err.Number doesn't seem to work. (at least for Access)  And
since this is an "ADO" newsgroup, not a "SQL" newsgroup I was only
conveying my experience.  But, as I noted, this is with Access and may
have no application for what the original poster asked.  If so, accept
my apologies for wasting your time.



Wed, 25 May 2005 11:31:47 GMT  
 SQL Server ADO Error Handling
Mike,
       OK.  I had way TOO MUCH coffee on Friday, you are trying to help and
thank you...
Here is (my) bottom line:
use NATIVE ERROR in the connection object Error Collection.  The ADO error
numbers will not necessarily map to specific SQL Server Messages.  For
Native Error Decoding see SQL Server BOLL at (mind the wrap):

b
lsql.chm::/tr_errorformats_0tpv.htm
b
lsql.chm::/tr_syserrors1_0cqh.htm

On the issue of

Quote:
> > {snip}.  Strangely HelpContext in the VB Error Object is intended
> > to point to Topic within the HelpFile pointed to by the HelpFile

property
well, I have (almost) never seen this implemented in any consistent and
reasonable way in the VB Error object so I always ignore Help Context since
there is no CHM helpfile provided by MS which does this mapping.

any one else?

dlr


Quote:
> > Please see SQL Server BOL for decoding NativeError Messages

> Isn't how this all started? Can you point me to the SQL BOL that will
> tell me how to decode ADO errors, since the error code it returns
> seems to be -2147217900, even when it's a different context?

> > from SQL Server.  Strangely HelpContext in the VB Error Object is
intended
> > to point to Topic within the HelpFile pointed to by the HelpFile
property

> *Sarcasm noted*

> Then again in the context of an "Error" presumably the "HelpContext"
> would provide some insight in to the cause of the error, or it
> wouldn't really be providing much of a "context".  All I was looking
> for was a value by which I could differentiate the different errors
> since Err.Number doesn't seem to work. (at least for Access)  And
> since this is an "ADO" newsgroup, not a "SQL" newsgroup I was only
> conveying my experience.  But, as I noted, this is with Access and may
> have no application for what the original poster asked.  If so, accept
> my apologies for wasting your time.



Fri, 27 May 2005 22:40:33 GMT  
 SQL Server ADO Error Handling
Quote:
>        OK.  I had way TOO MUCH coffee on Friday, you are trying to help and
> thank you...

LOL

Quote:
> Here is (my) bottom line:
> use NATIVE ERROR in the connection object Error Collection.  The ADO error
> numbers will not necessarily map to specific SQL Server Messages.  For

Thanks.  I guess I've been lazy using the ADO error codes, and
actually it seems to have been adequate for my needs up until now
(with MS-SQL).  The native error codes look to be a much better way
though. :-)


Sun, 29 May 2005 18:09:07 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. SQL Server sp Error Handling & ADO

2. ADO and SQL Server Error Handling

3. Error Handling: SQL Server Transaction Locks

4. Error handling between SQL Server and VB

5. Error Handling Between SQL Server and VB

6. Seeking advice on VB6->ADO->SQL Stored Procedure Error Handling

7. Seeking advice on VB6->ADO->SQL Stored Procedure Error Handling

8. ADO/VB6/SQL Server7 error handling

9. handling error when update ADO Recordset (VB6, ADO2.5, SQL 7)

10. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

11. ADO error trapping in VB + SQL Server 2000

12. ADO messages and SQL SERVER 7 errors

 

 
Powered by phpBB® Forum Software