date in TAdvDBFilterPanel

VCL TMS VCL UI Pack
1

L.S.,

I'm having trouble with the filtering in a DBAdvGrid with date-values.
I suspect a little bug here:
[AdvDBFilterPanel.pas line 650]

    // datetimepickers
    if C.Controls[II] is TDateTimePicker then
    begin
      if FSQLDateFormat <> '' then
        D.Value := FormatDateTime(FSQLDateFormat, TDateTimePicker(C.Controls[II]).Date)
      else
        D.Value := DateToStr(TDateTimePicker(C.Controls[II]).Date);

      D.Value := StringReplace(D.Value, ' ', '', [rfReplaceAll]);
      Con := StringReplace(Con, '{SEARCH}', '"' + D.Value + '"',[rfReplaceAll, rfIgnoreCase]);
      Con := StringReplace(Con, '''',  FSQLDateSeparator ,[rfReplaceAll, rfIgnoreCase]);

      cons.Add(con);
    end;

I think the last StringReplaces should be something like this, because now there are allways double-quotes around the literal date-value. My RDBMS and/or DB-components seem to "prefer" single quotes...

      D.Value := StringReplace(D.Value, ' ', '', [rfReplaceAll]);
      Con := StringReplace(Con, '{SEARCH}', '"' + D.Value + '"',[rfReplaceAll, rfIgnoreCase]);
      if FSQLDateSeparator <> '' then
         Con := StringReplace(Con, '"',  FSQLDateSeparator ,[rfReplaceAll, rfIgnoreCase]);

So, possibly 2 things wrong here:

  • the replace is not conditional
  • the replace uses 2 quotes instead of a single double quote.

I I'm am wrong and it is supposed to function like this (my RDBMS MS SQL-Server, DB components SDAC from devart) please give me directions how to configure correctly.

With regards,

Peet Terluin

2

Thanks for reporting.
You're correct about this issue. We have addressed this. Next update will have this fixed.

1 Like
3

Thanks for the update.
However, there is still an issue.
The query-expression is build using the "placeholder" '{SEARCH}' (including single quotes),
then on line 654 the replace is executed:

          Con := StringReplace(Con, '{SEARCH}', '"' + D.Value + '"',[rfReplaceAll, rfIgnoreCase]);

Here the single quotes are omitted in the '{SEARCH}' and will not be replaced in the expression.
Therefore these single quotes can not be replaced by my own delimiter.

with regards,
Peet Terluin

4

We will fix it with:

      D.Value := StringReplace(D.Value, ' ', '', [rfReplaceAll]);
      Con := StringReplace(Con, '''{SEARCH}''',  '{SEARCH}' ,[rfReplaceAll, rfIgnoreCase]);
      Con := StringReplace(Con, '{SEARCH}', '"' + D.Value + '"',[rfReplaceAll, rfIgnoreCase]);
      if FSQLDateSeparator <> '' then
        Con := StringReplace(Con, '"',  FSQLDateSeparator ,[rfReplaceAll, rfIgnoreCase]);