Freeing up a locked Access DB

[iamguyster]

Hi,

 

I am currently writing an application that allows a user to upload an Access database to the webserver whereupon the application opens up a datareader and imports the data into a central SQL Server DB.

 

This all works well except once the procedure is complete the Access DB on the server is locked up. This means I can no longer import databases of the same name.

 

In the code I am closing the Datareader, closing the connection to the Access DB, calling Dispose() on the command and setting the command to nothing. But the ldb file remains there and the database cannot be overwritten or deleted.

 

Has anyone any idea on what I can do to free up the resource after I have used it.

 

Currently the only way I can free up that resource is by rebuilding the solution!!

 

Help!!

 

Cheers,

IamGuyster

[Robby]

There must be an object latching onto the db since the ldb is still there, can you post the relative code?

[Volte]

Have you got your database opened in the .NET Server Explorer window? That will create a lock-file as well.

[iamguyster]

The Code!

 

Hello,

 

I am sure that there is nothing using the DB connection and I have closed everything...but then maybe I am missing something simple. Anyway, here is the code....it's not the most elegant but it works!! I have edited out a lot of setting of parameters, comments and the like so that I can actually post this (it was too long otherwise)

 

       If IsPostBack Then

           strFolder = "S:\WebApps\HMHW\Import\UploadedDB\"

           strFileName = txtFile.PostedFile.FileName
           strFileName = System.IO.Path.GetFileName(strFileName)
           strFilePath = strFolder & strFileName
           txtFile.PostedFile.SaveAs(strFilePath)

           Dim oCmd As OleDb.OleDbCommand
           Dim oDR As OleDb.OleDbDataReader
           Dim oDR2 As SqlClient.SqlDataReader
           Dim strSQL As String
           Dim strConn As String
           cnHMHW.Open()

           strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";User ID=;Password=;"

           strSQL = "SELECT * FROM import_DB_Buildings"
           oCmd = New OleDb.OleDbCommand
           With oCmd
               .Connection = New OleDb.OleDbConnection(strConn)
               .Connection.Open()
               .CommandType = CommandType.Text
               .CommandText = strSQL
               oDR = .ExecuteReader()
           End With

           Do While oDR.Read()

               'If not, enter it into the tblBuildingsTemp
               sBuildingsID = oDR.Item("Bldg_Code")

              cmdBuildingExists.Parameters("@BuildingID").Value = New Guid(sBuildingsID)
               cmdBuildingExists.ExecuteNonQuery()
               If cmdBuildingExists.Parameters("@RETURN_VALUE").Value = 1 Then
                Else
                              With cmdAddTempBuilding
                       .Parameters("@BuildingID").Value = New Guid(sBuildingsID)
                       .ExecuteNonQuery()
                   End With
               End If
           Loop


           strSQL = "SELECT * FROM import_DB_POC"
           oCmd = New OleDb.OleDbCommand
           With oCmd
               .Connection = New OleDb.OleDbConnection(strConn)
               .Connection.Open()
               .CommandType = CommandType.Text
               .CommandText = strSQL
               oDR = .ExecuteReader()
           End With

           Do While oDR.Read
               sSFORID = oDR.Item("SFOR_ID")

               cnSecurityAdmin.Open()
               With cmdGetRankGUID
                   .Parameters("@RankName").Value = sRank
                   oDR2 = .ExecuteReader()
                   Do While oDR2.Read
                       gRankID = oDR2.Item("RankGUID")
                   Loop
               End With
               cnSecurityAdmin.Close()

               With cmdImportPOC
                   .Parameters("@prmLastName").Value = sLastName
                   .ExecuteNonQuery()
               End With
           Loop


           strSQL = "SELECT * FROM import_DB_Materials"
           oCmd = New OleDb.OleDbCommand
           With oCmd
               .Connection = New OleDb.OleDbConnection(strConn)
               .Connection.Open()
               .CommandType = CommandType.Text
               .CommandText = strSQL
               oDR = .ExecuteReader()
           End With

           Do While oDR.Read
              gMaterialsID = oDR.Item("@MaterialsID")

               With cmdGetImportMaterialIDs

                   .Parameters("@LookUpType").Value = 1
                   oDR2 = .ExecuteReader
                   Do While oDR2.Read
                       gBAVID = oDR2.Item("BAVID")
                   Loop
                   oDR2.Close()

                   .Parameters("@LookUpType").Value = 5
                   oDR2 = .ExecuteReader
                   Do While oDR2.Read
                       gRDRID = oDR2.Item("RDRID")
                   Loop
                   oDR2.Close()
                   With cmdGetMaterialPOCImport
                       .Parameters("@BuildingID").Value = New Guid(sBuildingsID)
                       oDR2 = .ExecuteReader
                       Do While oDR2.Read
                           gPOCID = oDR2.Item("POCID")
                       Loop
                       oDR2.Close()
                   End With
               End With

               With cmdImportMaterial
                   .Parameters("@MaterialsID").Value = gMaterialsID
                   .ExecuteNonQuery()
               End With
           Loop

           strSQL = "SELECT * FROM import_DB_Spills"
           oCmd = New OleDb.OleDbCommand
           With oCmd
               .Connection = New OleDb.OleDbConnection(strConn)
               .Connection.Open()
               .CommandType = CommandType.Text
               .CommandText = strSQL
               oDR = .ExecuteReader()
           End With

           Do While oDR.Read
               sBuildingsID = oDR.Item("Bldg_Code")
               gBuildingID = New Guid(sBuildingsID)
               With cmdGetMaterialPOCImport
                   .Parameters("@BuildingID").Value = gBuildingID
                   oDR2 = .ExecuteReader
                   Do While oDR2.Read
                       gPOCID = oDR2.Item("POCID")
                   Loop
                   oDR2.Close()
               End With
               sSurfaceID = oDR.Item("Surface")

               With cmdGetImportMaterialIDs
                   .Parameters("@LookUpType").Value = 6
                   oDR2 = .ExecuteReader
                   Do While oDR2.Read
                       gSurfaceID = oDR2.Item("SurfaceID")
                   Loop
                   oDR2.Close()
               End With
               dRecordCreated = oDR.Item("Record_Created")
               dSpillDate = oDR.Item("Date")
               sMaterialNameEng = oDR.Item("Material_Name")
               dQty = oDR.Item("Qty")
               sActions = oDR.Item("Actions")

               With cmdImportSpill
                   .Parameters.Item("@BuildingsID").Value = gBuildingID
                   .ExecuteNonQuery()
               End With
           Loop

           strSQL = "SELECT * FROM import_DB_Questionnaire"
           oCmd = New OleDb.OleDbCommand
           With oCmd
               .Connection = New OleDb.OleDbConnection(strConn)
               .Connection.Open()
               .CommandType = CommandType.Text
               .CommandText = strSQL
               oDR = .ExecuteReader()
           End With

           Do While oDR.Read
               dRecordCreated = oDR.Item("Record_Created")
               sPOCEmail = SwapNull(oDR.Item("POC_Email"))
               'Get POCID base on email address!!
               With cmdGetPOCByEmail
                   .Parameters("@Email").Value = sPOCEmail
                   oDR2 = .ExecuteReader
                   Do While oDR2.Read
                       gPOCID = oDR2.Item("POCID")
                   Loop
                   oDR2.Close()
               End With
               sQ1 = SwapNull(oDR.Item("Q1"))
               With cmdImportQuestionnaire
                   .Parameters.Item("@RecordCreated").Value = dRecordCreated
                   .Parameters.Item("@POCID").Value = gPOCID
                   .ExecuteNonQuery()
               End With
           Loop

           With cmdUpdateExport
               .Parameters("@LastExport").Value = Now()
               .ExecuteNonQuery()
           End With

           cnHMHW.Close()
           cnREAT.Close()

           cmdAddTempBuilding.Connection.Dispose()
           cmdBuildingExists.Connection.Dispose()
           cmdGetImportMaterialIDs.Connection.Dispose()
           cmdGetMaterialPOCImport.Connection.Dispose()
           cmdGetPOCByEmail.Connection.Dispose()
           cmdGetRankGUID.Connection.Dispose()
           cmdImportMaterial.Connection.Dispose()
           cmdImportPOC.Connection.Dispose()
           cmdImportQuestionnaire.Connection.Dispose()
           cmdImportSpill.Connection.Dispose()
           cmdUpdateExport.Connection.Dispose()

           cmdAddTempBuilding.Dispose()
           cmdBuildingExists.Dispose()
           cmdGetImportMaterialIDs.Dispose()
           cmdGetMaterialPOCImport.Dispose()
           cmdGetPOCByEmail.Dispose()
           cmdGetRankGUID.Dispose()
           cmdImportMaterial.Dispose()
           cmdImportPOC.Dispose()
           cmdImportQuestionnaire.Dispose()
           cmdImportSpill.Dispose()
           cmdUpdateExport.Dispose()

           cmdAddTempBuilding = Nothing
           cmdBuildingExists = Nothing
           cmdGetImportMaterialIDs = Nothing
           cmdGetMaterialPOCImport = Nothing
           cmdGetPOCByEmail = Nothing
           cmdGetRankGUID = Nothing
           cmdImportMaterial = Nothing
           cmdImportPOC = Nothing
           cmdImportQuestionnaire = Nothing
           cmdImportSpill = Nothing
           cmdUpdateExport = Nothing

           oDR.Close()
           oCmd.Connection.Dispose()
           oCmd.Connection.Close()
           oCmd.Dispose()
           oCmd = Nothing

       End If

Thanks for your help,

IAmGuyster

Edited October 17, 2003 by Robby

[Robby]

This is to followup on VoltFaces' idea, are you running this from the IDE or by entering the LocalHost URL in your browser?

[iamguyster]

I am building the solution and running it from a separate development webserver. The Access DB is opened by nothing at all except the application itself. The DB is not locked until I run the page. This is what confuses me...the only way it can be locked is for the application to still be using the resource, but I'm closing everything and setting everything to nothing and having no luck!

[iamguyster]

*bump*

 

Any ideas, anyone? I've been going through the code over and over and get the feeling I am missing something really basic.

 

Cheers,

IAmGuyster

[iamguyster]

I have it!!!

 

OK,

 

I figured it out so here it is for anyone who is having the same problem.

 

In my code I created an instance of the datareader (oDR) several times. I assumed (incorrectly) that each time the same instance of this object would be used. Not the case!

 

So, when I disposed of the oDR resources and set it nothing at the end of the code I was actually only doing this for one instance of the datareader.

 

To get the application to free up the database resource I had to explicitly dispose the oDR resources and set it to nothing each separate time I used it.

 

You live and learn!!

 

Thanks for your time,

IAmGuyster:D