GundamGP01
asked on
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.
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.
ASKER
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
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
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(Da taSet: 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
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
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(Da
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(
begin
sumval := sumval - Table1.Fields[0].AsFloat;
edit1.text := floattostr(sumval);
end;
end.
hope this helps
meikl
That is very long...
Any simpler answer.. ?
Any simpler answer.. ?
That is very long...
Any simpler answer.. ?
Cause I also have many problems.on this too...
Any simpler answer.. ?
Cause I also have many problems.on this too...
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
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
gundam, are you here?
ASKER
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
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
well,
must do a bit finetuning,
to avoid such effects you've
mentioned above.
i let you know when i'm ready
meikl
must do a bit finetuning,
to avoid such effects you've
mentioned above.
i let you know when i'm ready
meikl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank, I will test it.
Gundam
Gundam
hi gundam,
some results?
meikl
some results?
meikl
ASKER
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
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
hi gundam,
first thanks for accepting,
glad that you get it work
good luck again
meikl ;-)
first thanks for accepting,
glad that you get it work
good luck again
meikl ;-)
why can't u use another-query?
do u use cachedupdates?
meikl