File being locked by another process

[Jackxxx]

I upload an excel file and view it in a gridview. If I try to upload the file again I get an error that the file is being used by another process.

How can I stop this, so I can upload the file again without the wait time? The file is always the same name.

Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonUploadFile.Click

If FileUploadExcel.HasFile Then
Try
' alter path for your project
FileUploadExcel.SaveAs(Server.MapPath("~/Import_Process/ExcelImport.xls"))
LabelUpload.Text = "Upload File Name: " & _
FileUploadExcel.PostedFile.FileName & "<br>" & _
"Type: " & _
FileUploadExcel.PostedFile.ContentType & _
" File Size: " & _
FileUploadExcel.PostedFile.ContentLength & " kb<br>"
Catch ex As Exception
LabelUpload.Text = "Error: " & ex.Message.ToString
Finally
FileUploadExcel.Dispose()
End Try
Else
LabelUpload.Text = "Please select a file to upload."
End If

End Sub
Protected Function ExcelConnection() As OleDbCommand

' Connect to the Excel Spreadsheet
Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Server.MapPath("~/Import_Process/ExcelImport.xls") & ";" & _
"Extended Properties=Excel 8.0;" 'HDR=NO;IMEX=1"

' create your excel connection object using the connection string
Dim objXConn As New OleDbConnection(xConnStr)
objXConn.Open()

' use a SQL Select command to retrieve the data from the Excel Spreadsheet
' the "table name" is the name of the worksheet within the spreadsheet
' in this case, the worksheet name is "Members" and is coded as: [Clients$]
Dim objCommand As New OleDbCommand("SELECT * FROM [SHEET$]", objXConn)
Return objCommand

'objXConn.Close()
'objXConn = Nothing
'objCommand.Dispose()
'xConnStr = Nothing

End Function

Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonView.Click
PanelUpload.Visible = False
PanelView.Visible = True
PanelImport.Visible = False
' Create a new Adapter
Dim objDataAdapter As New OleDbDataAdapter()

' retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = ExcelConnection()

' Create a DataSet
Dim objDataSet As New DataSet()

Try
' Populate the DataSet with the spreadsheet worksheet data
objDataAdapter.Fill(objDataSet).ToString()

' Bind the data to the GridView
GridViewExcel.DataSource = objDataSet.Tables(0).DefaultView
GridViewExcel.DataBind()
Catch ex As Exception
LabelGrid.Text = ex.Message
Finally
'clear the adapter so we can upload the file again.
objDataAdapter.Dispose()
objDataSet.Dispose()
ExcelConnection.Dispose()
End Try
End Sub
Protected Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonImport.Click
PanelUpload.Visible = False
PanelView.Visible = False
PanelImport.Visible = True
LabelImport.Text = "" ' reset to blank
End Sub
Protected Sub btnProcessImport_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Try
' retrieve the Select Command for the worksheet data
Dim objCommand As New OleDbCommand()
objCommand = ExcelConnection()
' create a DataReader
Dim reader As OleDbDataReader
reader = objCommand.ExecuteReader()


Dim counter As Integer = 0 ' used for testing your import in smaller increments

While reader.Read()
counter = counter + 1 ' counter to exit early for testing...

Dim bTransTrue As Boolean
bTransTrue = False
Dim tr As SqlTransaction = Nothing

Dim myNewID As Long
Dim cmdInsert As SqlCommand
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
cmdInsert = New SqlCommand
cmdInsert.CommandText = "AddNewBatchUpload"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)


If String.IsNullOrEmpty(Convert.ToString(reader("Client_ID"))) Then
cmdInsert.Parameters.AddWithValue("@Client_ID", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@Client_ID", Convert.ToString(reader("Client_ID")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("FirstName"))) Then
cmdInsert.Parameters.AddWithValue("@FirstName", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@FirstName", Convert.ToString(reader("FirstName")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("LastName"))) Then
cmdInsert.Parameters.AddWithValue("@LastName", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@LastName", Convert.ToString(reader("LastName")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("orgname"))) Then
cmdInsert.Parameters.AddWithValue("@OrgName", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@OrgName", Convert.ToString(reader("orgname")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("Address"))) Then
cmdInsert.Parameters.AddWithValue("@Address", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@Address", Convert.ToString(reader("Address")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("City"))) Then
cmdInsert.Parameters.AddWithValue("@City", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@City", Convert.ToString(reader("City")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("State"))) Then
cmdInsert.Parameters.AddWithValue("@State", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@State", Convert.ToString(reader("State")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("zip"))) Then
cmdInsert.Parameters.AddWithValue("@zip", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@zip", Convert.ToString(reader("zip")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("phone"))) Then
cmdInsert.Parameters.AddWithValue("@phone", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@phone", Convert.ToString(reader("phone")))
End If
If String.IsNullOrEmpty(Convert.ToString(reader("email"))) Then
cmdInsert.Parameters.AddWithValue("@email", DBNull.Value)
Else
cmdInsert.Parameters.AddWithValue("@email", Convert.ToString(reader("email")))
End If


Try
cmdInsert.Connection.Open()
tr = cmdInsert.Connection.BeginTransaction
cmdInsert.Transaction = tr
myNewID = cmdInsert.ExecuteScalar()
bTransTrue = True
tr.Commit()
If bTransTrue Then
lblImportMessage.Text = "Your Import is Complete."
End If
Catch ex As Exception
lblImportMessage.Text = ex.Message
Finally
cmdInsert.Connection.Close()
End Try
End While
reader.Close()
objCommand.Dispose()
ExcelConnection.Dispose()
Catch ex As Exception
lblImportMessage.Text = ex.Message
End Try

End Sub

 

[ca8msm]

You need to make sure that you are properly disposing of any objects that interact with the excel file, otherwise you will see that they still retain the lock on the file.


Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[jmeckley]

since you are opening and closing so many connections on the page within separate functions I would recommend the [tt]using[/tt] block, instead of try/catch/finally. it ensures disposable objects are disposed of properly and cleans up the code.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259