locked
Error in Export data from Excel to Sql server 2008 RRS feed

  • 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

    Saturday, September 12, 2015 6:48 AM

All replies

  • 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.

    Saturday, September 12, 2015 7:25 AM
  • 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

    Wednesday, September 16, 2015 3:52 AM