0

I am writing a VB.net winforms application which connects to Sql server 2008. (C# answers also accepted)

I am trying to differentiate between any SqlExceptions related to the connection to the database and other SQL specific errors (such as incorrect syntax etc.)

I am using a try/catch around the connection like so:

Try
   //code to open the connection/access the DB/etc.
Catch ex As SqlException When ex.Number=???
   //Do stuff
End Try

I am presuming that checking the error code is the correct way to go. My issue is that there are a number of different error codes (i'm not sure how many) relating to the connection to the database. For example:

121 = Timeout (could be caused by disconnecting from the network).

53 = Could not connect (again, could be caused by not being connected to the network).

1326 = You are connected to the physical server machine but the Sql Server is not running.

And I am sure that there are many, many more. Do I have to check for each of these individually or is there a better way? If I have to check for them individually, is there a list of the connection related ones anywhere that I can refer to?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Gravitate
  • 2,885
  • 2
  • 21
  • 37
  • 3
    Then put the connection code into a different try/catch then the code that performs a query so you can distinguish between the two. – jdweng Aug 16 '17 at 09:28
  • As [this answer (C#)](https://stackoverflow.com/a/62688/6741868) suggests, catch the exception first, then look at `ex.Number`. A switch/case might be useful. As for list of all codes, it is a long long list, but [this](https://msdn.microsoft.com/en-us/library/cc645603.aspx) might help (you'll have to identify the sql related ones yourself). – Keyur PATEL Aug 16 '17 at 09:31
  • @jdweng Thanks. That's a great idea. Really simple! Care to put it as an answer so that I can accept it? – Gravitate Aug 16 '17 at 09:50
  • @KeyurPATEL Thanks, but I am not looking through that list of 35999+ exceptions to see which are connection related. – Gravitate Aug 16 '17 at 09:54

1 Answers1

0

As @jdweng Suggested in comments, I added a try/catch block around just the query. So that if an exception occurred, it couldn't be anything else but connection related.

So simple! Thanks again.

Gravitate
  • 2,885
  • 2
  • 21
  • 37