'The process cannot access the file because it is being used by another process' with FileUpload con

[sirnose1]

The code does what I want when it first runs, moves data from spreadsheet to excel. However when I attempt to load it again using the fileupload control, I get an error "The process cannot access the file. because it is being used by another process." I have tried different things but it still errors out. Any help would be appreciated. Here is the code:


If fuGroupAwarenessXLS.HasFile Then
Dim strAttachmentPath As String = Application("RootPath") & "/UPLOADED_FILE/GROUP_GROWTH/"
Dim strFileName As String = Path.GetFileName(fuGroupAwarenessXLS.FileName)
strFileName = strFileName.Replace("'", "")

fuGroupAwarenessXLS.SaveAs(strAttachmentPath & "/" & strFileName)
fuGroupAwarenessXLS.Dispose()

Dim gv1 As New GridView
Dim objDa As New OleDbDataAdapter()
objDa.SelectCommand = ExcelConnection()
Dim objDs As New DataSet()
objDa.Fill(objDs)
gv1.DataSource = objDs.Tables(0).DefaultView
gv1.DataBind()

End If

Protected Function ExcelConnection() As OleDbCommand

' Connect to the Excel Spreadsheet
Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("~/UPLOADED_FILE/GROUP_GROWTH/" & fuGroupAwarenessXLS.PostedFile.FileName) & ";Extended Properties=Excel 12.0;"
' 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 expressed as: [Members$]
Dim objCommand As New OleDbCommand("SELECT * FROM [Sheet1$]", objXConn)

Return objCommand

End Function

 

[MarkSweetland]

You'll need to close the connection in the command object. That is, you're opening the connection and doing a SELECT and then returning the OLEDBCommand object to bind to the Gridview, but you never close the connection nor delete the file (which will error out if a file with the same name is uploaded (file exists)).

I have done this with Excel files in the past-- open the connection and SELECT the records and store in a DataTable in ViewState, closed all connections, and delete the uploaded file before I worked with the content. You'll also have to watch for exceptions and probably put the clean up in a try...finally.




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[sirnose1]

Thanks! Closing the connection was part of the problem. I didnt have to delete the file, but what I did was add "fuGroupAwarenessXLS.PostedFile.InputStream.Dispose()".