[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

TDBGrid Change the order of the data.

Hello,

I am an extreme noob to delphi and have been thrown into tons of code
to make minor changes.  I have a TDBGrid that imports from a dm.
The information it is importing is correct.  There are several fields, one
of which is date.  Currently the information is ascending by date.  So the
newest entry is the last entry.  I need this to be the exact opposite.  I need
the newest entry to be at the top of the list.  Is there a way to change
how the data is brought in?


Thanks for the help!!
0
c230kochi
Asked:
c230kochi
  • 12
  • 10
  • 6
  • +1
1 Solution
 
vadim_tiCommented:
TDBGrid only displays your dataset,
so you need to supply dataset with right sortorder.
could you post how you build dataset?
0
 
c230kochiAuthor Commented:
No clue where to find that.  I know that its an adt that I use Arc32 to administer.
The dataset is called tblServices.  Under that info in the object inspector I don't
see anywhere to tell it to sort the incoming information.
0
 
vadim_tiCommented:
Select in a form "view as text", and post this form here
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
pcsentinelCommented:
Check out the code in your datamodule, the one supplying the data for the grid.

Is the data being supplied from a query control or a StoredProc control

If its a query control then you should be able to get at the underying query text and change it.

Have a look and come back to us

regards

0
 
c230kochiAuthor Commented:
object grdServices: TDBGrid
    Left = 6
    Top = 487
    Width = 501
    Height = 162
    DataSource = DM.srcServices
    Options = [dgTitles, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect, dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit]
    ReadOnly = True
    TabOrder = 16
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
    OnDblClick = grdServicesDblClick
    Columns = <
      item
        Expanded = False
        FieldName = 'SERV_DATE'
        Title.Caption = 'DATE'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'TYPE'
        PickList.Strings = (
          'Oil Change'
          'New Tires'
          'WindShield Replaced'
          'Brakes'
          'Muffler')
        Width = 189
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'COST'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'LOCATION'
        Width = 158
        Visible = True
      end>
  end
0
 
vadim_tiCommented:
please make the same with DM unit,
we want to know what is a dataset for DM.srcServices
0
 
c230kochiAuthor Commented:
Where do I go to get the information for the DM.srcServices?
0
 
pcsentinelCommented:
you should have a datamodule called DM

have a look at that and find

srcServices

I am presuming this is a datasource component

open the object inpector and find the Dataset entry.

This is the control that is supplying the data for your grid

Let us know what the control is.

If its a TQuery look for the the SQL entry in Object inspector

regards
0
 
vadim_tiCommented:
found your DM.pas unit
view as form
view as text
post here
0
 
c230kochiAuthor Commented:
object tblServices: TAdsTable
    IndexDefs = <
      item
        Name = 'SERV_DATE'
        Fields = 'SERV_DATE'
      end
      item
        Name = 'VIN'
        Fields = 'VIN'
      end>
    IndexName = 'VIN'
    MasterFields = 'VIN'
    MasterSource = srcVehicles
    StoreActive = True
    Version = '6.2 Beta (ACE 6.11)'
    AdsConnection = AdsConnection1
    FieldDefs = <
      item
        Name = 'SERV_DATE'
        DataType = ftDate
      end
      item
        Name = 'TYPE'
        DataType = ftString
        Size = 40
      end
      item
        Name = 'COST'
        DataType = ftFloat
      end
      item
        Name = 'LOCATION'
        DataType = ftString
        Size = 40
      end
      item
        Name = 'MILEAGE'
        DataType = ftFloat
      end
      item
        Name = 'VIN'
        DataType = ftString
        Size = 15
      end>
    StoreDefs = True
    TableName = 'SERVICES.ADT'
    Left = 38
    Top = 352
    object tblServicesSERV_DATE: TDateField
      FieldName = 'SERV_DATE'
    end
    object tblServicesTYPE: TStringField
      FieldName = 'TYPE'
      Size = 40
    end
    object tblServicesCOST: TFloatField
      FieldName = 'COST'
      currency = True
    end
    object tblServicesLOCATION: TStringField
      FieldName = 'LOCATION'
      Size = 40
    end
    object tblServicesMILEAGE: TFloatField
      FieldName = 'MILEAGE'
    end
    object tblServicesVIN: TStringField
      FieldName = 'VIN'
      Size = 15
    end
  end
0
 
pcsentinelCommented:
Dont know if this will work as not knowledgeable about your database

in the datamodule select

tblServices
in object inspector
expand indexdefs

select SERV_DATE
 expand options in object inspector

change ixdescending to true from false

regards
0
 
c230kochiAuthor Commented:
ixdescending is set to false.  I tried changing it to true with no result.
0
 
vadim_tiCommented:
Add TADSQuery component to unit with TDBGrid

Set its ADSConnection property to DM.AdsConnection1

Set its SQL property to

SELECT SERV_DATE, TYPE, COST, LOCATION
FROM "'SERVICES.ADT"
WHERE VIN=:VIN
ORDER BY SERV_DATE DESC

Set its DataSource property to srcVehicles

Add TdataSource component to the same unit
set its Dataset property to TADSQuery1

Change grdServices.DataSource property to TAdsQuery1

Set TAdsQuery1 "Active" property to true and see in IDE if you get what you wanted
0
 
pcsentinelCommented:
This means that it is reading straight from the database

and the SERVICES.ADT table on the database is indexed on SERV_DATE ascending

the only way of changing that is to modify the database.

You may be able to subtitue a TADSQuery component

and in the SQL strings add something like

SELECT SERV_DATE,TYPE,COST,LOCATION,MILEAGE, VIN FROM SERVICES.ADT ORDER BY SERV_DATE DESC

then point the datasource at the TADSQuery instaead of the TADSTable

but without more knowlegde of your databases I can't be sure

regards
0
 
c230kochiAuthor Commented:
Will this use the same information that is currenlty in the database?
This is an existing program with a database full of records.


thx for the help!
0
 
vadim_tiCommented:
are you tried it?
if it will work it will work proper and will use information currnetly in database
but i do not have ADS components you use, so i cannot check it here
0
 
pcsentinelCommented:
Yes it will, but ideally you would want to get an new index added to the database table, you could then just use that

It would work much quicker than the query

regards
0
 
EddieShipmanCommented:
If you modify your app to use an AdsQuery you can use this code to have sorting by clicking on the header of your DBGrid:

Save the unit uGridSort below and place it in your uses, i.e.
  uses .., uGridSort;

Then, in your DBGrid's OnTitleClick:

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
 with TGridSort.Create do
 begin
   // First param is the column that was clicked on,
   // second, sort order
   GridSort(Column, 'ASCENDING');
   Free;
 end;
end;


Now for the uGridSort Unit:

unit uGridSort;

interface

uses
  { I'm not sure I got all the units needed from Advantage to get to the
    TAdsQuery, fix if needed.
  }
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Menus, Grids, DBGrids, DB, DBTables, ExtCtrls, DBCtrls, StdCtrls,
  AdsData, AdsFunc, AdsTable;

Type
  TGridSort = Class(TObject)
  private
    {Private Declarations}
  public
    {Public Declarations}
    procedure GridSort
      ( ASender:                   TColumn
      ; ADirection:                String
      );
end;

implementation

procedure TGridSort.GridSort
  ( ASender:                   TColumn
  ; ADirection:                String
  );
var
  LGridPos:                    Integer;
  LList:                       TStringList;
  LSql:                        String;
  LStr:                        String;
  LIndex,
  LCount:                      Integer;
  LSort:                       String;
  LDataset:                    TAdsQuery;
  LOriginalSQL:                String;
  LPos:                        Integer;
  intLength:                   Integer;
begin
  LGridPos := TColumn(Sender).Index; // Get the active column for this grid
  LDataset := TAdsQuery(TColumn(Sender).Grid.Datasource.Dataset);
  if ((TColumn(Sender).Field.dataType <> ftMemo) and
      (TColumn(Sender).Field.dataType <> ftBlob) and
      (TColumn(Sender).Field.dataType <> ftgraphic)) then
  begin
    LOriginalSQL := LDataset.SQL.Text;
    LPos         := Pos('order',OriginalSQL);
    LLength      := Length(OriginalSQL);
    Delete(LOriginalSQL,LPos,(LLength-LPos)-1);

    if UpperCase(LDirection) = 'ASCENDING' then
    begin
      LSort := 'order by %s asc';
    end;
    else
    begin
      if UpperCase(LDirection) = 'DESCENDING' then
      begin
        LSort := 'order by %s desc';
      end;
    end;

    LList    := TStringlist.Create;
    try
      LDataset.GetFieldNames(LList);
      LStr := LList.Strings[LGridPos];
      LSql := LDataset.SQL.Text;
      LIndex := Pos(LStr, LSql);
      if LIndex <> 0 then
      begin // needed for select * type sql statements
        LCount := LIndex;
        repeat
          Dec(LIndex)
        until LSQL[LIndex] = ' ';
        strCount := (LCount + Length(LStr)) - LIndex;
        with LDataset do
        begin
          Close;
          SQL.Text := LOriginalSQL + Format(LSort,[Copy(LSQL,LIndex,LCount)]);
          Open;
        end; // with LDataset
      end // if..else statement
      else
      begin
        with LDataset do
        begin
          Close;
          SQL.Text := LOriginalSQL + Format(LSort,[LStr]);
          Open;
        end;
      end;
    finally
      LList.Free;
    end;
  end
  else
  begin
     MessageDlg('Cannot sort on this field', mtInformation, [mbOK], 0);
  end;
end;

end.
0
 
c230kochiAuthor Commented:
I can add an index to the database, but when I tell it to you descending order it doesn't change anything in the program.  Is there a better program to open this adt file with, that will give me the ability to create a new index that works?
0
 
pcsentinelCommented:
How are you creating the index?

Are you using something like

tblServices.AddIndex('VINDesc','VIN',[ixDescending,ixCaseInsensitive],'VIN');

This should create a descending index on VIN called VinDesc

After running this once only change the index in tblServices in the DM module

regards


0
 
c230kochiAuthor Commented:
The indexes are already created using a program called Advanatage Data Architect.  In that program there is a index
management area that lets me add or delete indexes.  If I go to create a new index I have these choices for available
fields.

SERV_DATE
TYPE
COST
LOCATION
MILEAGE
VIN

Then on the right side I have "Index File Name" which is SERVICES.

Below this is Index Name

Then Index Key Expression

Then Condition Expression

Then While Expression


Checkmark boxes at the bottom are "Descending", "Unique", and "Custom".



thx
0
 
pcsentinelCommented:
Ok, you've said you created a descending index.

Did you then go back to the program and select that index?

If not open the datamodule click on tblservices.
In the object inspectordropdown the indexname listbox and see what you've got.

let us know whats in there

regards
0
 
c230kochiAuthor Commented:
Awesome,

that worked.  I recreated the index using VIN and not SERV_DATE and set it to descending.

New problem that happened somewhere along the way, my original file has an add button
that adds an additional field to the TDBGrid.  In the new file when I press on the add button
it takes me to the last entry to edit the data instead of adding a new line.  any ideas?

thx


0
 
pcsentinelCommented:
Can you post the code behind the button please?

regards
0
 
c230kochiAuthor Commented:
object Button3: TButton
    Left = 124
    Top = 464
    Width = 75
    Height = 23
    Caption = 'Add'
    Font.Charset = ANSI_CHARSET
    Font.Color = clWindowText
    Font.Height = -13
    Font.Name = 'Tahoma'
    Font.Style = [fsBold]
    ParentFont = False
    TabOrder = 19
    OnClick = Button3Click

I tried comparing my old and new file and can't seem to find where there was a change.

thx
0
 
pcsentinelCommented:
No not the form info, the programming code.

right click on the above and go View> As Form

then double click on the button you are talking about and you should see the code behind it

regards
0
 
c230kochiAuthor Commented:
procedure TfrmVehicle.Button3Click(Sender: TObject);
begin
    frmService.Add;
//  dm.tblServices.Append;
//  dm.tblServicesVIN.Value:=dm.tblVehiclesVIN.Value;
//  grdServices.SetFocus;
end;


this is the code from frmService


type
  TfrmService = class(TForm)
    wwDBDateTimePicker1: TwwDBDateTimePicker;
    Label1: TLabel;
    Label2: TLabel;
    edtServiceType: TDBComboBox;
    Label3: TLabel;
    edtLocation: TDBEdit;
    edtMileage: TDBEdit;
    Label4: TLabel;
    btnOK: TBitBtn;
    btnCancel: TBitBtn;
    edtCost: TDBEdit;
    Label5: TLabel;
    procedure btnOKClick(Sender: TObject);
    procedure btnCancelClick(Sender: TObject);
  private
    { Private declarations }
  public
    procedure Add;
    { Public declarations }
  end;

var
  frmService: TfrmService;

implementation
  uses Database;
{$R *.dfm}
procedure TfrmService.Add;
begin
  dm.tblServices.Append;
  showmodal;
end;

procedure TfrmService.btnOKClick(Sender: TObject);
begin
  dm.tblServices.FieldByName('vin').AsString:=dm.tblVehicles.fieldbyname('vin').AsString;
  dm.tblServices.Post;
  Close;
end;

procedure TfrmService.btnCancelClick(Sender: TObject);
begin
  dm.tblServices.Cancel;
end;

end.





sorry.... :)


thx
0
 
pcsentinelCommented:
So Button3 on frmVehicle calls the add function in frmService which adds a record to the end of the table then shows frmVehicle modally.


When you click on the ok button on frmService it sets the vin field to the same as table vehicles

I dont see anything wrong, are you saying that it appears to be editing the last record?

0
 
c230kochiAuthor Commented:
Yeah, i'm not sure what I did.  I'm just going to take the old file and rebuild it, shouldn't take but a few mintues.


thx for all the help!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 12
  • 10
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now