Click here to Skip to main content
15,066,326 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have tried to import an excel sheet to the database. I have tired the below mentioned code and i am getting error "
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.
"

The excel sheet is not opened anywhere while i am executing the code..

Please assist
Thanks in advance.

What I have tried:

private void ExcelConn(string FilePath)
{

    constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);
    Econ = new OleDbConnection(constr);

}

private void InsertExcelRecords(string FilePath)
{
    ExcelConn(FilePath);

    Query = string.Format("Select [Account_name],[Key_Risks],[Mitigation_Plan],[Contingency_plan],[Status_in_detail],[Flag],[Rdate],[LableDate],[Pool_name],[Group_name],[Lastupdatedate] FROM [{0}]", "Sheet1$");
    OleDbCommand Ecom = new OleDbCommand(Query, Econ);
    Econ.Open();

    DataSet ds = new DataSet();
    OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
    Econ.Close();
    oda.Fill(ds);
    DataTable Exceldt = ds.Tables[0];

    SqlConnection con = new SqlConnection(cs);

    //creating object of SqlBulkCopy
    SqlBulkCopy objbulk = new SqlBulkCopy(con);
    //assigning Destination table name
    objbulk.DestinationTableName = "ERP_Mgmt";
    //Mapping Table column
    objbulk.ColumnMappings.Add("Account_name", "Account_name");
    objbulk.ColumnMappings.Add("Key_Risks", "Key_Risks");
    objbulk.ColumnMappings.Add("Mitigation_Plan", "Mitigation_Plan");
    objbulk.ColumnMappings.Add("Contingency_plan", "Contingency_plan");
    objbulk.ColumnMappings.Add("Status_in_detail", "Status_in_detail");
    objbulk.ColumnMappings.Add("Flag", "Flag");
    objbulk.ColumnMappings.Add("Rdate", "Rdate");
    objbulk.ColumnMappings.Add("LableDate", "LableDate");
    objbulk.ColumnMappings.Add("Pool_name", "Pool_name");
    objbulk.ColumnMappings.Add("Group_name", "Group_name");
    objbulk.ColumnMappings.Add("Lastupdatedate", "Lastupdatedate");
    //inserting Datatable Records to DataBase
    con.Open();
    objbulk.WriteToServer(Exceldt);
    con.Close();
}

protected void Button1_Click(object sender, EventArgs e)
{
    string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
    InsertExcelRecords(CurrentFilePath);
}
Posted
Updated 19-Sep-20 22:35pm
v3
Comments
Dave Kreskowiak 8-Aug-17 10:35am
   
Does the user have write permissions to the folder the Excel sheet is in? If under Program Files, the answer to that would be no.
Renjith_R 8-Aug-17 10:42am
   
Thanks for the quick reply..
i have created a folder in e drive.. still the file path is showing like "C:\Program Files (x86)\Common Files\Microsoft Shared\DevServer\10.0\ImportTest.xlsx"

Where should i keep the excel ?
Dave Kreskowiak 8-Aug-17 10:53am
   
That's dependent on your business rules and requirements. I can't tell you where to put the Excel file. I CAN tell you that whatever folder the sheet goes in, the user MUST have write permissions to that folder.

As for the path you're coming up with, does the Excel sheet actually exist at that path?
Richard Deeming 9-Aug-17 12:28pm
   
This appears to be the same question that "paul_vin" posted three days ago:
https://www.codeproject.com/Questions/1200094/Error-while-inserting-excel-sheet-data-into-databa[^]

Are you working on the same project, or have you both been given the same homework assignment? If it's homework, then your teacher obviously hasn't done a very good job of explaining the difference between the client and the server, and how file uploads work in ASP.NET applications.

Start by checking the permissions on the file and folder as Dave mentions - if the user the software is running under doesn't have read and write access, nor does the application.

If that all looks good, then check the rest of your code: does any part of it create a connection object and forget to close the connection (for best practice, you should use a using block with each creation, so they are automatically closed and disposed when you are finished with them:
using (OleDbCommand Ecom = new OleDbCommand(Query, Econ))
   {
   Econ.Open();
   ...
   }
If any part of your software fails to close the connection, no other part of it will be able to open it again until you app closes (or the GC is kicked into life to dispose the redundant object).
   
(Cross-posting from Error while inserting excel sheet data into database[^], as it seems to be the same question from a different user.)

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:
protected void Button1_Click(object sender, EventArgs e)
{
    string filePath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString("N") + ".xlsx");
    FileUpload1.SaveAs(filePath);
    try
    {
        InsertExcelRecords(filePath);
    }
    finally
    {
        File.Delete(filePath);
    }
} 
   
Hello
I installed software for a company
I get this error when I try to update the information
Can you help me with what it is?
Microsoft Access Database Engine could not open or write files
c: \ newfolder. it has already been opened exclusively by another user, or you need permission to view and write its data. "
Is the problem with my software or the client company system?
   
Comments
Richard MacCutchan 20-Sep-20 7:48am
   
"c:\newfolder. it has already been opened exclusively by another user, or you need permission to view and write its data. "
The error message is perfectly clear. Find out which application is using this folder. But you should not be using a folder in the root of the C drive, see this article by the famous OriginalGriff: Where should I store my data?[^].

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