C # Use oledb to connect to excel and execute the Insert Into statement. The following error occurs: "The operation must use an updatable query ."

Environment in which an error occurs: 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 ', '123', 'test', 'test', 'aa') ", conn); // (A, B, C, D, E, F, G) cmd. executeNonQuery ();} catch (System. exception ex) {textBox1.Text + = ("failed to insert data:" + ex. message); textBox1.Text + = ("\ r \ n ");}

When this error occurs, the first thing that comes to mind is that you do not have the permission, but using the Administrator identity to run is still the same error! The following code adds permissions:

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);

For more information, see Extended Properties = 'excel 12.0; HDR = yes; IMEX = 2'

Parameter HDR value:

HDR = Yes, which indicates that the first line is the title and is not used as data. If HDR = NO is used, it indicates that the first line is not the title and used as data. The default value is YES.
Parameters Excel 8.0 for Excel 97 and later versions Excel 2003 and Extended Properties = Excel 8.0 for Excel 2007 and 2010 IMEX (IMport EXport mode) settings
IMEX has three modes:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
Here I particularly want to explain the IMEX parameter, because different modes represent different read/write behaviors:
When IMEX = 0, the "Export mode" is enabled. The Excel files enabled in this mode can only be used for "writing" purposes.
When IMEX = 1, it is set to "Import mode". The Excel files opened in this mode can only be used for "read" purposes.
When IMEX = 2, the connection mode is enabled. The Excel files enabled in this mode support both "read" and "write.
Meaning:
0 --- output mode;
1 --- input mode;
2 ---- connection mode (full update capability) according to the above description, the above connection string should be readable and recorded by the plug-in
However, this is not the case. When an Insert Into statement is executed, 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 permission of IIS_IUSRS or IIS_Service users;
Search to see how others solve the problem. But after reading the solution, the test fails! I guess it's still the IMEX value. If it cannot be changed to 1, it should be changed to 0, Nima, a miracle!Then I tried to set IMEX to 4 or 10, and the result was okay, but 1 and 2 didn't work. It was really a pitfall.