在Excel上执行多个更新命令

I have an Excel file and I want update multiple rows in a sheet.So I write this code :

我有一个Excel文件，我想在一个表中更新多个行。所以我写了这样的代码:

OleDbConnection cn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + serverPath + ";Extended Properties = Excel 8.0;");
        try
        {

            strUpd = "";
            strUpd += "update [Data14City$] set  B_1_1 = 5 ,B_1_2 = 26 ,B_1_3 = 44 ,B_1_4 = 8  where id = 1 ";
            strUpd += " update [Data14City$] set  B_1_1 = 0 ,B_1_2 = 8 ,B_1_3 = 17 ,B_1_4 = 0  where id = 2";
            cn.Open();
            OleDbCommand cmdInsert = new OleDbCommand(strUpd, cn);
            cmdInsert.ExecuteNonQuery();
            cn.Close();
        }
        catch
        {
        }

and I got this error:

我得到了这个错误

Syntax error (missing operator) in query expression 'id = 1 update [Data14City$] set B_1_1 = 0 ,B_1_2 = 8 ,B_1_3 = 17 ,B_1_4 = 0 where id = 2'.

查询表达式'id = 1 update [Data14City$]设置B_1_1 = 0,B_1_2 = 8,B_1_3 = 17,B_1_4 = 0，其中id = 2'。

and I when I add ; to this line :

当我加上;这条线:

strUpd += "update [Data14City$] set  B_1_1 = 5 ,B_1_2 = 26 ,B_1_3 = 44 ,B_1_4 = 8  where id = 1;";

I got this error:

我得到这个错误:

Characters found after end of SQL statement.

在SQL语句结束后找到的字符。

how I can execute multiple statement in Excel?

如何在Excel中执行多个语句?

thanks

谢谢

1 个解决方案

#1

You don't really need to stack up your updates like that (in fact, as has been pointed out above, you can't). It doesn't take much longer to execute them individually. Here's the code I've been using and it works fine (I actually have mine in a loop, but it'll work equally well if you can't loop your updates).

你不需要像这样堆叠你的更新(事实上，正如上面指出的，你不能)。单独执行它们不需要太长时间。这是我一直在使用的代码，它工作得很好(实际上我的代码在一个循环中，但是如果你不能循环更新的话，它也同样可以工作得很好)。

cn.Open();

using (OleDbCommand cmd = cn.CreateCommand())
{
    cmd.CommandText = "update [Data14City$] set  B_1_1 = 5 ,B_1_2 = 26 ,B_1_3 = 44 ,B_1_4 = 8  where id = 1";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "update [Data14City$] set  B_1_1 = 0 ,B_1_2 = 8 ,B_1_3 = 17 ,B_1_4 = 0  where id = 2";
    cmd.ExecuteNonQuery();

    // ... and so on
}

cn.Close();