Workaround for "Action must use an updatable query" when using OLE DB to read and write Excel

Workaround for "Action must use an updatable query" when using OLE DB to read and write Excel

Transferred from: http://www.cnblogs.com/Richinger/archive/2008/09/28/1301170.html

The first two days to use OLE DB to connect to Excel as a unit of a department from a number of Excel sheet to extract the required data, the program is very simple, the results are quickly come out, the original manual takes a long time to select the results now almost a click on the results. The user is very satisfied, because it is used by many people, start just to show the results on a page. Later they asked for the result to be written to the XLS file in another sheet, I casually wrote a statement that can be immediately fixed, I did not expect to actually appear "operation must use an updatable query." Because the read results are normal, I started to think that the Excel file was in a folder that did not have write permissions. When the appropriate permissions have been added, the result remains unchanged. There is no way to check the program, carefully examined, the problem has been found, originally started just to read Excel I will connect the following format:

Private filename as String = Server.MapPath (".") + "\test.xls; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 ' "
Dim constr as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filename

Special attention

Extended properties= ' Excel 8.0; Hdr=yes;imex=1 '

A: HDR (HeaDer Row) settings
If the specified value is yes, the first row of the worksheet in the Excel file is the field name

If the specified value is No, the first row of the worksheet in the Excel file is information, No field name

B:IMEX (IMport EXport mode) settings

There are three modes of IMEX, each of which causes different reading and writing behavior, and then describes:
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.

After reading these people will not have to say my writing why there is a problem. I will change the connection to read as follows:

Private filename as String = Server.MapPath (".") + "\test.xls; Extended properties= ' Excel 8.0; Hdr=yes;imex=2 ' "
Dim constr as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filename

Run everything OK.

Workaround for "Action must use an updatable query" when using OLE DB to read and write Excel