Delphi ADO：使用数据集过滤器查找错误 [英] Delphi ADO : Locate with dataset filter on bug

问题描述

我有一个简单的查询，它返回以下行：

 名称值
 Peter 1 
彼得2 
彼得3 
约翰1 
约翰2 
  

申请过滤器：

  ADO.Filter：='Name =John'; 
 ADO.Filtered：= True; //现在，它在数据集中只有2行
 ADO.Locate（'Value'，2）; 
  

光标应指向John 2，但它指向Peter 2通过过滤器）。并找到返回True。

在Delphi 7上试用，Rad studio XE 6.似乎这个错误在过去15年里生活在
任何解决方案？

解决方案

TCustomADODataSet.Locate 是内部使用 Recordset.Clone ，并尝试在克隆的记录集中查找记录，而不将筛选器设置为克隆的记录集（请参阅ADODB TCustomADODataSet.LocateRecord ）。

从文档中的注释：

原始的Filter属性Recordset（如果有），不会将
应用于克隆。设置新的Recordset
的Filter属性过滤结果。复制任何现有的Filter
值的最简单的方法是直接分配它，如下所示。 rsNew.Filter =
rsOriginal.Filter新创建的克隆的当前记录设置为
到第一个记录。

我已经使用我自己的简单的为过滤的ADO DataSet定位函数：基本上，存储当前书签，移动到第一个记录并迭代DataSet，直到找到比赛。如果没有找到匹配项可以恢复以前的书签。

Bellow是一个非常有限的实现，适用于我（很多要做完美的解决方案）：

class function TData.Locate（DataSet：TDataSet; const KeyFields：string;
const KeyValues：Variant; Options：TLocateOptions）：Boolean;
{一个非常简单的定位函数 - todo：TLocateOptions&多个KeyFields / KeyValues}
var
BM：TBookmarkStr;
begin
结果：= False;
如果DataSet.IsEmpty然后退出;
BM：= DataSet.Bookmark;
DataSet.DisableControls;
try
DataSet.First;
而不是DataSet.Eof do
begin
如果DataSet.FieldByName（KeyFields）.Value = KeyValues then
begin
结果：= True;
休息;
结束
DataSet.Next;
结束
如果不是结果然后DataSet.Bookmark：= BM;
finally
DataSet.EnableControls;
结束
结束

另一个选项是修补ADODB.pas TCustomADODataSet.LocateRecord 并设置 FLookupCursor.Filter 以匹配当前的数据集过滤器。只要将ADODB.pas作为新的副本放在项目文件夹中，该选项就可以接受。

另一个选项是使用 TCustomADODataSet.Recordset.Find 方法（另见：如何使用RecordSet.Find与TADOQuery？）。

I have as simple query, which returns following rows:

Name  Value
Peter   1
Peter   2
Peter   3
John    1
John    2

Applying filter:

 ADO.Filter := 'Name="John"';
 ADO.Filtered := True; // Now, its only 2 rows in dataset
 ADO.Locate('Value', 2); 

Cursor should point to "John 2", but it points to "Peter 2" (which being filtered out by filter). And locate returns True.

Tried on Delphi 7, Rad studio XE 6. It seems that this error is living there for the last 15 years Any solution ?

解决方案

The problem with TCustomADODataSet.Locate is that it's internally using Recordset.Clone and trying to locate a record in the cloned recordset, without setting the filter to the cloned recordset (see ADODB TCustomADODataSet.LocateRecord).

From the Remarks in the docs:

The Filter property of the original Recordset, if any, will not be applied to the clone. Set the Filter property of the new Recordset to filter the results. The simplest way to copy any existing Filter value is to assign it directly, as follows. rsNew.Filter = rsOriginal.Filter The current record of a newly created clone is set to the first record.

I have been using my own simple Locate function for filtered ADO DataSets: Basically, storing the current bookmark, moving to the first record and iterating the DataSet until it found a match. If no match found restore the previous bookmark.
Bellow is a really limited implementation that worked for me (a lot of todo tho for a perfect solution):

class function TData.Locate(DataSet: TDataSet; const KeyFields: string;
  const KeyValues: Variant; Options: TLocateOptions): Boolean;
{ a very simple Locate function - todo: TLocateOptions & multiple KeyFields/KeyValues }
var
  BM: TBookmarkStr;
begin
  Result := False;
  if DataSet.IsEmpty then Exit;
  BM := DataSet.Bookmark;
  DataSet.DisableControls;
  try
    DataSet.First;
    while not DataSet.Eof do
    begin
      if DataSet.FieldByName(KeyFields).Value = KeyValues then
      begin
        Result := True;
        Break;
      end;
      DataSet.Next;
    end;
    if not Result then DataSet.Bookmark := BM;
  finally
    DataSet.EnableControls;
  end;
end;

Another option is to patch ADODB.pas TCustomADODataSet.LocateRecord and set the FLookupCursor.Filter to match the current dataset filter. This option is acceptable as long as you patch ADODB.pas as a new copy placed in your project folder.

Yet another option is to use TCustomADODataSet.Recordset.Find method (See also: How to use a RecordSet.Find with TADOQuery?).

这篇关于Delphi ADO：使用数据集过滤器查找错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！