none
Open method of system.data.sqlclient sqlconnection not consistent RRS feed

  • Question

  •  

    Hello all, I have a bit of a mystery with the open method of a sqlconnection object.  I am using VB2008 SP1 and the 3.5 SP1 .Net Framework.  I have two forms where I copy and pasted a section of code used to test connectivity that are giving me different results.  Basically, I have the startup form that loads and reads the database connection information from the registry and uses this code to test database connectivity.  I have a work around for this by adding a sqlcommand to select 1 and testing the connection that way but I am one of those people that like to know why things are the way they and would like to understand the behavior I am seeing.  I should also mention that in terms of VB and .Net programming I am a novice at best so try to overlook any obviously incorrect coding practices.

     

    'This code block is from my main form, Main1.

    Dim cn As New LoadRegistry 'Custom class to pull registry settings for my application

    Dim frmSplash As New SplashScreen 'Yep, its a splash screen

    frmSplash.Show()

    frmSplash.Refresh()

    cn.LoadDatabaseRegistry() 'Method that loads the database connection setting from the registry

    repwiseConnectionString = cn.ConnectionString 'repwiseconnectionstring is a friend string variable from this form

    Dim objConn As SqlConnection = New SqlConnection(repwiseConnectionString)

     

    Try

    Me.Cursor = Cursors.WaitCursor

    objConn.Open()

    objConn.Close()

    Catch ex As Exception

    MsgBox(ex.Message, MsgBoxStyle.Critical, "Error Connecting to SQL Server")

    Finally

    Me.Cursor = Cursors.Default

    objConn.Dispose()

    End Try

     

    When I run the above code if the SQL server is not available an exception is thrown and I get the message box, I have stepped through this in debug and see this happening.  I did this just to make sure that is where I was getting my message box.  This is what I want to happen, now here is where it stops making sense. I have the following section of code in a different form.

     

    'This code block executes when a OK command button is clicked in my SystemSetting form 

    Dim mboolTestConnection As Boolean 'Using this so I can know to start updating the system settings the user changed.

    mboolTestConnection = False

    Dim objConn As SqlConnection = New SqlConnection(Main1.repwiseConnectionString) 'Here is where I pull the friend var

     

    Try

    Me.Cursor = Cursors.WaitCursor

    objConn.Open()

    objConn.Close()

    mboolTestConnection = True

    Catch ex As Exception

    MsgBox("Unable to connect to SQL. your changes will not be saved." & vbCrLf & ex.Message, MsgBoxStyle.Critical, "Connection Broken")

    Finally

    Me.Cursor = Cursors.Default

    objConn.Dispose()

    End Try

     

    Now, unless I am just missing something there is no fundemental difference in these two code blocks, however if I stop my sql server after I have the application loaded and the System Setting form open to simulate the connection being unavailable it runs straight through and sets my boolean to true and begins the update routine that follows.  I have tried changing the name of the sqlconnection object and that didn't help.  I have tested the value of main1.repwiseConnectionString and it is correct and matches exactly what is used in the first routine that runs on application startup.  I have tried using .tostring to force a string just in case and that did nothing either.  Now, just to add to the wierdness, if I change main1.repwiseConnectionString to be the actual connection string or if I create a new registry object and assign a new string variable to that objects connection string the routine throws the exception it only fails to throw the exception when the connectionstring is equal to main1.repwiseconnectionstring.  To add to my confusion, a different class module runs the exact same routine as the SystemSettings form and it works with the same(correct in my opinion) behavior as the startup form and throws the exception when the connection is not available.

     

    Does anyone have any idea of what would cause this behavior?  Again, I have a better solution in place so this is not holding up my development but I would really love to know what is going on under the hood to make this happen.

     

    Thanks in advance,

    Cory

    Wednesday, October 8, 2008 3:31 AM