Click here to Skip to main content
15,068,645 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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
Posted
Updated 22-Feb-11 18:16pm
v3
Comments
Prerak Patel 22-Feb-11 23:15pm
   
where is the code?
lowpw 23-Feb-11 0:16am
   
i have posted the codes. Tks!

1 solution

this[^] might help you to solve your problem.
   
Comments
lowpw 22-Feb-11 23:44pm
   
I forgot to add on actually when i run on Visual Studio everything works without any errors. Once i deploy up to IIS 7 this error came out. Something weird is i could login using the userID and password stored in the database.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900