locked
Connection to Azure SQL Server fails with "A first change exeption of type 'System.Data,SqlClient.SqlException' occured in System.Data.dll RRS feed

  • Question

  • I'm using for my application SQL Server database in Azure cloud and want to catch if connection to database failed.

    I built a small checkup function to check connection and catch the exception, but when I run it, the exception doesn't work and I keep getting system error instead of the exception message box.

    here's  my code:

            Dim strConn As String = "Server=server_name,1433;Initial Catalog=db_name;Persist Security Info=False;User ID=user_id;Password=password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
            Dim conn As New SqlConnection(strConn)
            Try
                conn.Open()
                conn.Close()
                conn.Dispose()
            Catch ex As System.Data.SqlClient.SqlException
                MsgBox(Err.Number)
                Exit Sub
            End Try

    The code runs and when it gets to "Conn.Open()" it shows the system error shown in the subject, instead of the message box in the "Catch" routine.

    Can anyone help me solving it?

    Thanks in advance.

    Amit

    Saturday, July 29, 2017 5:59 AM

All replies

  • Hello,

    Not knowing if you are using classic or current Azure Portal here goes.

    1. Make sure you have set firewall settings (see figure 1). This is very important.
    2. Ensure you have the correct user name and password. If the password is incorrect or unsure, reset the password by selecting from overview, click server name link (see figure 2), select "reset password" at top.

    Figure 1

    Figure 2

    This is all the code you need

    Using cn As SqlConnection = New SqlConnection With {.ConnectionString = connectionString}
    	Try
      	   cn.Open()
    	   MessageBox.Show("Open")
    	Catch ex As Exception
    	   MessageBox.Show($"Connect error: {ex.Message}")
    	End Try
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, July 29, 2017 9:45 AM
  • So why don't you just put a catch Exception to catch all exceptions after the SQL Exception so you know what the exception is? 
    Saturday, July 29, 2017 10:55 AM
  • Dear Karen,

    thanks for your lead.

    The problem is that in one computer I can connect to the database without any exceptions, and in another computer I have problems sometimes to connect. And when I get problems connecting to the Azure database, I get a message box not the one I added to the Try...Catch code I  wrote to test the connection, but a Visual Studio message box.

    Why the application ignores my Catch statements?

    Amit

    Saturday, July 29, 2017 2:40 PM
  • In my code I added Catch to catch the exceptions, but when I'm debugging the code (see below), the code doesn't get the catch statement.

    here's my code:

    Dim mySelectQuery As String = "SELECT column1 FROM table1"
    Dim myConnection As New SqlConnection(strConn)
    Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
    Try
        myCommand.Connection.Open()
    Catch e As SqlException
        Dim errorMessage As String = "Message: " & e.Message & vbCrLf & _
                                     "Source: " & e.Source & vbCrLf & _
                                     "State: " & e.State & vbCrLf & _
                                     "Procedure: " & e.Procedure & vbCrLf
        Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog()
        log.Source = "My Application"
        log.WriteEntry(errorMessage)
        Console.WriteLine("An exception occurred. Please contact your system administrator.")
    End Try


    Where strConn is a string with my connection string.

    The problem is not with the connection string, because sometimes I get connections and sometimes I don't get connection. All I want to do is use the Catch statements to catch this exception.


    • Edited by KareninstructorMVP Saturday, July 29, 2017 2:50 PM placed code into code block. Please always use code blocks
    Saturday, July 29, 2017 2:45 PM
  • Your try/catch is limiting

    Try
        ' expected failure
    Catch sqlEx As SqlClient.SqlException
        ' specific type of exception
        MessageBox.Show($"Sql exception: {sqlEx.Message}")
    Catch ex As Exception
        ' all other exceptions
        MessageBox.Show($"Sql exception: {ex.Message}")
    End Try
    Azure databases are meant really to connection through a layer where all clients go through the same ip address rather than having firewall rules for multiple clients.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, July 29, 2017 2:47 PM
  • Hi Amit1963,

    You can follow the Kareninstructor's suggestion to catch all exception, and post error message here.

    By the way here is use of SQL Azure step by step guide,  you can check Azure configuration。

    https://www.sqlshack.com/working-with-visual-studio-and-sql-azure-databases/

    http://www.c-sharpcorner.com/UploadFile/8ef97c/how-to-use-sql-azure-in-visual-studio-step-by-step-guide/

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, July 31, 2017 5:21 AM