locked
Excel Upload and Import .Open Error RRS feed

  • Question

  • User623914705 posted

    Hi there,

    Following some standard tutorials out there, I created a page to 1) Upload an Excel SPreadsheet, 2) View the data in a Grid, and 3) import it into our database.

    The Upload always goes fine, and usually the View data works, but I keep getting this error when trying to open the Excel during the Import process:

    The Microsoft Jet database engine cannot open the file
    'D:\WebTest\Code\UPLOADED_FILE\MEETINGS\ANNUALMEETING\Confirmations\HotelConfirmations\'.
    It is already opened exclusively by another user, or you need permission to view
    its data

    On the line: OleDbConnection.Open() that would have just ran fine during the View step.

    I've literally spent a day Googling and trying out solutions, including give Everyone Full permission on the folder the file is being uploaded to and making sure all objects are properly disposed of.

    Any assistance will be gratefully appreciated,

    John

    P.S. Here's the CodeBehind:

    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    
    Partial Class ADMIN_new_PRIVATE_GENERAL_INFO_meetings_annual_meeting_reports_hotel_HotelReservationUpload
        Inherits System.Web.UI.Page
    
        Protected Sub ButtonUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonUpload.Click
            PanelUpload.Visible = True
            PanelView.Visible = False
            PanelImport.Visible = False
        End Sub
    
        Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
          Handles ButtonUploadFile.Click
    
            If FileUploadExcel.HasFile Then
                Try
                    FileUploadExcel.SaveAs(Server.MapPath("~/UPLOADED_FILE/MEETINGS/ANNUALMEETING/Confirmations/HotelConfirmations/" & FileUploadExcel.PostedFile.FileName)) '~/uploaded_file/meetings/annual_meeting/confirmations
                    LabelUpload.Text = "Upload File Name: " & _
                        FileUploadExcel.PostedFile.FileName & "<br>" & _
                        "Type: " & _
                        FileUploadExcel.PostedFile.ContentType & _
                        " File Size: " & _
                        FileUploadExcel.PostedFile.ContentLength & " kb<br>"
                    LabelImport.Text = FileUploadExcel.PostedFile.FileName
                    FileUploadExcel.Dispose()
                Catch ex As Exception
                    LabelUpload.Text = "Error: " & ex.Message.ToString
                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.Jet.OLEDB.4.0;" & _
                  "Data Source=" & Server.MapPath("~/UPLOADED_FILE/MEETINGS/ANNUALMEETING/Confirmations/HotelConfirmations/" & LabelImport.Text) & ";" & _
                  "Extended Properties=Excel 8.0;" 'Test.xls 
    
            ' 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 System.EventArgs) Handles ButtonView.Click
            PanelUpload.Visible = False
            PanelView.Visible = True
            PanelImport.Visible = False
    
            ' Create a new Adapter
            Dim objDataAdapter As New OleDbDataAdapter()
            Dim tempObject As OleDbCommand
            tempObject = ExcelConnection()
            ' retrieve the Select command for the Spreadsheet
            Try
                objDataAdapter.SelectCommand = tempObject
    
                ' 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()
                objDataAdapter.Dispose()
            Catch ex As Exception
                LabelUpload.Text = "Error: " & ex.Message.ToString
                LabelImport.Text = "Error: " & ex.Message.ToString
            Finally
                tempObject.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
    
            ' retrieve the Select Command for the worksheet data
            Dim objCommand As New OleDbCommand()
            Try
                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
    
                Dim connetionString As String
                Dim connection As SqlConnection
                Dim adapter As New SqlDataAdapter
                Dim dr As SqlDataReader
                Dim myCommand As SqlCommand
                Dim sql As String
                connetionString = "Server=10.8.16.30;User Id=AppAccess;pwd=App@Access9;DATABASE=db21310_test"
                connection = New SqlConnection(connetionString)
                connection.Open()
    
                Dim connection1 As SqlConnection
                Dim adapter1 As New SqlDataAdapter
                'Dim dr1 As SqlDataReader
                'Dim myCommand1 As SqlCommand
                'Dim sql1 As String
                connetionString = "Server=10.8.16.30;User Id=AppAccess;pwd=App@Access9;DATABASE=db21310_test"
                connection1 = New SqlConnection(connetionString)
                connection1.Open()
    
                While reader.Read()
                    counter = counter + 1 ' counter to exit early for testing...
    
                    ' set default values for loop
                    Dim firstname As String = Convert.ToString(reader(1))
                    Dim lastname As String = Convert.ToString(reader(2))
                    Dim company As String = Convert.ToString(reader(3))
                    Dim position As String = Convert.ToString(reader(4))
                    Dim email As String = Convert.ToString(reader(5))
                    Dim confirmation As String = Convert.ToString(reader(6))
    
                    myCommand = New SqlCommand("EXECUTE GetAnnualMeeting_HotelConfirmation_ByHotelConf '" & confirmation & "'", connection)
                    dr = myCommand.ExecuteReader
                    If Not dr.HasRows Then
                        sql = "EXECUTE InsertAnnualMeeting_HotelConfirmation '" & firstname & "', '" & lastname & "', '" & company & "', '" & position & "', '" & email & "', '" & confirmation & "'"
                        Try
                            adapter1.InsertCommand = New SqlCommand(sql, connection1)
                            adapter1.InsertCommand.ExecuteNonQuery()
                            LabelImport.Text &= "<font color=green>Record Imported: " & firstname & " " & lastname & ", " & company & ", " & position & ", " & email & ", " & confirmation & ".</font><br>"
                        Catch ex As Exception
                            LabelImport.Text &= ex.ToString & " <font color=red>Fail to Import: " & firstname & " " & lastname & ", " & company & ", " & position & ", " & email & ", " & confirmation & ".</font><br>"
                        End Try
                    Else
                        LabelImport.Text &= "<font color=red>Duplicate Record: " & firstname & " " & lastname & ", " & company & ", " & position & ", " & email & ", " & confirmation & ".</font><br>"
                    End If
                    dr.Close()
                End While
                reader.Close()
            Catch ex As Exception
                LabelImport.Text &= ex.ToString
            Finally
                objCommand.Dispose()
            End Try
    
        End Sub
    
    End Class
    Tuesday, August 9, 2011 5:04 PM

Answers

  • User1324895001 posted

    Maybe i didnt see it, but where are you closing the connection, i see where you are calling .Open(), but didnt see where you were closing, could explain why you get that message that its open still

    im using similar code to handle my uploading and dont have that issue with the file being open.

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 9, 2011 5:09 PM

All replies

  • User1324895001 posted

    Maybe i didnt see it, but where are you closing the connection, i see where you are calling .Open(), but didnt see where you were closing, could explain why you get that message that its open still

    im using similar code to handle my uploading and dont have that issue with the file being open.

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 9, 2011 5:09 PM
  • User3866881 posted

    'D:\WebTest\Code\UPLOADED_FILE\MEETINGS\ANNUALMEETING\Confirmations\HotelConfirmations\'.
    It is already opened exclusively by another user, or you need permission to view
    its data

    Hello:)

    This problem due to it that maybe your file is in use by some process.

    You should close connection at once when it's useless. Or just Use this:

    using (OleDbConnection……)

    {

        //Here do what you want.

    }

    Thursday, August 11, 2011 11:39 PM