Click here to Skip to main content
12,299,654 members (47,654 online)

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.

lowpw asked:

Open original thread
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
Tags: VB, VB.NET, ASP.NET

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).



Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100