Click here to Skip to main content
15,117,830 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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.



in the following code it gives error at
excelConnection.Open();


What I have tried:

Database table contain following columns
1)Id
2)name
Designation



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace WebApplication5
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnSend_Click(object sender, EventArgs e)
        {
            String strConnection = "Data Source=DESKTOP-HL3PK63;Initial Catalog=Demo;Integrated Security=True";
            //file upload path
            string path = fileuploadExcel.PostedFile.FileName;
            //Create connection string to Excel work book
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
            //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection);
            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            //Give your Destination table name
            sqlBulk.DestinationTableName = "Excel_table";
            sqlBulk.WriteToServer(dReader);
            excelConnection.Close();
        }
    }
}
Posted
Updated 9-Aug-17 5:58am
v2

Try adding Mode=Read to only open as read only;

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Mode=Read;Extended Properties=Excel 12.0;Persist Security Info=False";
   
Comments
paul_vin 6-Aug-17 1:48am
   
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Mode=Read;Extended Properties=Excel 12.0;Persist Security Info=False";

Even after trying Mode=Read it giving error
as
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.
Michael_Davies 6-Aug-17 1:54am
   
Are you sure it is the excelConnection.Open giving the fault then, as the error seems to be related to the Access DB, there is no file name in the strConnection, just what looks like the network name of a PC and the error shows the file name as '' which is no name.
Quote:
string path = fileuploadExcel.PostedFile.FileName;
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

Your code is running on the server.

The FileName property returns the path - or sometimes just the name - of the file on the client.

Code running on the server has no access to the client's file system.

It might appear to work when you're debugging your code in Visual Studio. But that's just because the server and the client are the same in that specific instance.

You need to save the posted file somewhere on the server, and then use the server path to read the file:
C#
string excelPath = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString("N") + ".xls");

fileuploadExcel.SaveAs(excelPath);

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=Excel 12.0;Persist Security Info=False";

try
{
    ...
}
finally
{
    System.IO.File.Delete(excelPath);
}
   

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




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