Add the field to the dataset
lazarus / 16-Databases

----------------------------------------------------------------------
xinyiman · 2014-01-31 11:13

Hello guys I have the following problem.
Suppose I have the following query

select field1, field2 from table;

and for my own personal reasons I need to have in DBGrid also field3
(which does not exist in the table), but I need to do my calculations.

How can I do at run time?! And possibly after I've already opened the
query?!

I'm creating a special unit at the moment is this:

Code: [Select]unit Unit_CampoComboDBGrid;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, Grids;


function AddDBComboBoxToGrid(Field:TField;NomeNuovoCampo:
string;DataSet: TDataSet; CampoDataSet: string ;DataSetList: TDataSet;
ChiaveLista: string; CampoLista: string): boolean;

implementation

              function
AddDBComboBoxToGrid(Field:TField;NomeNuovoCampo: string;DataSet:
TDataSet; CampoDataSet: string ;DataSetList: TDataSet; ChiaveLista:
string; CampoLista: string): boolean;
              var
                i: integer;
                ret: boolean;
              begin
                     ret:=false;
                     try
                        try
                           DataSet.Active:=False;
                           for i:=0 to DataSet.FieldDefs.Count-1 do
                           
 Field:=DataSet.FieldDefs[i].CreateField(DataSet);

                           Field:=TStringField.Create(DataSet);
                           with Field do
                           begin
                             FieldName:=NomeNuovoCampo;
                             Dataset:=DataSet;
                             FieldKind := fkLookup; //QUI DICO CHE E'
UNA COMBOBOX
                             LookupDataSet:=DataSetList; //QUI ASSEGNO
IL DATASET DA CUI EREDITARE I DATI DA FAR VEDERE NELLA COMBOBOX
                             LookupCache:=FALSE;
                             LookupKeyFields:=ChiaveLista;//'CHIAVE';
//CHIAVE CHE VERRA' INSERITA NEL CAMPO Temf.KeyFields
                             LookupResultField:=CampoLista;//'NOME';
//VALORE CHE VEDI NELLA COMBOBOX DA SELEZIONE
                             ReadOnly:=FALSE; //SOLA LETTURA
                             ProviderFlags:=[pfInUpdate, pfInWhere];
                             Required:=FALSE;
                             KeyFields:=CampoDataSet;//'CHIAVEPV';
                           end;
                           DataSet.Active:=True;
                           ret:=TRUE;
                        finally
                       end;
                     except
                           on E: Exception do
                           begin

                           end;
                     end;
                     result:=ret;
              end;
end.
At the moment it does not work, but if I put this code in the form
after I open the query and then not going for a function then
magically it all works. Probably something to revise the parameters of
the function.
Where am I wrong?

I want to understand how to solve through my unit in order to reuse
the code.

----------------------------------------------------------------------
mangakissa · 2014-01-31 12:34

- go to your T()Query component.
- Open the fieldeditor with right mouse click -> edit fields
- select  "add fields" from fielddefs
- select all your fields and go back to the fieldeditor
- create "new field".
- select calculated as fieldtype and fill in the required fields
- close form "edit fields"

with the property OnCalcfields you can fill your extra field.

----------------------------------------------------------------------
exdatis · 2014-01-31 13:01

No, the question is: "How can I do at run time?!". But I agree, 
mangakissa,it's ok to create field before, at design time and use it
in run time.

----------------------------------------------------------------------
mangakissa · 2014-01-31 19:04

found this option with google:
Code: [Select]Procedure AddCalcStringField(Table1: tDataset;
fName,fDisplay:String;
Size:
Integer);
{ Adds a calculated tStringField to a table at runtime}
var
f : TField;
i : integer;
begin
Table1.FieldDefs.Update;
Table1.Close;
for i := 0 to Table1.FieldDefs.Count - 1 do
if table1.FindField(table1.FieldDefs[i].Name) = nil then
table1.FieldDefs.Items[i].CreateField(Table1);

if table1.FindField(fName) <> nil then
Exit;

f := tStringField.Create(Table1);
f.Name := Table1.Name+fName;
f.FieldName := fName;
f.DisplayLabel := fDisplay;
f.Calculated := True;
f.DataSet := Table1;
Table1.Open;
end;

----------------------------------------------------------------------
xinyiman · 2014-02-01 14:21

I tried the code and it works, but I need it to be a combobox. Then I
tried to modify the code so? But the following line

FieldKind := fkLookup; // <--ERROR

sends the loop my program. Why?

Code: [Select]Procedure AddCalcStringField(fName:
string;fDisplay:String;Size:Integer;Table1: tDataset; CampoDataSet:
string; DataSetList: TDataSet; ChiaveLista: string; CampoLista:
string);
{ Adds a calculated tStringField to a table at runtime}
var
f : TField;
i : integer;
begin
Table1.FieldDefs.Update;
Table1.Close;
for i := 0 to Table1.FieldDefs.Count - 1 do
if table1.FindField(table1.FieldDefs[i].Name) = nil then
table1.FieldDefs.Items[i].CreateField(Table1);

if table1.FindField(fName) <> nil then
Exit;

f := tStringField.Create(Table1);
with f do
begin
  FieldName := Table1.Name+fName;
  Name := fName;
  DisplayLabel := fDisplay;
  Calculated := True;
  DataSet := Table1;

  FieldKind := fkLookup; // <--ERROR

  LookupDataSet:=DataSetList;
  LookupCache:=FALSE;
  LookupKeyFields:=ChiaveLista;
  LookupResultField:=CampoLista;

  ReadOnly:=FALSE;
  ProviderFlags:=[pfInUpdate, pfInWhere];
  Required:=FALSE;
  KeyFields:=CampoDataSet;

end;
Table1.Open;
end;

----------------------------------------------------------------------
mangakissa · 2014-02-01 21:05

fkLookup doesn't work on most  dataquery components.

----------------------------------------------------------------------
xinyiman · 2014-02-03 12:44

But this works.

I did a test example. Someone give me a hand to figure out how I can
remedy the problem?

You must have installed Zeos only as a package.
Unzip and compiled. Then he crushed button1 you will see that it
works, but if you press on button 2 you will see that nothing changes.

In addition, if you try a button then restart the program and try the
other because otherwise it goes in error.

thank you very much

----------------------------------------------------------------------
xinyiman · 2014-02-05 17:24

No idea? Am I wrong or is that a bug?