Board index » delphi » Resorting TQuery data

Resorting TQuery data

I have a dbgrid that gets its data from a tquery. I want to be able to
resort the grid based on the user clicking a column on it. Do I need to
issue a new query every time the user clicks on a col. or is there a way to
resort the records in memory?????

Thanks,

-Mauricio

 

Re:Resorting TQuery data


If you use the ClientDataSet, then you can build indexes on those keys.  But
you need the CS version of Delphi 3.  Otherwise, yes, you need to run the
query again with the an Order By clause.

Quote
Mauricio Korbman wrote in message <69qjn5$f...@forums.borland.com>...
>I have a dbgrid that gets its data from a tquery. I want to be able to
>resort the grid based on the user clicking a column on it. Do I need to
>issue a new query every time the user clicks on a col. or is there a way to
>resort the records in memory?????

>Thanks,

>-Mauricio

Re:Resorting TQuery data


Quote
Steve Zimmelman wrote:

> If you use the ClientDataSet, then you can build indexes on those keys.  But
> you need the CS version of Delphi 3.  Otherwise, yes, you need to run the
> query again with the an Order By clause.

> Mauricio Korbman wrote in message <69qjn5$f...@forums.borland.com>...
> >I have a dbgrid that gets its data from a tquery. I want to be able to
> >resort the grid based on the user clicking a column on it. Do I need to
> >issue a new query every time the user clicks on a col. or is there a way to
> >resort the records in memory?????

        Or you can batch copy your query dataset to a temporary table, add a
primary and several secondary indexes for each sort case, then link it
to your grid. This works with any flavour of Delphi.

        Servus,         Alin
__________________________________________________________________
   Alin Flaider       mailto:aflai...@datalog.ro
   Delphi home page:  http://www.datalog.ro/delphi/delphires.html
   Datalog Computers, http://www.datalog.ro, tel/fax +40 65 169867

Re:Resorting TQuery data


Quote
Alin Flaider wrote:

> Steve Zimmelman wrote:

> > If you use the ClientDataSet, then you can build indexes on those keys.  But
> > you need the CS version of Delphi 3.  Otherwise, yes, you need to run the
> > query again with the an Order By clause.

> > Mauricio Korbman wrote in message <69qjn5$f...@forums.borland.com>...
> > >I have a dbgrid that gets its data from a tquery. I want to be able to
> > >resort the grid based on the user clicking a column on it. Do I need to
> > >issue a new query every time the user clicks on a col. or is there a way to
> > >resort the records in memory?????

>         Or you can batch copy your query dataset to a temporary table, add a
> primary and several secondary indexes for each sort case, then link it
> to your grid. This works with any flavour of Delphi.

>         Servus,         Alin
> __________________________________________________________________
>    Alin Flaider       mailto:aflai...@datalog.ro
>    Delphi home page:  http://www.datalog.ro/delphi/delphires.html
>    Datalog Computers, http://www.datalog.ro, tel/fax +40 65 169867

Try this generic function: (It uses Infopower wwDBGrid and wwQuery
You can modify to use standard DB controls.)

unit uSort;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
Dialogs, Menus, Grids, Wwdbigrd, Wwdbgrid, DB, DBTables, Wwquery,
Wwdatsrc,ExtCtrls, DBCtrls, StdCtrls;

Type
  TGridSort = Class(TObject)
Private
  {Private Declarations}
Public
  {Public Declarations}
  procedure GridSort(Sender : TObject; Direction : String);
end;

implementation

procedure TGridSort.GridSort(Sender : TObject; Direction : String);
var
  GridPos     : integer;
  sList       : TStringList;
  sSql        : String;
  sStr        : String;
  strIndex,
  strCount    : integer;
  sSort       : String;
  sDataSet    : TWWQuery;
  OriginalSQL : String;

  position    : integer;
  intLength   : Integer;
  bIsNull : Boolean;
begin
  GridPos  := TwwDBGrid(Sender).getactiveCol;
  if ((TwwDBGrid(Sender).fields[GridPos-1].dataType <> ftMemo) and
      (TwwDBGrid(Sender).fields[GridPos-1].dataType <>  ftBlob) and
      (TwwDBGrid(Sender).fields[GridPos-1].dataType <>  ftgraphic)) then
  Begin
    OriginalSQL :=
UpperCase(twwquery(twwdbgrid(sender).datasource.dataset).sql.text);
    if Pos('ISNULL', OriginalSQL) > 0 then
      bIsNull := True;
    {Pos(Substr: string; S: string): Integer;}
    Position    := pos('ORDER',OriginalSQL);
    intLength   := Length(OriginalSQL);
    {Delete(var S: string; Index, Count:Integer);}
    Delete(OriginalSQL,Position,(intLength-Position)-1);
    if UpperCase(Direction) = 'ASCENDING' then
      sSort := ' order by %s asc'
    Else if UpperCase(Direction) = 'DESCENDING' then
      sSort := ' order by %s desc';
    slist    := Tstringlist.create;
    sDataSet := TWWQuery(TwwDBGrid(Sender).DataSource.DataSet);
    sDataSet.getfieldnames(sList);
    sStr     := sList.strings[GridPos - 1];
    sSql     := sDataSet.sql.text;
    strIndex := pos(sStr, sSql);
    if strIndex <> 0 then
      begin    //needed for select * type sql statements
        StrCount := strIndex;
        if not bisNull then
        begin
          repeat dec(strIndex) until sSQL[strIndex] = ' ';
            strCount := (strCount + length(sStr)) - StrIndex;
        end;
        with sDataSet do begin
          close;
          if bisNull then
            sql.text := originalSQL + Format(sSort,[sStr])
          else
            sql.text := originalSQL +
format(sSort,[copy(sSQL,strIndex,StrCount)]);
          open;
       end; //with q1
     end  // if..else statement
     else begin
       with sDataSet do begin
         close;
         sql.text := originalSQL + format(sSort,[sStr]);
         open;
       end;
     end;
     sList.free;
   End
   Else
    MessageDlg('Cannot sort on this field', mtinformation, [mbOK], 0);
end;

end.

--
Eddie Shipman

Other Threads