TMSFNCDataGrid Sorting issue

In my project, sorting the grid via the HeaderClick only works on the first click.
On the second click on the header, the sorting is random.

I cannot reproduce this in the example I sent.
https://support.tmssoftware.com/t/tmsfncdatagrid-filter-do-not-work-with-numbers/24875

It doesn't matter whether the column is an integer, date or string field.
For all columns the same effect after starting the program the sorting works correctly once and then never again.

Furthermore, the grid no longer reacts to changes in the dataset (change of the SQL Where statement).
If I fill the dataset with the datasource with completely different data, nothing changes in the grid, but my DBEdit fields change their data.

I have commented out all events in the grid and in the dataset but without success.

What can I do to refresh the DataGrid or the DatagridAdapter?
Query.Close and Query.open do not react, even DatabaseAdapter active=False and active=True do not reload the grid.
The grid is set to unbuffered.
As in my last example, I have set Connection to Query, query to DataSource, Datasource to Databaseadapter and Databaseadapter to Grid.

Please give me tips on the best way to achieve a change in the grid again.

This are Pictures of the first and second click on SortColumn:
SortKasse1
SortKasse2

It's strange that the grid doesn't react anymore. If the problem is not reproducible in the sample, then there must be code that is executed that interferes with this process. Maybe an idea is to comment code, or rebuild the demo you have to enable sorting. Did you change settings on the dataset? In applications of this kind, it's important to know which properties and code that has been executed.

I found the Problem why the Grid not do anything if i change the SQL in the Dataset.
There are 4 Forms with DataGrids and DatabaseAdapters.
I named all DatabaseAdapters with the same Name and thought this wont be a problem because they are all at a another Form.
But this was wrong.
After giving any DatabaseAdapter a uniqe name then it works like before with one Form.

But now to the sorting:
After starting the Program a click on a header works fine and sort this column.
After another click to achieve the opposite sorting the column do not change.
No changing in the Dataset.

Sorting is not implemented for UniDac, only for FireDac and Client-DataSet

That means I have to decide whether to use TMS or Devart UniDAC, both are not possible.
Good to know.

The issue is sadly a technical limitation. While filtering is an abstract implementation, sorting needs to be implemented for each dataset individually. Since we cannot rely on UniDac being installed we cannot include built in filtering. There are events triggered so let me come up with a solution for your application, stay tuned

I only use unidac because no database connections were allowed in the previous delphi Pro versions. If I have understood correctly, i can now also work with Firedac from Delphi 12.3 Pro onwards. Then I could also switch back to FireDAC. My test project seems to run with it.
But can I also work with client-server databases with Delphi 12.3 Pro? I haven't tested that yet and my program is supposed to work with a Firebird server later on.

Hi,

Unclear, we are not experts in that area unfortunately. You can find a full feature matrix here

Like this i can use Singleuser Databases in Delphi Pro but no clientServer Databases like Firebird.
So i need my UniDac Component to use my program with more than one user.
I hope you could find an option to use TTMSFNCDatagrid with Devart UniDAC.
(it runs in 01.2025 with the UniDAC Component)

You could implement the OnSortData on database adapter level and add sort code for your dataset. You can access TMSFNCDataGrid1.SortIndexList in order to know which columns are sorted and in which direction. If you are using a query you can rebuild the query and add sort by statements.

FYI, here is a piece of code to handle sorting, when LoadMode = almBuffered

Implement the OnSortData event on TTMSFNCDataGridDatabaseAdapter

procedure TForm1.DoSortData(Sender: TObject);
var
  c: Integer;
  f: TField;
  uq: TUniQuery;
  s: TTMSFNCDataGridSortIndex;
  n, fn: string;
begin
  if Adapter.DataLink.DataSet is TUniQuery then
  begin
    uq := Adapter.DataLink.DataSet as TUniQuery;
    uq.IndexFieldNames := '';
    fn := '';

    for s in Grid1.SortIndexList do
    begin
      if s.Direction <> gsdNone then
      begin
        c := s.Column - Grid1.FixedColumnCount;
        f := Adapter.FieldAtColumn[c];
        if Assigned(f) and (f.FieldKind in [fkData, fkInternalCalc, fkLookup]) then
        begin
          n := f.FieldName;

          if s.Direction = gsdDescending then
            n := n + ' DESC'
          else
            n := n + ' ASC';

          if fn = '' then
            fn := n
          else
            fn := fn + ';' + n;
        end;
      end;
    end;

    uq.IndexFieldNames := fn;
  end;
end;

The sorting is working with this Code.
Great Job.

Next Job is filtering, do you have also a code for this?

Now after click on sort all Columns are shown in the grid not only the first 14 selected Columns. Maybe i have to set the columnmaxCount?
I want to see only the first 14 Columns in the Grid, at wich procedure i have to set this?

Filtering should work out of the box, it's implemented for TDataSet. If filtering does not work (assuming you use LoadMode = almBuffered), then it's because the TDataSet your are using hasn't implemented filtering. Does filtering work effectively when trying it out on your dataset

Instead of setting AutoCreateColumns to True, set it to False, and then add columns to your adapter manually, specifying which fields you need to show. Alternatively, you can also add all fields in the dataset and then remove the field definitions, or set Visible to False

Thanks for the Tips.
I played with the AutoCreateColumns and dont reset it to false afterthat.
That was the problem.

The Filtering in my FireDAC Project works fine but in the UniDAC Project it only search with the first Char in the search.
With the searchcriterium 'b' all Data is found starting with 'b'.
If i search for 'ba' nothing is found. (There are names like 'Bart').

this is the Propertylist from my UniDAC component for Filtering:
UniDACQueryProps

This is the code i am using to filter in the TAdvedit.change:

procedure TfrmKunden.edtSucheChange(Sender: TObject);
begin
  SearchNV(edtSuche.Text,gridKunden,0,1);
end;

{-------------------------------------------------------------------------------
Suchen nach Nachname und Vorname
-------------------------------------------------------------------------------}
procedure SearchNV(Search:String; grd:TTMSFNCDataGrid; NPos,VPos:Integer);
var
  Suchbegriff, Nachname, Vorname: string;
  SepPos: Integer;
begin
  Suchbegriff := Trim(Search);
  Grd.Filter.Clear;

  if Suchbegriff = '' then
  begin
    Grd.ApplyFilter;
    Exit;
  end;

  // Prüfen, ob ein Komma enthalten ist
  SepPos := Pos(',', Suchbegriff);
  if SepPos > 0 then
  begin
    // Nachname vor dem Komma
    Nachname := Trim(Copy(Suchbegriff, 1, SepPos - 1));
    // Vorname nach dem Komma
    Vorname := Trim(Copy(Suchbegriff, SepPos + 1, Length(Suchbegriff) - SepPos));

    // Filter hinzufügen und CaseSensitive auf False setzen
    with Grd.Filter.Add do
    begin
      Condition := Nachname + '*'; // Bedingung für "StartsWith"
      Column := NPos; // Spalte "Nachname"
      CaseSensitive := False;
    end;

    with Grd.Filter.Add do
    begin
      Condition := Vorname + '*'; // Bedingung für "StartsWith"
      Column := VPos; // Spalte "Vorname"
      CaseSensitive := False;
    end;
  end
  else
  begin
    // Nur Nachname suchen
    with Grd.Filter.Add do
    begin
      Condition := Suchbegriff + '*'; // Bedingung für "StartsWith"
      Column := NPos; // Spalte "Nachname"
      CaseSensitive := False;
    end;
  end;

  Grd.ApplyFilter;
end;

Hope you could help me again to this problem.

We can't see any issues on our end, tested with CARS.mdb connected to a UniQuery.

image

And this is the filter code

  qTest.SQL.Text:='Select Brand,Date,Type,CC,PK,Price from CARS';
  qTest.Open;
  qTest.FilterOptions := [foCaseInsensitive];

  Grid1.Filter.Add(0, 'al*');
  Grid1.ApplyFilter;

Can you try executing the Filter directly on the dataset and see if that works? supposedly, you'll need to do something like:

cdDocs.DisableControls;
try
  cdDocs.Filtered := False;
  cdDocs.FilterOptions := [foCaseInsensitive];
  cdDocs.Filter := 'Product LIKE ''%' + txtFilter.Text + '%'''
  cdDocs.Filtered := True;
finally
  cdDocs.EnableControls;
end;

This is the test in edtSuche.change:

{ -------------------------------------------------------------------------------
  Suchen per Hand durch Eingabe in edtSuche
  ------------------------------------------------------------------------------- }
procedure TfrmKunden.edtSucheChange(Sender: TObject);
var
  Suchbegriff, Nachname, Vorname: string;
  SepPos: Integer;
begin
  Suchbegriff := Trim(edtSuche.Text);

  // Prüfen, ob ein Komma enthalten ist
  SepPos := Pos(',', Suchbegriff);
  if SepPos > 0 then
  begin
    // Nachname vor dem Komma
    Nachname := Trim(Copy(Suchbegriff, 1, SepPos - 1)) + '%';
    // Vorname nach dem Komma
    Vorname := Trim(Copy(Suchbegriff, SepPos + 1, Length(Suchbegriff) - SepPos)) + '%';

    // Suchbegriff zusammensetzen
    if Vorname <> '' then
      Suchbegriff := 'Nachname LIKE ''' + Nachname + ''' AND Vorname LIKE ''' + Vorname + ''''
    else
      Suchbegriff := 'Nachname LIKE ''' + Nachname + '''';
  end
  else
  begin
    Nachname := Suchbegriff + '%';
    Suchbegriff := 'Nachname LIKE ''' + Nachname + '''';
  end;

  qKunden.DisableControls;
  try
    qKunden.Filtered := False;
    qKunden.FilterOptions := [foCaseInsensitive];
    qKunden.Filter := Suchbegriff;
    qKunden.Filtered := True;
  finally
    qKunden.EnableControls;
  end;
end;

With this i do the searching from the editbox.
But how to search in the DataGridFilter?
i only need one column to filter.

Does this code work directly on the dataset with more than one character or not? It should have the same effect when applying the filter directly on the grid, if you have LoadMode = almBuffered, I hope this is still the case. The code to filter should work, unless you have an error in your filter statement. If you still run into issues, please create a new sample, with ready to use code which reproduces this. You can use a sample DB or data which we can test with.

Yes this code work with more than one Char and the Query qKunden and Datasource dsKunden is the query i have connected to my GridDatabaseAdapter.
LoadMode is allways almBuffered.