How to identify an error?

  • Thread starter Thread starter DraguVaso
  • Start date Start date
D

DraguVaso

Hi,

I want my application do different actions depending on the exception it
gets.
For exemple: I have an SQL-table with a unique index. In case I try to
Insert a record that's alreaddy in it I get this exception: "Cannot insert
duplicate key row in object 'tblTelephones' with unique index
'UniqueValues'."

What I'm looking for is a way to identify the exception: in case I get this
exception I want to do this, in case of another I want to do that.

The most simple solution to me seems this:
Catch ex As Exception
If Left(ex.Message, 41) <> "Cannot insert duplicate key row in
object" Then
'do this action
End If

QAlthough, I'm not convinced this is the best way. Is there a way to
identify the exception with a unique number? Or I've seen once something
like a name for an error. Can anybody help me with this?

Thanks a lot in advance

Pieter
 
Hi,
If it is SQL error, you may catch System.Data.SqlClient.SqlException instead
of Exception.
In SqlException, you are having several properties such as Number that give
you the specific error.

So in the client side you may want to do something like that:
catch (SqlException exp)
{
if (exp.Number == 1801)
do action;
else if (exp.Number == 1802)
do action2;
else WriteErrorMessage ("Fail to do this op, SQL error desription" +
exp.Message);
}
catch (Exception exp)
{
WriteErrorMessage ("Fail to do this op, error desription" +
exp.Message);
}

You may also define your excpetions and do the following in the server side:
catch (SqlException exp)
{
if (exp.Number == 1801)
throw new MyException1();
else if (exp.Number == 1802)
throw new MyException2();
else throw;
}

And in the client side catch those exceptions and do the needed actions.

It is not a good idea to have if statement on the message string.
 
One way around this is to set the column seed to -1 and count down. This way
when the new records are inserted you will not get duplicates assuming your
records are all positive.

However, I would recommend that if possible you dump this idea and use
GUID's which are extremely reliable as far a uniqueness is concerned ( not
totally though ).

Regards - OHM
 
Thanks Tal! That was exactly what I needed!

Tal said:
Hi,
If it is SQL error, you may catch System.Data.SqlClient.SqlException instead
of Exception.
In SqlException, you are having several properties such as Number that give
you the specific error.

So in the client side you may want to do something like that:
catch (SqlException exp)
{
if (exp.Number == 1801)
do action;
else if (exp.Number == 1802)
do action2;
else WriteErrorMessage ("Fail to do this op, SQL error desription" +
exp.Message);
}
catch (Exception exp)
{
WriteErrorMessage ("Fail to do this op, error desription" +
exp.Message);
}

You may also define your excpetions and do the following in the server side:
catch (SqlException exp)
{
if (exp.Number == 1801)
throw new MyException1();
else if (exp.Number == 1802)
throw new MyException2();
else throw;
}

And in the client side catch those exceptions and do the needed actions.

It is not a good idea to have if statement on the message string.
Click to expand...
 
For reference, here is how I solved this problem. We had a requirement in a
multi-user system to identify the field that failed (since we can have more
than one unique index per table and it's nice to take the user to the
offending input box). We can't avoid the problem using GUIDs because the
value is human-enetered and if there's more than one user you can get
overlapping edits.

First of all it's best to catch the smallest set of exceptions -- so in this
case we want to catch SqlExceptions and take action on that.

Second, identify the type of error based on the SqlException.Number -- for
example, unique key violation is 2627, and foreign key violation is 547. You
can get these from SQL BOL.

Next is the kluge -- I want to know the table and field so that I can use it
further up the stack if I recognise it. I wrote a dispatcher class which
matches on a regex (eg "Violation of UNIQUE KEY constraint
'(?<CONSTRAINT>.*?)'"). I have one instance per type of violation (unique,
foreign, etc).

So basically, if I have a SqlException, I switch on number. Depending on the
number, I ask the appropriate dispatcher to handle the error message. The
dispatcher allows registration based on the name of the key and a function
to call (which normally throws a typed exception filled out with appropriate
parameters). Since our database generation scripts (and DA layer) are
generated from a single XML schema definition I know what the names of the
keys will be. The dispatcher uses the regex and compares against the
registered keys, and calls the given callback. If there's no match then the
exception is re-thrown and hopefully some generic exception handler gets it
(ie if it works nicely the user is taken to the field; if not then they get
some generic error message).

Pros:
+ I can add translations to specific exceptions only where I need
them -- everything else just bubbles up as usual.
+ Registration is the responsibility of individual business object
classes, not some central point.

Cons:
- Matches are based on human-readable strings, so different languages
and/or versions of the server could break it.

Hope that helps,
Stu
 
Stu,
Next is the kluge -- I want to know the table and field so that I can use it
further up the stack if I recognise it. I wrote a dispatcher class which
matches on a regex (eg "Violation of UNIQUE KEY constraint
'(?<CONSTRAINT>.*?)'"). I have one instance per type of violation (unique,
foreign, etc).
Click to expand...

Have you considered a stored procedure that intelligently checked the
columns and returned (via output parameters) the columns with the
duplicates?
parameters). Since our database generation scripts (and DA layer) are
generated from a single XML schema definition I know what the names of the
Click to expand...
Which may not be possible...

Hope this helps
Jay
 
Jay B. Harlow said:
Stu, use

Have you considered a stored procedure that intelligently checked the
columns and returned (via output parameters) the columns with the
duplicates?
Click to expand...

The main problem I can think of is atomicity -- are you suggesting something
like this (psuedo-code):

if( sp_checkinsert_foo v1, v2, v2 )
{
insert foo values v1, v2, v3
}

because if so an insert could happen after the check.

I guess an alternative would be to look into wrapping each insert/update
operation up into a correctly-transactioned, atomic test-and-set
stored-procedure... but to be honest I'm not sure it's worth it, as it would
require a serious re-work of our databse-generation stuff, plus the databse
access system.

However thanks for taking the time to think about these things!

Stu
 
Stu,
No I am suggesting:

insert_foo v1, v2, v3, out1, out2, out3

where insert_foo is a sp that does both the check & insert in an atomic
manner. out1, out2 & out3 are the necessary number of output parameters (1?)
to properly identify to the user the problem...

I understand that you need to use updlock locking hint and/or repeatable
read transaction isolation level so the check locks the records in the sp
long enough for the insert to work.

Hope this helps
Jay
 
Back
Top