How to handle SqlException

Question

I've got some SQL handling code in C#, which can throw a number of exceptions when handling UNION clauses. I would like to detect which particular exception is being thrown so as to handle it accordingly. When testing my application, these two errors are thrown:

enter image description here

How can I differentiate between each one and recognise which one was thrown? I cannot simply copy/paste the error message and place it in an if-statement as the field names and schema will not always be the same.

try
 {
   adapter.Fill(dtResults);
 }
 catch (SqlException ex)
 {
 }

score 3 · Accepted Answer · answered Mar 16 '13 at 14:29

3

The SqlException you are catching has rich information on it such as the error number. This allows you to handle the conditions individually:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.number.aspx

answered Mar 16 '13 at 14:29

bryanmac

38,941
11
91
99

1

@DotNET: You should have mentioned(or byranmac) that the number is just the wrapper around the **first** exception. You can loop `ex.Errors` as shown in the link above. That might be the reason why you got the same number on both errors. – Tim Schmelter Mar 16 '13 at 14:42

score 2 · Answer 2 · answered Mar 16 '13 at 14:30

2

Inside your catch statement you can use:

   if (ex.ToLower.Contains("conversion failed")
       MessageBox.Show("Error: a conversion failed");
   if (ex.ToLower.Contains("multi-part identifier")
       MessageBox.Show("this is the other error!");

Just one method of many to get around this issue :)

answered Mar 16 '13 at 14:30

Mike Baxter

6,868
17
67
115

2

Not a good approach. Now your code unnecessarily relies on the language(maybe even on the db-version since the messages are not set in stone). – Tim Schmelter Mar 16 '13 at 14:39
This was the only method I knew to catch specific errors. I was just providing an alternate way to achieving the same result - now I realise that there are better ways of doing it. Answering questions is a learning experience too. – Mike Baxter Mar 16 '13 at 14:52

score 1 · Answer 3 · edited May 23 '17 at 10:30

1

The SqlException has a Number property (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.number.aspx) that you can check.

Take a look at this answer for details, it seems to be the same: https://stackoverflow.com/a/6222003/1758762

edited May 23 '17 at 10:30

Community

1
1

answered Mar 16 '13 at 14:29

Leo Chapiro

13,678
8
61
92

How to handle SqlException

3 Answers3