Creating user friendly error messages for SqlExceptions

17 Jun 2009 10:17 PM

Andy B.

I need to take the errors that sql server reports through SqlException and
turn them into user friendly messages since not everybody that will use the
software will know what all of the errors mean. How would I do something
like this?

18 Jun 2009 12:29 AM

Lloyd Sheen

"Andy B." <a_bo***@sbcglobal.net> wrote in message
news:e%23COol57JHA.5508@TK2MSFTNGP04.phx.gbl...
>I need to take the errors that sql server reports through SqlException and
>turn them into user friendly messages since not everybody that will use the
>software will know what all of the errors mean. How would I do something
>like this?
>

Andy,

    Since you know your application, what you need to do is capture the
error and then using the context of what your user is doing create the
appropriate error message.

    Example: Lets say your app allows the user to maintain products and
categories of products. There is a foreign key between product and
category. This would ensure that you cannot delete a category while
products of that category exist. The message you get back from SqlException
(just an estimate of the message) would say something like 'attempt to
delete row failed because of FK product_category'.

    Your app should know what the user is attempting and return to them a
user friendly message. You could do this by create a data layer which gets
called. This data layer would handle the error and then could throw another
error with a more user friendly message.

Hope this give you a start,
LS

18 Jun 2009 11:58 AM

Andy B.

Show quote Hide quote

"Lloyd Sheen" <a@b.c> wrote in message
news:eOS7tv67JHA.1568@TK2MSFTNGP06.phx.gbl...
>
> "Andy B." <a_bo***@sbcglobal.net> wrote in message
> news:e%23COol57JHA.5508@TK2MSFTNGP04.phx.gbl...
>>I need to take the errors that sql server reports through SqlException and
>>turn them into user friendly messages since not everybody that will use
>>the software will know what all of the errors mean. How would I do
>>something like this?
>>
>
> Andy,
>
>    Since you know your application, what you need to do is capture the
> error and then using the context of what your user is doing create the
> appropriate error message.
>
>    Example: Lets say your app allows the user to maintain products and
> categories of products. There is a foreign key between product and
> category. This would ensure that you cannot delete a category while
> products of that category exist. The message you get back from
> SqlException (just an estimate of the message) would say something like
> 'attempt to delete row failed because of FK product_category'.
>
>    Your app should know what the user is attempting and return to them a
> user friendly message. You could do this by create a data layer which
> gets called. This data layer would handle the error and then could throw
> another error with a more user friendly message.
>
> Hope this give you a start,
> LS
>
>

That's fine except the data layer has no clue what error to look for.
Neither would the business logic directly. The only way to do something like
this is to put the decisions on what to display for user friendly errors is
in the UI. I am looking for certain errors since not all of them apply to
the context. An example is:

try
'The bulk of the code to insert a headline

Headlines.Insert(Headline)
catch ex as SqlException
'The only things that can happen here are:
'1. Unique constraint violation (There can't be 2 or more headlines with the
exact same title)
2. The database is unavailable for whatever reason like network issues or
server problems.

'-- Process unique constraint and show friendly error if needed
'-- Process other issues needing possible attention (network, server) see #2
above).
end try

Would this be a better way of doing things?

18 Jun 2009 5:06 PM

Lloyd Sheen

Show quote Hide quote

"Andy B." <a_bo***@sbcglobal.net> wrote in message
news:eyr4mwA8JHA.1568@TK2MSFTNGP06.phx.gbl...
> "Lloyd Sheen" <a@b.c> wrote in message
> news:eOS7tv67JHA.1568@TK2MSFTNGP06.phx.gbl...
>>
>> "Andy B." <a_bo***@sbcglobal.net> wrote in message
>> news:e%23COol57JHA.5508@TK2MSFTNGP04.phx.gbl...
>>>I need to take the errors that sql server reports through SqlException
>>>and turn them into user friendly messages since not everybody that will
>>>use the software will know what all of the errors mean. How would I do
>>>something like this?
>>>
>>
>> Andy,
>>
>>    Since you know your application, what you need to do is capture the
>> error and then using the context of what your user is doing create the
>> appropriate error message.
>>
>>    Example: Lets say your app allows the user to maintain products and
>> categories of products. There is a foreign key between product and
>> category. This would ensure that you cannot delete a category while
>> products of that category exist. The message you get back from
>> SqlException (just an estimate of the message) would say something like
>> 'attempt to delete row failed because of FK product_category'.
>>
>>    Your app should know what the user is attempting and return to them a
>> user friendly message. You could do this by create a data layer which
>> gets called. This data layer would handle the error and then could throw
>> another error with a more user friendly message.
>>
>> Hope this give you a start,
>> LS
>>
>>
> That's fine except the data layer has no clue what error to look for.
> Neither would the business logic directly. The only way to do something
> like this is to put the decisions on what to display for user friendly
> errors is in the UI. I am looking for certain errors since not all of them
> apply to the context. An example is:
>
> try
> 'The bulk of the code to insert a headline
>
> Headlines.Insert(Headline)
> catch ex as SqlException
> 'The only things that can happen here are:
> '1. Unique constraint violation (There can't be 2 or more headlines with
> the exact same title)
> 2. The database is unavailable for whatever reason like network issues or
> server problems.
>
> '-- Process unique constraint and show friendly error if needed
> '-- Process other issues needing possible attention (network, server) see
> #2 above).
> end try
>
> Would this be a better way of doing things?
>

In your example you would put the code for Headline.Insert into a DAL. In
that code you would capture the SqlException, find out what the error was
and then return an error (raiseerror is one way but I think making the
Insert a function returning an error code would be easier to code than using
errors). When you get a certain error in your DAL you can then return to
the user a more friendly message.

If your DAL doesn't know what it is doing then you are on the wrong track.
This code is where all database handling is done. You can then code the
application (GUI) to use those classes and recieve from them meaningful (to
the user) messages.

The number of thing that can go wrong will be a function of the complexity
of the code and the database.

If you are using something like SQL Server you could also code against
stored procedures. The procedure which is resident on the database server
would then be responsible for returning meaningful messages.

LS