Hi, I have am interesting problem that I cannot think of a solution for.

I have an VB.NET application. This Application is permanently resident in memory and minimized to the tray area because we use it very often. The problem comes in when we VPN into a client's site. These VPN connections trash all other network connections to and from our machines.
Then every time my VB.NET application refreshes its database, errors start occurring, because the machine has lost connectivity to the SQL server, however, these are not at the connection level, but only when the reader attempts to read.

I am checking for connection.open, but because of the SQL connection pooling, I just get a previous connection that was valid before the network was cut. Is there anyway to check if an actual SQL connection is valid, prior to re-issuing a connection from the pool?

I have created a separate app to test a solution to this. (below)
If there is no connection on the first attempt, I get an error, but if the first connection is valid and then I physically disconnect the LAN cable, I still get an alledged successful connection. I need to be sure that this connection is actually valid, or throw an exception that I can trap and handle prior to attemting transactions on the SQL server. If not, I will have to turn the pooling off, which will slow things down a bit :-(

Imports System.Data.SqlClient
Public Class Form1
Public Conn As New SqlConnection

Public Sub ConnDB()
        Try
            Conn.ConnectionString = "server = " & "wyvern" & "\" & ""
            Conn.ConnectionString = Conn.ConnectionString & "; Database=" & "Forge"
            Conn.ConnectionString = Conn.ConnectionString & ";integrated security=true"

            Conn.Open()   'This continues to work even after I disconnect the LAN cable

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Connection to SQL Server Failed!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Conn.Close()
        Finally

        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ConnDB()
        If Conn.State = 1 Then
            MsgBox("You have successfully connected to SQL Server!", 0, "Test Successful!")
        End If

        If Conn.State = ConnectionState.Open Then
            Conn.Close()
        End If
    End Sub
End Class

You could try adding SqlConnection.ClearPool(Conn) or SqlConnection.ClearAllPools before your open statement.

Hi, thanks Momerath, thats unfortunately all I could come up with as well.

I have changed my connection errors to show in a label rather, this way there are not a lot of popup errors happening while the network is unavailable.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.