error handling with SQL Server

Discussion in 'ASP General' started by Jon LaRosa, Jan 3, 2005.

  1. Jon LaRosa

    Jon LaRosa Guest

    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
     
    Jon LaRosa, Jan 3, 2005
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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, Jan 4, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UmV6YQ==?=
    Replies:
    3
    Views:
    17,948
    Carlos Barini
    Jun 7, 2004
  2. Daves
    Replies:
    1
    Views:
    378
  3. Geoff
    Replies:
    3
    Views:
    447
    Patrick.O.Ige
    Jan 16, 2006
  4. farseer

    SQL Server 2005 + SQL Server Express

    farseer, Aug 7, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    347
    farseer
    Aug 8, 2006
  5. Grey Alien

    SQL Server 2000 vs SQL Server Express

    Grey Alien, Jul 8, 2007, in forum: ASP .Net
    Replies:
    6
    Views:
    496
    Grey Alien
    Jul 9, 2007
Loading...

Share This Page