Question

我在试图更新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();

任何帮助都将受到赞赏。

这里指的是:

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"";
            }

Answer 1

将IMEX=2(或IMEX=1)从连接线上移走,并将工作。我多次测试了这一高分辨率解决办法,并出于某些奇怪的原因,删除了IMEX(至少Xlsx文档)。

下述法典:

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

得益于http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/e0f65510-2164-4d8a-a70e-631ac6a30f84/“social.msdn.microsoft.com

Answer 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.

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.

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.

Answer 3

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

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

Answer 4

My issue was a previous connection locked the excel file into readonly mode. Needed to kill the connection.

友情链接