Hi, I have got the the following error when i'm trying to upload an excel file and store data into SQL SERVER.
The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
when i run on Visual Studio everything works without any errors. Once i deploy up to IIS 7 this error came out. But I can login using the userID and password stored in the database. What should I do to resolve this error? Thanks!
' Read the file and convert it to Byte Array
Dim filePath As String = FileUpload1.PostedFile.FileName
Dim filename As String = Path.GetFileName(filePath)
Dim ext As String = Path.GetExtension(filename)
Dim contenttype As String = String.Empty
'Set the contenttype based on File Extension
Select Case ext
Case ".xls"
contenttype = "application/vnd.ms-excel"
Exit Select
Case ".xlsx"
contenttype = "application/vnd.ms-excel"
Exit Select
End Select
If contenttype <> String.Empty Then
Dim fs As Stream = FileUpload1.PostedFile.InputStream
Dim br As New BinaryReader(fs)
Dim bytes As Byte() = br.ReadBytes(fs.Length)
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
ExcelConnection.Open()
Dim objCmdSelect As New OleDbCommand("Select AcadYr, Sem, Status, TUTGRP, ADM_NO, NAME, Assigned, Ward, Module1, Posting1, Start_Date1, End_Date1, Assigned2, Ward2, Module2, Posting2, Start_Date2, End_Date2, Assigned3, Ward3, Module3, Posting3, Start_Date3, End_Date3, Assigned4, Ward4, Module4, Posting4, Start_Date4, End_Date4, Assigned5, Ward5, Module5, Posting5, Start_Date5, End_Date5, Assigned6, Ward6, Module6, Posting6, Start_Date6, End_Date6, Assigned7, Ward7, Module7, Posting7, Start_Date7, End_Date7 FROM [Main$]", ExcelConnection)
Dim objDR As OleDbDataReader
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("database").ConnectionString()
Dim con As New SqlConnection(strConnString)
Dim SQLconn As New SqlConnection(strConnString)
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = "studentdata"
Try
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
objDR.Close()
SQLconn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
con.Close()
End Try
lblMessage.ForeColor = System.Drawing.Color.Green
lblMessage.Text = "File Uploaded Successfully"
End Using
Else
lblMessage.ForeColor = System.Drawing.Color.Red
lblMessage.Text = "File format not recognised." & " Please Upload Excel formats"
End If