MS-Access：TableAdapter用于没有主键的表的UpdateCommand [英] MS-Access: TableAdapter UpdateCommand for table without primary key

问题描述

对于没有主键的表的更新查询的语法是什么？

免责声明：令人沮丧的是，添加主键不是一个选项。我的程序是一个更大的系统中的一个小程序，数据管理不善。我的开发时间不包括重写其他软件。

注意：数据库是Microsoft Access。

注意：类似于： Excel：TableAdapter UpdateCommand for table without primary key

更新：我正确的说：数据库没有明确的主键，那么可能没有有效的TableAdapter UpdateCommand？

解决方案

如果没有明确的主键，至少应该有一个隐式的主键（即使它是每一列）。没有任何密钥，您将无法安全更新表。

如果在创建数据集时通过向导你应该得到一个更新查询，其中包含一个类似于这样的更新语句：

 
 update TableA 
 set Column1 = @ Column1， Column2 = @ Column2 ... 
其中Column1 = @ PreviousColumn1和Column2 = @ PreviousColumn2 ... 
 

编辑

您将无法使用该向导来更新或删除表中没有PK的命令。但是，您可以将访问文件的副本放在表上放置一个PK（如果您不能派生一个简短的隐式密钥，则可能需要使用每一列），并使用它来通过向导创建命令。 / p>

如果您不想通过该步骤，则必须创建类似于上述的查询。 @PreviousColumnX 参数将其 SourceVersion 值设置为原始。

  update TableA 
 set Column1 = @ Column1，Column2 = @ Column2 ... 
 where （Column1 = @ PreviousColumn1或@ PreviousColumn1为null）
和Column2 = @ PreviousColumn2 ... 
  

What's the syntax for an Update query for a table without a primary key?

Disclaimer: Frustratingly, adding a primary key is not an option. My program is a small program in a much larger system with poor data management. My development time does not include rewriting the other software.

Note: The database is Microsoft Access.

Note: Similar to: Excel: TableAdapter UpdateCommand for table without primary key

UPDATE: Am I correct in saying, "If the table in the database has no explicit primary key, then there can be no valid TableAdapter UpdateCommand?"

解决方案

If there isn't an explicit primary key, there should at least be an implicit primary key (even if it's every column). Without any sort of key, you won't be able to safely update the table.

If you go through the wizard when creating the dataset, you should get an update query that includes an update statement similar to this:

update TableA
set Column1 = @Column1, Column2 = @Column2 ...
where Column1 = @PreviousColumn1 and Column2 = @PreviousColumn2 ...

EDIT
You won't be able to use the wizard for update or delete commands without a PK on the table. You can, however, make a copy of the Access file put a PK on the table (if you can't derive a short implicit key, you may have to use every column) and use that to create the commands via the wizard.

If you don't want to go through that step, then you'll have to create a query similar to the one above. The @PreviousColumnX parameters would have their SourceVersion values set to Original.

update TableA
set Column1 = @Column1, Column2 = @Column2 ...
where (Column1 = @PreviousColumn1 or @PreviousColumn1 is null)
    and Column2 = @PreviousColumn2 ...

这篇关于MS-Access：TableAdapter用于没有主键的表的UpdateCommand的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！