C# uses oledb to connect to excel to execute the Insert Into statement. The sample code of the solution "must use an updateable query" appears

My environment when the error occurred: Windows 7, Framework 4, 0, Microsoft Office 2007, VS2010, c# WinForm;

Part of the code:

                    string strConn = "Provider=Microsoft.Ace.OleDb.12.0;Persist Security Info=False;
                    " + "data source=" + @excelPath + ";Extended Properties='Excel 12.0; HDR=yes; IMEX=2'";
                    OleDbConnection conn = new OleDbConnection();
                    conn.ConnectionString = strConn;
                    try
                    {
                        OleDbCommand cmd = null;
                        try
                        {
                            cmd = new OleDbCommand("Insert Into [Sheet1$] Values('abc', 'bac', '0', '123456', 'test','测试','aa')", conn);//(A,B,C,D,E,F,G) 
                            cmd.ExecuteNonQuery();
                        }
                        catch (System.Exception ex)
                        {
                            textBox1.Text += ("插入数据失败：" + ex.Message);
                            textBox1.Text += ("\r\n");
                        }

The first thing that comes to mind when encountering this error is that there is no permission, but running as an administrator still gives the same error !

Added permissions through the following code, still the same error:

FileInfo fi = new FileInfo(excelPath);
System.Security.AccessControl.FileSecurity fileSecurity = fi.GetAccessControl();
fileSecurity.AddAccessRule(new FileSystemAccessRule("Everyone", FileSystemRights.FullControl, AccessControlType.Allow));
fileSecurity.AddAccessRule(new FileSystemAccessRule("Users", FileSystemRights.FullControl, AccessControlType.Allow));
fi.SetAccessControl(fileSecurity);

DirectoryInfo di = new DirectoryInfo(Path.GetDirectoryName(excelPath));
System.Security.AccessControl.DirectorySecurity dirSecurity = di.GetAccessControl();
dirSecurity.AddAccessRule(new FileSystemAccessRule("Everyone", FileSystemRights.FullControl, AccessControlType.Allow));
dirSecurity.AddAccessRule(new FileSystemAccessRule("Users", FileSystemRights.FullControl, AccessControlType.Allow));
di.SetAccessControl(dirSecurity);

Knowledge tutorial, there are too many connections Strings: Extended Properties='Excel 12.0; HDR =yes; IMEX=2'

Value of parameter HDR:

HDR= Yes, this means that the first row is the title and is not used as data. If HDR=NO is used, it means that the first row is not the title and is used as data. The system default is YES
Parameter Excel 8.0 For Excel 97 and above to 2003, use Excel 8.0, and for 2007 or 2010, use Extended Properties=Excel 12.0

IMEX (IMport EXport mode) settings
IMEX has three modes:
　0 is Export mode
　1 is Import mode
　2 is Linked mode (full update capabilities)
　I What needs special explanation here is the IMEX parameter, because different modes represent different reading and writing behaviors:
When IMEX=0, it is "export mode". The Excel file opened in this mode only Can be used for "writing" purposes.
When IMEX=1, it is "import mode". The Excel file opened in this mode can only be used for "reading" purposes.
When IMEX=2, it is "link mode". The Excel file opened in this mode can support both "reading" and "writing" purposes.
The meaning is as follows:
0 ---Output mode;
1---Input mode;
2----Link mode (full update capability)

According to the above description, the above connection string should be readable and recorded by the plug-in

But this is not the case. When executing the Insert Into statement, an exception occurs: "The operation must use an updatable query"!

Note that it is a c# WinForm program, not a Web application; if it is a Web application, you need to add the directory access permissions of the IIS_IUSRS or IIS_Service user;

It’s better to search and see how others solved it, but I’ve read it all The method to solve the problem failed to pass the test when it came to me!

I guess it’s still a problem with the IMEX value. If changing it to 1 doesn’t work, then change it to 0. Damn it, a miracle happened!

Then I tested setting IMEX to 4 or 10, and the results were all fine, except for 1 and 2. It was really a cheating rhythm.

The above is the detailed content of C# uses oledb to connect to excel to execute the Insert Into statement. The sample code of the solution "must use an updateable query" appears. For more information, please follow other related articles on the PHP Chinese website!