操作必须在更新excel表时使用可更新查询

I am getting this error when trying to update excel sheet :

我在更新excel表格时遇到了这个错误:

Server Error in '/ReadExcelData_Csharp' Application.
Operation must use an updateable query.

and here is the code that i am using :

这是我使用的代码:

  querys = "UPDATE [Sheet1$] "+"SET [Number]=" +s.Trim()+ " WHERE [Number]=" + s2.Trim() ;
  objcmc = new OleDbCommand(querys, conn);
  objcmc.ExecuteNonQuery();

any help will be appreciated .

如有任何帮助，我们将不胜感激。

and here is the connection i used :

这里是我使用的连接:

if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

3 个解决方案

#1

Remove the IMEX=2 (or IMEX=1) from the connection string and it will work. I have tested this crazy solution several times and removing the IMEX for some strange reason seems to do the trick (at least for xlsx files).

从连接字符串中删除IMEX=2(或IMEX=1)，它将工作。我已经测试过这个疯狂的解决方案好几次了，出于某种奇怪的原因删除IMEX似乎很管用(至少对于xlsx文件来说是这样)。

The following code works:

下面的代码:

    static void Main(string[] args)
    {
        string connectionString  = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + "d:\\temp\\customers.xlsx" + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";
        string selectString = "INSERT INTO [Customers$](Id,Company) VALUES('12345', 'Acme Inc')";

        OleDbConnection con = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand(selectString, con);

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine("Success");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            con.Dispose();
        }
        Console.ReadLine();
    }
}

Thanks to RobertNet from social.msdn.microsoft.com

感谢来自social.msdn.microsoft.com的RobertNet

#2

I used the solution provided above and removed the IMEX=2 or IMEX=1 string from the connection string. But this was not enough. In my case, the solution needed an additional work around.

我使用了上面提供的解决方案，并从连接字符串中删除IMEX=2或IMEX=1字符串。但这还不够。在我的情况下，解决方案需要额外的工作。

But in my data base I actually needed the IMEX - because my column had mixed data types, some double values some string values. When I remove IMEX =1, I get the a runtime exception "Unable to cast object of type" because it automatically selects the column data type based on the most popular value in the column and then fails to cast the values which are not of the selected type.

但在我的数据库中，我实际上需要IMEX——因为我的列有混合数据类型，一些双值，一些字符串值。当我删除IMEX =1时，我得到一个运行时异常“无法强制类型对象”，因为它根据列中最流行的值自动选择列数据类型，然后无法强制转换非所选类型的值。

I worked around this issue by changing my double and int values to string values (added a ' in the beginning of the cell value manually in excel) and removed the IMEX from the connection string. and then this solved the issue.

我通过将双值和int值更改为字符串值(在excel中手动添加单元值开头的a)来解决这个问题，并从连接字符串中删除IMEX。这就解决了这个问题。

#3

In my case,I changed ConnectionString and then this solved the issue.
I removed ReadOnly=False;HDR=Yes; parametes in connectionString

在我的例子中，我改变了ConnectionString，然后这个解决了这个问题。我删除只读的= False;HDR = Yes;参数在connectionString

string _connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;\";", pathToExcelFile);

#1