Solved

Server is locking uploaded excel file.

Posted on 2011-02-25
4
404 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:stopher2475
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 34983163
I see you calling objXConn.Open(), but you never call objXConn.Close(). Why is that?
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34983239
As kaufmed pointed out. It seems to be a connection leak issue.
0
 
LVL 2

Author Comment

by:stopher2475
ID: 34983848
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34983904
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question