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
