Why Locate() in TADOTable with date/time value is not working

Question

I'm working on a small subsystem for logging user activity. The system is using MS SQL Server as a database, Delphi7 and ADO for building the interface.

The problem I have is that I can't locate a record with specific datetime value.

Below is a sample reproduction of the problem:

1. Database: MS SQL Server 2005 Express Edition.

-- Table creation
CREATE TABLE [tlog] (
    [USERN] [numeric](10, 0) NULL,
    [USERDATE] [datetime] NULL,
    [LOGTEXT] [varchar](250) COLLATE Cyrillic_General_CS_AS NULL
);

-- Insert date/time value
INSERT INTO [tlog] (USERN, USERDATE, LOGTEXT)
VALUES (1, CURRENT_TIMESTAMP, 'Record current activity')
-- Insert date only value
INSERT INTO [tlog] (USERN, USERDATE, LOGTEXT)
VALUES (1, '20180202', 'Record current activity')

-- Table's content
-------------------------------------------------------------
| USERN |       USERDATE          |          LOGTEXT        |
-------------------------------------------------------------
| 1     | 26/10/2015 17:13:36.597 | Record current activity |
-------------------------------------------------------------
| 1     | 02/02/2018 00:00:00.000 | Record current activity |
-------------------------------------------------------------

2. Sample code: Delphi 7 and ADO

procedure TfrmMain.btnLocateClick(Sender: TObject);
var
   d: TDateTime;
   tblLog: TADOTable;
begin
   // 
   ThousandSeparator         := ' ';
   DecimalSeparator          := '.';
   DateSeparator             := '/';
   ShortDateFormat           := 'dd/mm/yyyy';
   LongDateFormat            := 'dd/mm/yyyy';
   TimeSeparator             := ':';
   ShortTimeFormat           := 'hh:mm';
   LongTimeFormat            := 'hh:mm';
   TwoDigitYearCenturyWindow := 50;
   ListSeparator             := ';';
   
   //
   tblLog := TADOTable.Create(Application);
   try
   
      //
      tblLog.ConnectionString :=
         'Provider=SQLOLEDB.1;'+
         'Password=xxxx;'+
         'Persist Security Info=True;'+
         'User ID=xxxxxxxx;'+
         'Initial Catalog=xxxxxxxxx;'+
         'Data Source=127.0.0.1\xxxxxxx,1066';
      tblLog.TableName := '[tlog]';
      tblLog.Open;
      
      // First try - locate with exact value. NOT WORKING.
      d := StrToDateTime('26/10/2015 17:13:36.597');
      if tblLog.Locate('USERDATE', d, []) then
         ShowMessage('Exact value, no Locate options: Located')
      else
         ShowMessage('Exact value, no Locate options: Not located');
      if tblLog.Locate('USERDATE', d, [loPartialKey]) then
         ShowMessage('Exact value, with Locate options: Located')
      else
         ShowMessage('Exact value, with Locate options: Not located');
      
      // Second try - locate with value that matches format settings. NOT WORKING.
      d := StrToDateTime('26/10/2015 17:13');
      if tblLog.Locate('USERDATE', d, []) then
         ShowMessage('Hours and minutes, no Locate options: Located')
      else
         ShowMessage('Hours and minutes, no Locate options: Not located');
      if tblLog.Locate('USERDATE', d, [loPartialKey]) then
         ShowMessage('Hours and minutes, with Locate options: Located')
      else
         ShowMessage('Hours and minutes, with Locate options: Not located');
      
      // Locate with date only value. WORKING.
      d := StrToDateTime('02/02/2018');
      if tblLog.Locate('USERDATE', d, []) then
         ShowMessage('Located')
      else
         ShowMessage('Not located');

   finally
      //
      tblLog.Close;
      tblLog.Free;
   end;   
end;

3. Expected result: Locate the record.

4. Actual result: TADOTable.Locate() returns false.

What am I doing wrong and how to pass datetime values to TADOTable.Locate() method?

Thanks in advance!

Answer 1

You have used Locate almost correctly. Almost, because the loPartialKey option you've included is pointless when searching for TDateTime values. In this case you need to search the exact date time value. The problem is in your tests.

Your first test has a wrong date time value. Its millisecond portion is ignored in your conversion so you're actually trying to locate date time 26/10/2015 17:13:36 which is not in your table.

In second case you're trying to locate date time 26/10/2015 17:13 which is not in your table.

I would suggest using e.g. EncodeDateTime function for building date time rather than that string conversion and removing those extra calls with loPartialKey option.