Solved

Server is locking uploaded excel file.

Posted on 2011-02-25
4
403 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

839 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