使用C＃OLEDB未命中匹配类型错误编辑Excel [英] Edit excel with C# OLEDB miss match type error

问题描述

我正在尝试使用c＃oledb方式更新excel文件。



我有多个excel文件。其中一个没有任何问题，我可以像我想要的那样更新。

但是其他excel文件我有一些错配列。

我想像字符串一样更新单元格，但它的类型是数字（在Excel上），所以我得到了不匹配错误。



我看看论坛，看到IMEX = 1可以解决问题。

但是当我添加IMEX = 1时我得到了这个错误：

Hi,

I'm trying to update an excel file with using c# oledb way.

I have excel files more than one. One of then hasn't any problem and i can update like what i want.
But other excel files i have some missmatch column.
I want to update cell like a string but it's type is number ( on Excel ), So i'm getting mismatch error for this.

I look at the forums and saw IMEX=1 can solve the problem.
But when i add IMEX=1 i got this error :

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Operation must use an updateable query.

这是我的代码块：

Here is my code block :

string excel = String.Format("UPDATE {0} SET F1='{1}'", shtsrg,exres);
string exceltwo = String.Format("UPDATE {0} SET F1='{1}'", shtsrgtwo, exrestwo);

OleDbConnection baglans = new OleDbConnection();
    baglans.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + yeniDosya + "; Extended Properties = 'Excel 8.0; HDR=NO'";
    baglans.Open();
OleDbCommand cmds = new OleDbCommand(excel, baglans);
cmds.ExecuteNonQuery();
baglans.Close();

OleDbConnection baglanstwo= new OleDbConnection();
baglanstwo.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + yeniDosya + "; Extended Properties = 'Excel 8.0; HDR=NO; IMEX=1'";
baglanstwo.Open();

OleDbCommand cmdstwo = new OleDbCommand(exceltwo, baglanstwo);

cmdstwo.ExecuteNonQuery();

baglanstwo.Close();

我使用了2个连接，我想知道问题出在哪里。我正在更新2列。 1.连接我没有任何问题。我有第二个问题。



如果没有Imex，有没有改变excel的单元数据类型？或者什么解决了可更新的查询错误？



我尝试过：



Imex = 1没有解决我的问题并给出另一个问题。

I used 2 connection for just i want to see where is the problem. I'm updating 2 columns. With 1. connection i haven't any problem. I have a problem with second one.

Is there anyway for change excel's cell data type without Imex ? Or anything solve Updateable query error ?

What I have tried:

Imex=1 is not solving my problem and gives another one.

推荐答案

你应该首先用 SELECT <读取项目/ code>语句，并确切检查每个变量返回的类型。然后，您可以使用正确的值更新它。

You should read the item(s) first with a SELECT statement, and check exactly what type each variable returns. You can then update it with the correct value.

这篇关于使用C＃OLEDB未命中匹配类型错误编辑Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！