Error in Export data from Excel to Sql server 2008

Question

User-712926555 posted

Hi,

my code is

 protected void Button1_Click(object sender, EventArgs e)
        {
            //string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
            //InsertExcelRecords(CurrentFilePath);

            String strConnection = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            //file upload path
            string path = FileUpload1.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 [Brand],
,[Model],[Name],[Size],[Price],[MRP] from [Sheet1$]", excelConnection);
            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            //Give your Destination table name
            sqlBulk.DestinationTableName = "Master_Product";
            sqlBulk.WriteToServer(dReader);
            excelConnection.Close();

            Utils.ShowAlert(this, "Success");
        }  
It
 shows error like
The Microsoft Office 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.
I have follow the following url,
 But still getting same errro
http://www.aspdotnet-suresh.com/2013/01/c-microsoft-office-access-database.html

Answer 1

User541108374 posted

Hi,

can you check under which user account your application runs? It might not be the same as described in that particular article. Once you found out you can follow the same steps as described and provide enough permissions to that folder.

Grz, Kris.

Answer 2

User-271186128 posted

Hi Hisanth,

The Microsoft Office 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.

As for this issue, I suggest you could refer to the Shawn's reply in this thread:

http://forums.asp.net/t/1929989.aspx?The+Microsoft+Office+Access+database+engine+cannot+open+or+write+to+the+file+

This error can be caused by:

1. The file is still open on the machine or if it is on the network then someone still has it open.

2. If the file is on a network, then this has caused this error due to security settings and the advice is to move the file locally onto the server.

3. If the file is local then there are still permission issues that ares till present

Solution

If you have already ensured that the file is not open anywhere else and you have copied it locally to the server please see the below steps:

1. Ensure the file is saved locally on the server and is in a folder instead of being in the root folder.

2. Once the file is in a folder, go to the privileges of that folder and make sure the following users have full access of read and write: "network service" and "asp".

3. After granting these users access onto the folder and you still see the error, grant the user "Everyone" and test if this resolves the error.

For further information, you could refer to:

http://community.landesk.com/support/docs/DOC-24626

http://www.vbforums.com/showthread.php?398478-RESOLVED-Webform-error-quot-It-is-already-opened-exclusively-by-another-user-or-you-need-(-)-quot

Besides, you could also refer to this article:https://support.microsoft.com/en-us/kb/316675

Best regards,
Dillion