ADO 可更新查询 - 当连接的表之一没有记录时出错

发布于 2024-11-08 01:11:31 字数 790 浏览 0 评论 0原文

我使用 ADO 的“可更新查询”功能,能够从多个表和条件中选择数据集,并将其显示在网格或其他 UI 中供用户浏览和编辑。

然而,令我惊讶的是,我之前没有遇到过这个问题,当一个连接表没有(主)键的记录,并且用户尝试编辑该表中的字段时,ADO 会给出著名的“无法找到行..”帖子错误。

据我了解此错误,ADO 驱动程序正在尝试查找记录以更新其字段 - 当然,在这种情况下,没有可查找的记录。在这些情况下,我所期望的是 ADO 驱动程序将发出与主表等效的 UPDATE 查询,但对子表发出 INSERT 查询。

有其他人遇到过这个问题并找到解决方法吗?

使用的 ADO 驱动程序是连接到 Access (mdb) 数据库的 Jet 4.0 OLE DB Provider。

我已确保两个表的主键字段在查询数据集中可供驱动程序使用。

下面是我使用的 SQL 的基本版本:

SELECT 
    Table1.CustomerNo, Table1.Field1, Table1.Fieldn,  
    Table2.CustomerNo, Table2.Field1, Table2.Fieldn  
FROM 
    Table1 
LEFT JOIN Table2 
    ON Table1.CustomerNo = Table2.CustomerNo  
WHERE 
    Table1.CustomerNo = Newcode;

作为实验,我在 MS Access 2007 中尝试了同样的操作,并且成功了,因此 ADO 中可能有一个解决方案(但 Access 可能使用不同的驱动程序)。

I use the 'updateable query' feature of ADO, being able to select a dataset from several tables and criteria and display it in a grid or some other UI for the user to browse and edit.

However, and I'm surprised I've not hit this problem before now, when one of the joined tables doesn't have a record for the (master) key, and the user tries to edit a field in that table, ADO gives the famous "Row cannot be located.." error on post.

As far as I understand this error, the ADO driver is trying to locate the record in order to update its fields - and, of course, in this instance, there is no record to find. What I was expecting in these circumstances would be that the ADO driver would issue the equivalent of an UPDATE query for the main table, but an INSERT query for the subsidiary table.

Has anyone else come across this problem and found a workround?

The ADO driver used is the Jet 4.0 OLE DB Provider connecting to an Access (mdb) database.

I have ensured that the primary keys fields for both tables are available in the query dataset for the driver to utilise.

Here is a basic version of the SQL I have using:

SELECT 
    Table1.CustomerNo, Table1.Field1, Table1.Fieldn,  
    Table2.CustomerNo, Table2.Field1, Table2.Fieldn  
FROM 
    Table1 
LEFT JOIN Table2 
    ON Table1.CustomerNo = Table2.CustomerNo  
WHERE 
    Table1.CustomerNo = Newcode;

As an experiment, I tried the same thing in MS Access 2007, and that worked, so there maybe a solution within ADO (but then Access is probably using a different driver).

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

跨年 2024-11-15 01:11:31

最后,我选择了“黑客”解决方案。仅在以下情况下才可以对 2 个表进行可更新的 ADO 查询:
客户的记录存在于两个表中。因此,我必须检查这一点,如果 table2 中缺少相关记录,则必须在调用主查询之前先将其插入。代码如下:

   //Query customer account - open query from Table1 and Table2 tables                     
   TwoTableQuery.Close;
   //
   //SELECT Table2.CustomerNo
   // FROM Table2
   //WHERE (Table2.CustomerNo = CustomerCode);
   Table2Query.Close;
   Table2Query.Parameters[0].Value := CustomerCode;
   Table2Query.Open;                                /
   //does the Table2 record exist for this customer?
   if Table2Query.RecordCount = 0 then
   begin   //no, so create a new record
      Table2Query.Insert;
      Table2Query.FieldByName('CustomerNo').AsString := CustomerCode;
      Table2Query.Post;
      Table2Query.Close;
   end;
   //okay, now okay to open main query
   TwoTableQuery.Parameters[0].Value := CustomerCode;
   TwoTableQuery.Open;                                 

它效率不高,而且还会创建可能不需要的记录(在 Table2 中)。但这似乎是唯一的解决方案。

In the end, I went for the 'hack' solution. The updateable ADO query over 2 tables would only work if
the customer's reocrds existed in both tables. So, I had to check that, and if the relevant record was missing from table2, had to insert it first, before calling the main query. Here is the code:

   //Query customer account - open query from Table1 and Table2 tables                     
   TwoTableQuery.Close;
   //
   //SELECT Table2.CustomerNo
   // FROM Table2
   //WHERE (Table2.CustomerNo = CustomerCode);
   Table2Query.Close;
   Table2Query.Parameters[0].Value := CustomerCode;
   Table2Query.Open;                                /
   //does the Table2 record exist for this customer?
   if Table2Query.RecordCount = 0 then
   begin   //no, so create a new record
      Table2Query.Insert;
      Table2Query.FieldByName('CustomerNo').AsString := CustomerCode;
      Table2Query.Post;
      Table2Query.Close;
   end;
   //okay, now okay to open main query
   TwoTableQuery.Parameters[0].Value := CustomerCode;
   TwoTableQuery.Open;                                 

It's not efficient, and also creates records (in Table2) that may not be needed. But it seems the only solution.

很快妥协 2024-11-15 01:11:31

首先尝试locate 函数以确保两个字段都存在。如果找到,则使参数等于该字段,布尔值为 true,否则使用另一个字段的值更新或插入新字段。之后,您知道该条目存在,并且可以加入字段...或者您可以使用 try except finally 来首先尝试并执行该语句,除非找不到该字段,然后最终创建新条目。将所有这些放入重复直到中,并确保直到语句在两个表上使用locate来查看是否找到它。

First try the locate function to make sure both fields are there. Make a parameter equal to the field if found and boolean true else update or insert new field with the value of the other one. After that you know the entry exists and you can join the fields...or you can use try except finally to first try and execute the statement except if the field is not found then finally create new entry. Put all of that in a repeat until and make sure the until statement uses locate on both tables to see if its found.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文