C# 传递带有修改行的 DataRow 集合时，更新需要有效的 UpdateCommand
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/588361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update requires a valid UpdateCommand when passed DataRow collection with modified rows
提问by Sam
So I had this working last week. At least, I thought I did! DataGridView Update
所以我上周有这个工作。至少，我认为我做到了！ 数据网格视图更新
Then I start working on the project again today and am getting
然后我今天又开始做这个项目了
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
当传递带有修改行的 DataRow 集合时，更新需要有效的 UpdateCommand。
On
在
scDB.SSIS_Configurations_StagingDataTable table = (scDB.SSIS_Configurations_StagingDataTable)stagingGrid.DataSource;
myStagingTableAdapter.Update(table);
The StagingTableAdapterhas an additional query which takes 'filter' as a parameter. That was used to fill the DataGridView. In the wizard for creating that query I see 'update was generated'. I see that most posts with this error require that an update statement be generated with a command builder. What do I do?
在StagingTableAdapter具有一个附加的查询这需要“过滤器”作为参数。那是用来填充DataGridView. 在创建该查询的向导中，我看到“更新已生成”。我看到大多数出现此错误的帖子都要求使用命令生成器生成更新语句。我该怎么办？
采纳答案by Henk Holterman
The error is quite literal: The Adapter needs a valid SQL Update statement. Dataset designers and CommandBuilders will generate these for you, but there is nothing wrong with hand-crafting a bit of SQL either.
错误很明显：适配器需要有效的 SQL 更新语句。数据集设计器和 CommandBuilders 将为您生成这些，但手工制作一些 SQL 也没有错。
Anyway, you'll have to verify (debugger) that the Update statement is still configured and what it actually is. It could be more of a SQL than a C# problem.
无论如何，您必须验证（调试器）Update 语句是否仍然配置以及它实际是什么。它可能更像是一个 SQL 而不是 C# 问题。
Edit: the Command Builder tools will only handle straight, single table, Select statements. Use a Join or anything fancy and you're on your own.
编辑：命令生成器工具将只处理直接、单表、选择语句。使用 Join 或任何花哨的东西，你就靠自己了。
回答by Sam
From the page:
从页面：
Note If there is enough information in the main query, the InsertCommand, UpdateCommand, and DeleteCommand commands are created by default when the TableAdapter is generated. If the TableAdapter's main query is more than a single table SELECT statement, it is possible the designer will not be able to generate the InsertCommand, UpdateCommand, and DeleteCommand. If these commands are not generated, you may receive an error when executing the TableAdapter.Update method.
注意 如果主查询中有足够的信息，则在生成 TableAdapter 时默认会创建 InsertCommand、UpdateCommand 和 DeleteCommand 命令。如果 TableAdapter 的主查询不止一个表 SELECT 语句，则设计器可能无法生成 InsertCommand、UpdateCommand 和 DeleteCommand。如果未生成这些命令，则在执行 TableAdapter.Update 方法时可能会收到错误。
So from what I can tell, these statements should have been generated. I'm using a single table.
因此，据我所知，这些陈述应该已经生成。我正在使用一个表。
回答by Dan
I ran into the same problem as Sam. I had working code that just suddenly was no longer working. I didn't know when I wrote it, but it must have been automatically inferring the update command, and then stopped doing it. Perhaps a service pack from MS in between versions that we never noticed. Anyway, the solution I came across is using a (in my case for oracle) a OracleCommandBuilder which takes the DataAdapter (after calling fill) as a parameter to the constructor and then calling GetUpdateCommand() and assigning that to the UpdateCommand on the DataAdapter.
我遇到了和 Sam 一样的问题。我有工作代码突然不再工作。我不知道我什么时候写的，但它一定是自动推断更新命令，然后停止这样做。也许是我们从未注意到的版本之间的 MS 服务包。无论如何，我遇到的解决方案是使用（在我的情况下是 oracle）一个 OracleCommandBuilder，它将 DataAdapter（在调用 fill 之后）作为构造函数的参数，然后调用 GetUpdateCommand() 并将其分配给 DataAdapter 上的 UpdateCommand。
pseudocode:
DataAdapter da = new DataAdapter(...)
...
da.Fill();
da.UpdateCommand = new OracleCommandBuilder(da).GetUpdateCommand();
...
da.Update();
回答by Hoang
Dan's workaround works for me, but instead of OracleCommandBuilder I can just use SqlCommandBuilder:
Dan 的解决方法对我有用，但我可以使用 SqlCommandBuilder 而不是 OracleCommandBuilder：
DataAdapter da = new DataAdapter(...)
...
da.Fill();
da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();
...
da.Update();
回答by Jason
This message will also be displayed caused when you do not have a primary key defined on the table you are updating.
当您没有在要更新的表上定义主键时，也会显示此消息。
回答by LaBird
I just experienced the same problem, and Jason's answer did work for me: I forgot to assign a primary key for my table.
我刚刚遇到了同样的问题，杰森的回答对我有用：我忘了为我的表分配一个主键。
Just One more reminder to add: After correcting the problem in the table, when going back to the Visual Studio, in the .xsd file that contains the dataset, make sure to remove the original table and add it again from the Server Explorer or Database Explorer. Otherwise the error will still exist.
再补充一点：表中问题更正后，返回Visual Studio时，在包含数据集的.xsd文件中，一定要删除原来的表，再从Server Explorer或Database重新添加探险家。否则错误仍然存在。
回答by MulugetaLegesse
I also faced the same problem and got the following solution. Try it.
我也遇到了同样的问题并得到了以下解决方案。尝试一下。
private void btnSave_Click(object sender, EventArgs e)
{
ConnStr = "Data Source=FIN03;Initial Catalog=CmsTest;Integrated Security=True";
cn = new SqlConnection(ConnStr);
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
mytran = cn.BeginTransaction(IsolationLevel.Serializable );
cmd.Transaction = mytran;
try
{
scb = new SqlCommandBuilder(da);
da.Update(ds, "tblworker");
mytran.Commit ();
MessageBox.Show("Data update successfully");
}
catch (Exception err)
{
mytran.Rollback();
MessageBox.Show(err.Message.ToString());
}
}
回答by Md Shahriar
You should always have a primary key defined column field. Otherwise the problem will always exist. SqlCommandBuilder will not help you if you don't have a primary key field.
您应该始终有一个主键定义的列字段。否则问题将一直存在。如果您没有主键字段，SqlCommandBuilder 将无济于事。
回答by d219
As others have said you need to make sure you have a primary key set on the table for the auto-generated update code to be created. I did this but was still seeing the same message of
正如其他人所说，您需要确保在表上设置了一个主键，以便创建自动生成的更新代码。我这样做了，但仍然看到相同的消息
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
当传递带有修改行的 DataRow 集合时，更新需要有效的 UpdateCommand。
I then discovered that you then need to refresh the table adapter as well (makes sense when you think about it!).
然后我发现您还需要刷新表适配器（考虑一下是有道理的！）。
While you are doing this it may also be worth double checking that you had the Generate Insert, Update and Delete statementsoption selected (like the below).
在执行此操作时，可能还需要仔细检查您是否选择了“生成插入、更新和删除语句”选项（如下所示）。
Then just click through the wizard, the final screen should confirm that it will create the redo the code required (now with the primary key):
然后只需单击向导，最终屏幕应确认它将创建所需的重做代码（现在使用主键）：
回答by Jaky Suriyakul Na Ayudhya
All you have to do is edit the database and ensure that at least one column is identified as a Primary Key.
您所要做的就是编辑数据库并确保至少有一列被标识为主键。
In my simple example, this could be the name column – although more realistically I would add an auto-numbering identity column which will ensure that all values in that column are unique.
在我的简单示例中，这可能是名称列——尽管更现实的是我会添加一个自动编号标识列，以确保该列中的所有值都是唯一的。
Resave everything, recreate a new DataSet,replace the DataGridView using drag and dropas before, and you're good to go.
重新保存所有内容，重新创建一个新的 DataSet，像以前一样使用拖放替换 DataGridView，您就可以开始了。
As usual, the answer is simple when you know it – incredibly frustrating when you don't.
像往常一样，当你知道的时候答案很简单——当你不知道时会非常令人沮丧。
来源。
