Click here to Skip to main content
15,887,683 members
Please Sign up or sign in to vote.
1.80/5 (2 votes)
I want to read the data from a xl sheet i wrote the code for that but showing error while executing
the error is following

{"The Microsoft Access database engine cannot open or write to the file 'D:\\Gopi.Ch\\DotNetTasks\\XL Sheet\\XL Sheet\\Files'. It is already opened exclusively by another user, or you need permission to view and write its data."}


my code following

C#
string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
            // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                string path = @"D:\Gopi.Ch\DotNetTasks\XL Sheet\XL Sheet\File\gopi.xlsx";
                OleDbConnection con_excel = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0");
                OleDbDataAdapter adpt = new OleDbDataAdapter("SELECT Country,State,City,Pincode FROM [Sheet1$]", con_excel);
                DataSet ds = new DataSet();
                adpt.Fill(ds, "tbl_temp");
                DataTable dt = new DataTable();
                dt = ds.Tables["FooTable"];
               /* List<SetData> setData = new List<SetData>();
                foreach (DataRow dr in dt.Rows)
                {

                } */

                gdXlData.DataSource = ds.Tables["tbl_temp"];
                gdXlData.DataBind();
            }
            catch
            {
            }
            finally
            {
                // Close connection
                oledbConn.Close();
            }

What Mistake i did and already installed microsoft oledb 12.0

thanks and regards
Posted
Updated 29-Dec-15 22:13pm
v3
Comments
Debojyoti Saha 30-Dec-15 2:51am    
@"D:\Gopi.Ch\DotNetTasks\XL Sheet\XL Sheet\Files";
where your file name?
[no name] 30-Dec-15 3:48am    
You are missing file name in path variable. Append excel sheet file name with "D:\Gopi.Ch\DotNetTasks\XL Sheet\XL Sheet\Files".
Anurag Gandhi 30-Dec-15 5:17am    
I could't see any mistake in your code, but OleDb locks it for 3 minutes or so.
If you are dealing with xlsx file, I would recommend to use EPPlus: http://epplus.codeplex.com/. Its simple, clean and will not lock your file like OleDb does.
Prasad Avunoori 30-Dec-15 8:59am    
Does use who runs this application has admin rights?
Maciej Los 30-Dec-15 9:19am    
Wrong connection string, here: Extended Properties=Excel 8.0

1 solution

2 possible reasons:
1) Check path to the file! Are you sure you used XL Sheet twice?
2) Connection string is wrong (see my comment to the question)
Excel connection strings - ConnectionStrings.com[^]
Proper connection string should look like:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\ProperPathToExcelFile.xlsx;Extended Properties="Excel 12.0;HDR=YES";
 
Share this answer
 
Comments
Sergey Alexandrovich Kryukov 30-Dec-15 12:00pm    
5ed.
—SA
Maciej Los 30-Dec-15 15:13pm    
Thank you, Sergey.

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