Calculat the sum of a column in DBGrid

Hello!

I have a DBGrid with some numeric fields, I want to calculate the sum of
the column when any data change, the change will not write to database until a key pressed, so I can't use another query to find the sum, could anyone give me some hints?

Thank.
GundamGP01Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kretzschmarConnect With a Mentor Commented:
well ok,

here is it,
no more usage of the oldvalue-property

unit cach_upd_sum_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, DBTables, Db, Grids, DBGrids;

type
  TForm1 = class(TForm)
    Edit1: TEdit;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Table1: TTable;
    Query1: TQuery;
    Button1: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Table1BeforePost(DataSet: TDataSet);
    procedure Table1BeforeDelete(DataSet: TDataSet);
    procedure Button1Click(Sender: TObject);
    procedure Table1BeforeEdit(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

var
  sumval : Double;
  EdVal  : Double;

procedure TForm1.FormCreate(Sender: TObject);
//sql : select sum(wert) from Table
begin
  query1.open;
  sumval := query1.Fields[0].AsFloat;  //get initial sum with a query
  edit1.text := floattostr(sumval);
  query1.close;
end;

//Recalculate sum, you could also use the fieldbyname-method
procedure TForm1.Table1BeforePost(DataSet: TDataSet);
begin
  if (table1.State = dsedit) then
  begin
    sumval := sumval - EdVal;
    sumval := sumval + Table1.FieldByName('Wert').AsFloat;
  end;
  if table1.state = dsinsert then
    sumval := sumval + Table1.FieldByName('Wert').AsFloat;
  edit1.text := floattostr(sumval);
end;

//Recalculate sum
procedure TForm1.Table1BeforeDelete(DataSet: TDataSet);
begin
  sumval := sumval - Table1.FieldByName('Wert').AsFloat;
  edit1.text := floattostr(sumval);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  table1.ApplyUpdates;
  table1.CommitUpdates;
  formcreate(self);  //requery
end;

//remember currentvalue
procedure TForm1.Table1BeforeEdit(DataSet: TDataSet);
begin
  EdVal :=  Table1.FieldByName('Wert').AsFloat;
end;

end.

hope that works now better

meikl
0
 
kretzschmarCommented:
hi gundam,

why can't u use another-query?
do u use cachedupdates?

meikl
0
 
GundamGP01Author Commented:
Hi Meikl,

Yes, I use cachedupdates, so the data will not write to database until I applyupdate. At this time, the another-query doesn't got new data.

Gundam
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
kretzschmarCommented:
hi gundam,

a sample how could it be done

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Mask, DBCtrls, DBTables, Grids, DBGrids, Db;

type
  TForm1 = class(TForm)
    Table1: TTable;
    DataSource1: TDataSource;
    Query1: TQuery;
    DBGrid1: TDBGrid;
    DataSource2: TDataSource;
    Button1: TButton;
    Button2: TButton;
    Edit1: TEdit;
    procedure FormCreate(Sender: TObject);
    procedure Table1BeforePost(DataSet: TDataSet);
    procedure Table1BeforeDelete(DataSet: TDataSet);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

var sumval : double;  //holds the sum

procedure TForm1.FormCreate(Sender: TObject);
begin
  query1.open;
  sumval := query1.Fields[0].AsFloat;  //get initial sum with a query
  edit1.text := floattostr(sumval);
  query1.close;
end;

//Recalculate sum, you could also use the fieldbyname-method
procedure TForm1.Table1BeforePost(DataSet: TDataSet);
begin
  if (table1.State = dsedit) then
  begin
    sumval := sumval - Table1.Fields[0].OldValue;
    sumval := sumval + Table1.Fields[0].AsFloat;
  end;
  if table1.state = dsinsert then
    sumval := sumval + Table1.Fields[0].AsFloat;
  edit1.text := floattostr(sumval);
end;

//Recalculate sum
procedure TForm1.Table1BeforeDelete(DataSet: TDataSet);
begin
  sumval := sumval - Table1.Fields[0].AsFloat;
  edit1.text := floattostr(sumval);
end;

end.

hope this helps

meikl
0
 
fkmfkmCommented:
That is very long...

Any simpler answer.. ?
0
 
fkmfkmCommented:
That is very long...

Any simpler answer.. ?

Cause I also have many problems.on this too...
0
 
kretzschmarCommented:
well yes,

there is another solution also,
in the afterpost and afterdelete event of the dataset iterate through all records and add the field, where the sum is wanted.

but, depending on the amount of records of the dataset, this will cost some more perfomance.

let me know, if a sample is needed for this

meikl
0
 
kretzschmarCommented:
gundam, are you here?
0
 
GundamGP01Author Commented:
Yes, I have try your solution, but having some problem

1. The field was a calculated field, without OldValue

2. Even if it is a data field, it will get the wrong result if modify more than onec. for example:

before change:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 10.0, sumval = 10.0,

if I change fields[0] to 20, the data will be:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 20.0, sumval = 10.0,

the sumval after calculation should be:
sumval := 10.0 - 10.0; sumval := 0 + 20.0 = 20 <- this is correct

if I change fields[0] to 15, the data will be:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 15.0, sumval = 20.0,

after calculation:
sumval := 20.0 - 10.0; sumval := 10 + 15.0 = 25 <- this is wrong!

because I am using CachedUpdates, the OldValue will not update after Post. Until I commitupdate.

I've think about your second solution, because I calculate this field in the oncalcfield event, it will be trigger when iterate through the records, which make my program loop infinitely.

Gundam
0
 
kretzschmarCommented:
well,

must do a bit finetuning,
to avoid such effects you've
mentioned above.

i let you know when i'm ready

meikl
0
 
GundamGP01Author Commented:
Thank, I will test it.

Gundam
0
 
kretzschmarCommented:
hi gundam,
some results?
meikl
0
 
GundamGP01Author Commented:
Thank for your solution.

I have use your concept but make some modification.
Because the field is a calculated field, I record the old value,
before assign new value, so the variable are local.
Also, do the calculation in both BeforeDelete and BeforeCancel event.

Gundam
0
 
kretzschmarCommented:
hi gundam,

first thanks for accepting,
glad that you get it work
good luck again

meikl ;-)
0
All Courses

From novice to tech pro — start learning today.