Environment when I have an error: 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 ', ' Test ', ' AA ') ', conn ';//(A,b,c,d,e,f,g) cmd. ExecuteNonQuery (); } catch (System.Exception ex) {TextBox1.Text + = ("Insert data failed:" + ex.) Message); TextBox1.Text + = ("\ r \ n"); }
When encountering this error, the first thought is that there is no permission, but running with administrator is still the same error!
Again, add the permissions with the following code, or 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);D irectoryinfo di = new DirectoryInfo (Path.getdirectoryname (Excelpath)); System.Security.AccessControl.DirectorySecurity dirsecurity = di. GetAccessControl ();d irsecurity.addaccessrule (New FileSystemAccessRule ("Everyone", Filesystemrights.fullcontrol, Accesscontroltype.allow));d Irsecurity.addaccessrule (New FileSystemAccessRule ("Users"), Filesystemrights.fullcontrol, Accesscontroltype.allow));d I. Setaccesscontrol (dirsecurity);
Knowledge tutorial, here the connection string is more: Extended properties= ' Excel 12.0; Hdr=yes; Imex=2 '
Value of the parameter HDR:
Hdr=yes, this means that the first row is the title, not as data use, if used Hdr=no, then the first row is not the title, as data to use. The system default is Yes
parameter Excel 8.0 for Excel 97 to 2003 versions, use Excel 8.0,2007 or 2010 for both extended Properties=excel 12.0
IMEX (IMport EXport mode) settings
There are three modes of IMEX:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
What I want to highlight here is the IMEX parameter, because different patterns represent different reading and writing behaviors:
when imex=0 is "Export mode", the Excel file opened by this mode can only be used for "write" purposes.
when Imex=1 is "Import Mode", the Excel file opened in this mode can only be used for "read" purposes.
when imex=2 is "connected mode", the Excel file opened in this mode can support both "read" and "write" purposes.
The meanings are as follows:
0---output mode;
1---input mode;
2----link mode (full update capability)
As described above, the connection string above should be readable, the plug-in record
This is not the case, however, 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, and if it is a Web application, it needs to add Iis_iusrs or Iis_service directory access to the user;
Or go to search to see how others are solved, but look at the way others solve the problem, to me here is the test does not pass!
Guess or Imex value problem, instead of 1, then 0, nimah, Miracle appeared!
Then test the IMEX set to 4 or 10, the result is no problem, but 1 and 2 not, it is the rhythm of the pit dad.