Error while inserting excel sheet data into database

Question

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 5-Aug-17 8:16am

paul_vin

Updated 9-Aug-17 4:58am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
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.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Michael_Davies · Answer 1 · 2017-08-05T08:48:00

Solution 1

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";

Posted 5-Aug-17 8:48am

Michael_Davies

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.

Richard Deeming · Answer 2 · 2017-08-09T04:58:00

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);
}