error handling with SQL Server

[Jon LaRosa]

Hi all -

I have a web application and I want to be able to do some basic error
handling. For example, here is one error I would like to catch and
display in a useful way for the user:

-----------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
conflicted with COLUMN FOREIGN KEY constraint
'FK_tbl_DemographicInfo_tbl_LookupCurrentLiving'. The conflict occurred
in database 'asian_task_force', table 'tbl_LookupCurrentLiving', column
'Code'.

/ASAPOnline/demoProcess.asp, line 271
-----------------

The error is straightforward to you and me. People reading this
newsgroup know why this error would occur and what it means. What is
not straightforward is how I can get the relevant info I need from the
error and return it to the user.

BTW, the line referenced, line 271, is a call to a stored procedure:
cmd.Execute
[cmd is set up as: set cmd = Server.CreateObject("ADODB.Command")]

An end user is not going to know what this error means. What I would
really like to be able to do is to determine that it's a foreign key
contrainst error and tell the user which field/table is causing the
error. If I can't get the actual table/field easily, I would like to at
least be able to determine that it's a foreign key constraint and
provide some text to the that explains what the heck that means in
plain english.

My problem is that I don't even know where to do this. I tried doing
this in the ASP code, and I was able to get this much:

---------------
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN FOREIGN KEY constraint
'FK_tbl_DemographicInfo_tbl_LookupCurrentLiving'. The conflict occurred
in database 'asian_task_force', table 'tbl_LookupCurrentLiving', column
'Code'.
HelpContext: 0
helpfile:
number: -2147217900
Source: Microsoft OLE DB Provider for ODBC Drivers
---------------

which is from the Err object that ASP returns. Still though, the fact
that it's a foreign key constraint error and the tables that are
involved are burried in the Err.Description text.

So would I be able to break this info out in my stored proc? I know
the message for the error is in the sysmessages table, in this case:

----------
%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.
----------

Is there any way I can get access to these "%ls" variables and then do
something with them? If so, where would I do this, in the stored proc,
in the ASP code, or elsewhere? Or is this an issue in the nebulous
region (read: the region I know very little about) of ODBC/OLE or ADO?
Help!

thanks in advance,
jon
jlarosa at alumni dot brown dot edu

[Roji. P. Thomas]

Jon,

To get a clear understanding about Error Handling in stored Procs, see

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jon LaRosa" <jl*****@alumni.brown.edu> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Hi all -

I have a web application and I want to be able to do some basic error
handling. For example, here is one error I would like to catch and
display in a useful way for the user:

-----------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
conflicted with COLUMN FOREIGN KEY constraint
'FK_tbl_DemographicInfo_tbl_LookupCurrentLiving'. The conflict occurred
in database 'asian_task_force', table 'tbl_LookupCurrentLiving', column
'Code'.

/ASAPOnline/demoProcess.asp, line 271
-----------------

The error is straightforward to you and me. People reading this
newsgroup know why this error would occur and what it means. What is
not straightforward is how I can get the relevant info I need from the
error and return it to the user.

BTW, the line referenced, line 271, is a call to a stored procedure:
cmd.Execute
[cmd is set up as: set cmd = Server.CreateObject("ADODB.Command")]

An end user is not going to know what this error means. What I would
really like to be able to do is to determine that it's a foreign key
contrainst error and tell the user which field/table is causing the
error. If I can't get the actual table/field easily, I would like to at
least be able to determine that it's a foreign key constraint and
provide some text to the that explains what the heck that means in
plain english.

My problem is that I don't even know where to do this. I tried doing
this in the ASP code, and I was able to get this much:

---------------
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN FOREIGN KEY constraint
'FK_tbl_DemographicInfo_tbl_LookupCurrentLiving'. The conflict occurred
in database 'asian_task_force', table 'tbl_LookupCurrentLiving', column
'Code'.
HelpContext: 0
helpfile:
number: -2147217900
Source: Microsoft OLE DB Provider for ODBC Drivers
---------------

which is from the Err object that ASP returns. Still though, the fact
that it's a foreign key constraint error and the tables that are
involved are burried in the Err.Description text.

So would I be able to break this info out in my stored proc? I know
the message for the error is in the sysmessages table, in this case:

----------
%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.
----------

Is there any way I can get access to these "%ls" variables and then do
something with them? If so, where would I do this, in the stored proc,
in the ASP code, or elsewhere? Or is this an issue in the nebulous
region (read: the region I know very little about) of ODBC/OLE or ADO?
Help!

thanks in advance,
jon
jlarosa at alumni dot brown dot edu