"...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 don't I get the error. Here's the code I'm 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 don't 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". I've 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.