"...database is in use" message when trying to restore

[DVader]

I am developing an application that will run on a notebook computer that as part of its functionality will need to refresh the local database with a version copied down from a network server. The database is in SQL Server Express 2005. When I test my code though I am getting an error message that says...

"Exclusive access could not be obtained because the database is in use."

As part of the process I need to check to see if there are any pending changes before I allow the user to refresh the local copy of the database. That is the highlighted code below in red.

Public Function RefreshLocalDatabase() As Boolean

        Dim strMessage As String = ""
        Dim bolPendingDataChanges As Boolean = False
        Dim bolPendingPhotosToUpload As Boolean = False
        Dim bolRefreshLocalDatabase As Boolean = False
        Dim bolErrorOccurred As Boolean = False

        Try
            --------------------------------------------------------
            -  First, check to see if there are any pending
            -  database changes . . .
            --------------------------------------------------------             
           [COLOR="Red"]bolPendingPhotosToUpload = PendingPhotosToUpload()[/COLOR]            
            --------------------------------------------------------
            -
            --------------------------------------------------------            
            If bolPendingPhotosToUpload = False Then
                ----------------------------------------------------
                -  If there are no pending changes then first make
                -  a backup copy of the LOCAL database . . .
                ----------------------------------------------------
                If LocalDatabaseBackupCreated() = True Then
                    If CopyProductionDatabaseToNotebook() = True Then
                        If Me.RestoreProductionDatabase = True Then
                            bolErrorOccurred = False
                        Else
                            bolErrorOccurred = True
                        End If
                    Else
                        bolErrorOccurred = True
                    End If
                Else
                    bolErrorOccurred = True
                End If
                If bolErrorOccurred = False Then
                    bolRefreshLocalDatabase = True
                Else
                    bolRefreshLocalDatabase = False
                End If
            Else
                bolRefreshLocalDatabase = False
                strMessage = "Unable to Refresh the Local database:" & vbNewLine & vbNewLine
                If bolPendingPhotosToUpload = True Then
                    strMessage = strMessage & " - There are still photos that need to be uploaded." & vbNewLine & vbNewLine
                End If
                MessageBox.Show(strMessage, "Refresh Local Database", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Catch ex As Exception
            bolRefreshLocalDatabase = False
            MessageBox.Show(ex.Message & vbCrLf & vbCrLf & ex.StackTrace, "RefreshLocalDatabase", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Return bolRefreshLocalDatabase

        MessageBox.Show("Done...RefreshLocalDatabase")

    End Function

If I comment that line of code the process runs to completion with no problem. If I dont I get the error. Heres the code Im using to check for pending changes...

Private Function PendingPhotosToUpload() As Boolean

        Dim connLocal As New SqlConnection
        Dim strSelect As String
        Dim bolPending As Boolean = False

        Dim cmdSelect As SqlCommand = Nothing
        Dim sqlDR As SqlDataReader = Nothing

        Try
            strSelect = "SELECT Count(*) AS PendingCount " & _
                        "FROM Segment WHERE PhotosAvailableInd = 1"

            connLocal = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=HR54GuardRail;Integrated Security=SSPI")

            connLocal.Open()

            cmdSelect = New SqlCommand(strSelect, connLocal)

            cmdSelect.CommandType = CommandType.Text

            sqlDR = cmdSelect.ExecuteReader
            If sqlDR.HasRows = True Then
                Do While sqlDR.Read
                    If Not sqlDR("PendingCount") Is DBNull.Value Then
                        If CInt(sqlDR("PendingCount")) > 0 Then
                            bolPending = True
                        Else
                            bolPending = False
                        End If
                    End If
                Loop
            Else
                bolPending = False
            End If
        Catch ex As Exception
            bolPending = False
            MessageBox.Show(ex.Message & vbCrLf & vbCrLf & ex.StackTrace, "PendingPhotosToUpload", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If Not (sqlDR Is Nothing) Then
                sqlDR.Close()
                sqlDR = Nothing
            End If
            If Not (cmdSelect Is Nothing) Then
                cmdSelect.Dispose()
                cmdSelect = Nothing
            End If
            If Not (connLocal Is Nothing) Then
                If Not (connLocal.State = ConnectionState.Closed) Then
                    connLocal.Close()
                End If
                connLocal.Dispose()
                connLocal = Nothing
            End If

        End Try

        Return bolPending

    End Function

So for some reason there is still a connection to the local database that is preventing the retore process to work. What I dont understand is what I am doing wrong with closing the database connection. Can anyone provide any help?

 

[DVader]

Okay, changing my SQL connection in the PendingPhotosToUpload function by eliminating the "Initial Catalog=HR54GuardRail;" reference cured the problem.

Now I have another question, when running the application on the notebook, I started SQL Server Management Studio and ran the sp_who2 stored procedure. In teh result set I see an entry for the database and a ProgramName of ".Net Sqlclient Data proivder". Ive checked my code and we are religiously closing and disposing of conections, so why should that entry be there?

 

[PlausiblyDamp]

If you have visual Studio open the server manager itself could be holding a connection open.