Click here to Skip to main content
15,889,909 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I need to insert data into an excel sheet but I am facing error that :- Operation must use an updateable query. I have given permission to the folder of everyone where my .xlsx file resides. I am using Visual Studio 2012 express using C#.
The Code is as follows-
C#
private void InsertData()
       {
           //--
           try
           {
               System.Data.OleDb.OleDbConnection MyConnection;
               System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
               string sql = null;
               //string path = System.IO.Path.GetFullPath(Server.MapPath("~/InformationNew.xlsx"));
               string path = "D:/ttt.xlsx";
               //MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
               string connstr= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:/ttt.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\"";

               MyConnection =new OleDbConnection(connstr);
               MyConnection.Open();
               myCommand.Connection = MyConnection;
               //sql = "Insert into [Sheet1$] (id,name) values('5','e')";
               sql = "Insert into [Sheet1$](a,b,c,d) Values('1','Vikas','Bohra','50')";
               //sql = "Select * From [Sheet1$]";
               myCommand.CommandText = sql;
               myCommand.CommandType = CommandType.Text;
               myCommand.ExecuteNonQuery();
               MyConnection.Close();
           }
           catch (Exception ex)
           {
               Response.Write(ex.ToString());
           }


           //--
       }
Posted
Updated 14-Jan-14 7:42am
v4
Comments
OriginalGriff 14-Jan-14 4:40am    
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
Perhaps showing us the code you are using to do the insert would help?
Use the "Improve question" widget to edit your question and provide better information.

A StackOverflow question was answered with the following response that supposedly worked...

"Get rid of IMEX=1 from your connection string. That works for me."

http://stackoverflow.com/questions/12002805/update-an-excel-sheet-using-vba-ado[^]
 
Share this answer
 
To set this permission, right click on the Excelsheet folder (or whichever other folder you have put the file in) and select Properties. Look for the Sharing tab. Click share button and give read and write permission to every one name then click share button. That's it. You are done. Now that will work. working for me.
 
Share this answer
 
v2
Comments
Dave Kreskowiak 13-Jul-23 16:28pm    
Even worse. You DO NOT use Excel as a multi-user database. Will you only beg for problems and data loss. Multi-user applications should ALWAYS use a database engine for this task. If you need data in Excel, you can export it to a sheet when needed.

And you're 9 years too late to the discussion.

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