When using oledb to read and write excel, the following error occurs: "an updatable query is required for the operation ".

 

Two days ago, we used oledb to connect to excel to extract the required data from multiple sheets in an Excel team,ProgramThe results are very simple and will soon come out. It takes a long time for the results to be selected manually. Now, the results are displayed in almost one click. The user is very satisfied. Because it is used by many people, the results are displayed on a page at the beginning. Later, they asked to write the results to another sheet in the xls file. I wrote a statement to immediately implement the rollback. I did not expect that "the operation must use an updatable query ". Because the reading result is normal, I started to think that the folder where the Excel file is located has no write permission. After the corresponding permissions are added, the results remain unchanged. There is no way to check the program. I checked the program carefully and found the problem. I used to read the Excel file and wrote the connection in 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 note

Extended properties = 'excel 8.0; HDR = yes; IMEX = 1'

A:HDR (header row) settings
If the value is yes, the first row of the worksheet in the Excel file is the column name.

If the value is no, the first row of the worksheet in the Excel file is the item, and there is no column name.

B:IMEX (Import Export mode) settings

There are three modes for IMEX, And the read/write behaviors are also different:
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.

After reading this, you don't have to say why my writing is wrong. I changed the connection to the following:

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 all OK.