Accessing TFields at runtime

[Mario Blataric]

Hi!
I use TIBQuery to which I change SQL at runtime (couple of different SQLs).
What I would like to do is that newly created fields would have proper
DisplayLabel and DisplayFormat properties. I use couple of if commands to do
that, but those field never get displayed that way in dxDBGrid. The problem
is that I have to open IBQuery in order to access Fields, but when I open
it, all columns get created in dxDBGrid, and then, when I do change
DisplayLabel and DisplayFormat, there is no result in dxDBGrid. I would have
to set those values before IBQuery gets opened but I don't know how.

Anyone knows how to do this?

Thanks!

--

Mario Blataric
V&M electronic d.o.o.

[Craig Stuntz (TeamB)]

Mario Blataric wrote:
>
> The problem
> is that I have to open IBQuery in order to access Fields, but when I open
> it, all columns get created in dxDBGrid, and then, when I do change
> DisplayLabel and DisplayFormat, there is no result in dxDBGrid.

1. Disconnect the dxDBGrid from its datasource.
2. Open the IBQuery
3. Set DisplayLabel/Format
4. Reconnect the dxDBGrid to its datasource.

HTH,

-Craig

--
Craig Stuntz (TeamB) · Vertex Systems Corp. · Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
Delphi/InterBase WebLog: http://delphi.weblogs.com

[Mario Blataric]

> > The problem
> > is that I have to open IBQuery in order to access Fields, but when I
open
> > it, all columns get created in dxDBGrid, and then, when I do change
> > DisplayLabel and DisplayFormat, there is no result in dxDBGrid.
>
> 1. Disconnect the dxDBGrid from its datasource.
> 2. Open the IBQuery
> 3. Set DisplayLabel/Format
> 4. Reconnect the dxDBGrid to its datasource.

It does not work. I tried DBGrid also. Captions are names of fields, and no
number formatting is performed, although I set them before. Here is the
code:

dxDBGrid1.DefaultFields:=False;
dxDBGrid1.DataSource:=nil;
dxDBGrid1.DestroyColumns;
Reset_Trans(IBIzbor.Transaction); //Reset_Trans is my custom
procedure to Commit and Start transaction
case tip of
SQL_AFO: IBIzbor.SQL.Text:=Prikaz_po_AFO;
SQL_ARTIKL: IBIzbor.SQL.Text:=Prikaz_po_artiklu;
SQL_RAD_NALOG: IBIzbor.SQL.Text:=Prikaz_rad_naloga;
end;
IBIzbor.Active:=True; // This is the TIBQuery
for i:=0 to IBIzbor.Fields.Count - 1 do begin
if IBIzbor.Fields[i].FieldName='ID_RAD_NALOG' then
IBIzbor.Fields[i].DisplayLabel:='Br. rad. naloga';
if IBIzbor.Fields[i].FieldName='BARKOD' then
IBIzbor.Fields[i].DisplayLabel:='Barkod';
if IBIzbor.Fields[i].FieldName='ID_SKLAD' then
IBIzbor.Fields[i].DisplayLabel:='Sif. sklad.';
if IBIzbor.Fields[i].FieldName='ID_ARTIKL' then
IBIzbor.Fields[i].DisplayLabel:='Sif. artikla';
if IBIzbor.Fields[i].FieldName='NAZIV_ART' then
IBIzbor.Fields[i].DisplayLabel:='Naziv artikla';
if IBIzbor.Fields[i].FieldName='STANJE' then begin
IBIzbor.Fields[i].DisplayLabel:='Stanje na primci';
TFloatField(IBIzbor.Fields[i]).DisplayFormat:=',#0.0000';
end;
if IBIzbor.Fields[i].FieldName='ROK_TRAJANJA' then
IBIzbor.Fields[i].DisplayLabel:='Rok trajanja';
if IBIzbor.Fields[i].FieldName='POZICIJA' then
IBIzbor.Fields[i].DisplayLabel:='Pozicija u skl.';
if IBIzbor.Fields[i].FieldName='DATUM' then
IBIzbor.Fields[i].DisplayLabel:='Datum dok.';
if IBIzbor.Fields[i].FieldName='PORIJEKLO' then
IBIzbor.Fields[i].DisplayLabel:='Porijeklo';
if IBIzbor.Fields[i].FieldName='ID_RAD_NALOG' then
IBIzbor.Fields[i].Visible:=False;
if IBIzbor.Fields[i].FieldName='JCD' then
IBIzbor.Fields[i].Visible:=False;
end;
dxDBGrid1.DefaultFields:=True;
dxDBGrid1.DataSource:=DSIzbor;
dxDBGrid1.ApplyBestFit(nil);

When data is displayed, columns caption are 'ID_RAD_NALOG, BARKOD, ID_SKLAD
.....' insted 'Br. rad. naloga, Barkod, Sif. sklad .....'. Also the number
displayed in column 'STANJE' is '432.72364732634382', instead '432.7236'.
How to accomplish that grid displays correct captions and data formatting
specified in TFields of TIBQuery?

Thanks,

[Arno Brinkman [BISIT]]

Maybe another solution ?

Set SQL.Text with queries like this :

SELECT
ID_RAD_NALOG As "Br. rad. naloga",
BARKOD As "Barkod",
ID_SKLAD As "Sif. sklad",
ID_ARTIKL As "Sif. artikla",
NAZIV_ART As "Naziv artikla",
STANJE As "Stanje na primci",
ROK_TRAJANJA As "Rok trajanja"
FROM
TABLE

And to Display it on a special way you could use then OnDrawColumnCell and
use the Column.Field property.
See example below :

procedure TfrmX.DBGridDrawColumnCell(Sender: TObject; const Rect: TRect;
DataCol: Integer; Column: TColumn; State: TGridDrawState);
var
lHandled : Boolean;
lTextWidth : integer;
lText : String;
begin
lHandled := False;
if (CompareText(Column.FieldName,'Tarief') = 0) or
(CompareText(Column.FieldName,'LaagTarief') = 0) then
begin
if Column.Field.IsNull then
begin
with grdBasisTarieven.Canvas do
begin
Font.Color := clRed;
lText := '<geen>';
lTextWidth := TextWidth(lText);
case Column.Alignment of
taLeftJustify : TextRect(Rect,Rect.Left + 2,Rect.Top + 2,lText);
taCenter : TextRect(Rect,Trunc((Rect.Right + Rect.Left -
lTextWidth) / 2),Rect.Top + 2,lText);
taRightJustify : TextRect(Rect,Rect.Right - lTextWidth -
2,Rect.Top + 2,lText);
end;
end;
lHandled := True;
end;
end;
if not lHandled then
begin
DBGrid.DefaultDrawColumnCell(Rect,DataCol,Column,State);
end;
end;

Greetz,

Arno Brinkman.

[Jeff Overcash (TeamB)]

This works for me. I tried this with 3 grids - DBGrid and two dxDBGrids. The
first dxDBGrid had its DefaultFields set to true, the second (gdData) had it set
to false and the columns created manually (all other properties the default
settings). In all three cases the column was labeled correctly and the column
displayed data correctly formatted.

procedure TForm1.Button2Click(Sender: TObject);
begin
IBQuery1.Close;
IBQuery1.SQL.Text := 'select * from job';
IBQuery1.Open;
TNumericField(IBQuery1.FieldByName('min_salary')).DisplayFormat :=
'$0,000.00';
IBQuery1.FieldByName('min_salary').DisplayLabel := 'Minimum Salary';
gdData.DestroyColumns;
gdData.CreateDefaultColumns(gdData.DataSource.DataSet, gdData);
end;

Mario Blataric wrote:
>
> > > The problem
> > > is that I have to open IBQuery in order to access Fields, but when I
> open
> > > it, all columns get created in dxDBGrid, and then, when I do change
> > > DisplayLabel and DisplayFormat, there is no result in dxDBGrid.
> >

--
Jeff Overcash (TeamB) | Talk about failure
(Please do not email | To fall is not to fail
me directly unless | Failure isn't about falling down
asked. Thank You) | Failure is staying down (Marillion)

[Mario Blataric]

I have tried all the possible combinations with dxDBGrid and DBGrid,
DefaultFields, no DefaultFields, everything. No result. Grid always displays
captions and formatting as reflection of table fields, and no formatting. I
use IBX 4.63, Interbase 6.01, Delphi 5.01 Enterprise, Win2k. I am not sure
about dx Version (I guess it does not matter, since DBGrid also does not
work).
I tried disconnecting grid from datasource and then reconnect it after table
open, but fields are always default, and not as specified in DisplayLabel.

The idea of Arno Brikman is great to solve captions, but I am not sure I
want to use CustomDrawCell just to display formatted value. Also, something
is wrong with the first way, and I don't know what. It is very interesting
that the same code works to Jeff, and does not work to me.

Any ideas about this?

[Mario Blataric]

Hi again.
Problem solved.

I think the problem is in programmers which always looks the sollutions in
most complicated things. I started new application, tried the same thing and
it worked. Copy code into project I work in, and it does not work. Then I
went exploring through complete code and I found one interesting event. On
FormActivate there was Full_Refresh procedure which closes Query, and
reopens it back again. My code for setting proper DisplayLabel was OnShow.
It makes sense why it did not work. And I lost one day to solve this. Ahhh.

Thanks everyone.

[RESO / Claude GUTH]

I'm using the following :

with MyIBQuery do
begin
{load my SQL at runtime}
SetFieldsDef(MyIBQuery);
{set my formats, for instance}
TNumericField(FieldByName('ANumericField')).DisplayFormat := ',0.00';
Open;
end;

procedure SetFieldDefs(UnDataset : TDataset);
var i: integer; UnChamp : TField;
begin
UnDataset.Fields.Clear;
UnDataset.FieldDefs.Update;
with UnDataset.FieldDefs do for i := 0 to Count - 1 do
begin
UnChamp := Items[i].FieldClass.Create(UnDataset);
UnChamp.Size := Items[i].Size;
UnChamp.FieldName := Items[i].Name;
UnChamp.DataSet := UnDataset;
end;
end;

working fine with dxDBGrid.

Mario Blataric <mario.b...@vz.tel.hr> a écrit dans le message :
3bbb0023_1@dnews...

[Wayne Niddery [TeamB]]

"Mario Blataric" <mario.b...@vz.tel.hr> wrote in message
news:3bbc0d10_1@dnews...
> Problem solved.

Thanks for letting us know. Glad you were able to figure it out.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Some see private enterprise as a predatory target to be shot, others as a
cow to be milked, but few are those who see it as a sturdy horse pulling the
wagon." - Winston Churchill