exception handling using SQLClient - is this the only exception that is caught???

[James Cooke]

Hi all,

I want to catch a duplicate key exception. I do not want to provide that
verbose message from the MSSQL server - I would put a user friendly message
out, like

"The item you have added already exists in the database. Please change
the description and try to save again."

instead of
System.Data.SqlClient.SqlException: Cannot insert duplicate key row in
object 'Tree' with unique index 'KeySiteParentTreeNodename'.
The statement has been terminated.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ClaPub.DataAccess.ExecuteCommand(String SQL, DataElements&
DataElements, BinaryDataElements& BinaryDataElements) in C:ClaPubClassesData
Access ClassesDataAccess.vb:line 129

How do I do this? because the only error captured by .net is the general
System.Data.SqlClient.SqlException. I have tried using
System.Data.DuplicateNameException but that is not caught.

Try
SqlConnection.Open()
m_ReturnValue = SqlCmdObj.ExecuteNonQuery()
SqlConnection.Close()
Catch e As System.Data.SqlClient.SqlException 'THIS IS CAUGHT
EVERY TIME
...
Catch e As System.Data.DuplicateNameException 'THIS IS NEVER
CAUGHT
...
Catch e As System.Data.NoNullAllowedException 'THIS IS NEVER
CAUGHT
...
End Try

 

[Armin Zingler]

I want to catch a duplicate key exception. I do not want to provide
that verbose message from the MSSQL server - I would put a user
friendly message out, like

"The item you have added already exists in the database. Please
change
the description and try to save again."

instead of
System.Data.SqlClient.SqlException: Cannot insert duplicate key
row in
object 'Tree' with unique index 'KeySiteParentTreeNodename'.
The statement has been terminated.
[...]

The Errors property of the SqlException object probably contains the
error(s). Maybe checking the Number property of an SQLError object helps.

BTW, there is a specialized group for ADO.Net questions:
microsoft.public.dotnet.framework.adonet

 

[William Ryan]

exc.Message will give you a cleaner name. You could also write your own
exception class with whatever message you like, then throw a YourException
from SQLException.

Incidentally, the second two will not get caught when you have SQLClient
exception at the top because everything that could come from ExecuteNonQuery
(just about) will be caught by the SqlException first. NoNullAllowed would
only happend if you tried to insert a null into a DataTable, not Database
talbe, where the column was marked allownull = false.

Your in different libraries, but remember, always go from specific to broad
exception catches b/c the broad ones, if they are first ,will catch
everything more precise.

HTH,

Bill