Server is locking uploaded excel file.

I am trying to create an page that allows users to upload excel file to sql server.
The problem I am running into is that after I use the button to display the contents of the excel file. The file is locked by the server. How can I get the server to release the file without restarting the website?

    Protected Function ExcelConnection() As OleDbCommand
        ' Connect to the Excel Spreadsheet
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("/uploaded/ExcelImport.xls") & ";" & _
              "Extended Properties=Excel 8.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

    Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As 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()

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

        ' Bind the data to the GridView
        GridViewExcel.DataSource = objDataSet.Tables(0).DefaultView
        GridViewExcel.DataBind()

    End Sub

Open in new window

LVL 2
stopher2475Asked:
Who is Participating?
 
käµfm³d 👽Commented:
I see you calling objXConn.Open(), but you never call objXConn.Close(). Why is that?
0
 
wdosanjosCommented:
As kaufmed pointed out. It seems to be a connection leak issue.
0
 
stopher2475Author Commented:
Thanks. I didn't know I could close the connection and still return the OleDbCommand.
Appreciate the help.

    Protected Function ExcelConnection() As OleDbCommand
        ' Connect to the Excel Spreadsheet
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("/uploaded/ExcelImport.xls") & ";" & _
              "Extended Properties=Excel 8.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)

        objXConn.Close()

        Return objCommand

    End Function

Open in new window

0
 
käµfm³d 👽Commented:
Technically, you don't even have to open it. DataAdapter handles the opening and closing of connections for you. I believe, though, that because you manually opened it prior to sending it to the DataAdapter, the DA left it in the same state it received it in. IIRC, you can remove both the Open() and Close() calls and the code should still function.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.