Solved

Getting the FieldList from an SQL-Statement and add then to the Fields of a TADOQuery

Posted on 2007-03-22
5
480 Views
Last Modified: 2010-04-05
Hi all,

I have a TADOQuery-Component that can hold any SQL-Statement (Select-Query).
So, from my IDE, the FieldsEditor contains no fields.
When I fill the SQL-property with e.g. 'SELECT * FROM MyTable' and I open the Query, the results are showed in a DBGrid with the FieldNames displayed as ColumnHeaders.
So far so good...  BUT !!

Now I also want to include my own calculated field ('RowNumber') that holds the RecNo-Value of the current record.
So I have to add this field at runtime, just before I open mu Query.
This is my code to Add the Calulated Field to the DataSet:
procedure TfrmBaseQueryAnalyzerStandAlone.AddRowNumberField( DataSet: TDataSet; ColNr: Integer );
var
  fld: TIntegerField;
begin
  { First check if the Calculated Field already exists. If so, do nothing. If not, create it now. }
  if DataSet.FindField( 'CalcRowNumber' ) = nil then
  begin
    showmessage( inttostr( DataSet.FieldCount ) );
    DataSet.Close;
    fld :=  TIntegerField.Create( DataSet );
    with fld do
    fld.Name          := 'RowNumber';
    fld.FieldName     := 'RowNumber';
    fld.FieldKind     := fkCalculated;
    fld.Index         := ColNr-1;
    fld.DisplayLabel  := 'RijNr';
    fld.DataSet       := DataSet;
    DataSet.FieldDefs.Add( fld.Name, ftInteger, 0, False );
    showmessage( inttostr( DataSet.FieldCount ) );
    DataSet.Open;
  end;
end;

The code works fine, but the result is that my Dataset ONLY contains this calculated field.
So, I guess I should add all the fields which are in my SELECT-Clause of my query also to my DataSet by code.
Is there anyone who knows how to do this ?
Or maybe another way to accomplish what I want ?

Thanks in advance,

Wim.
0
Comment
Question by:wimmeyvaert
  • 3
  • 2
5 Comments
 
LVL 26

Accepted Solution

by:
EddieShipman earned 125 total points
ID: 18771409
try doing it this way:

var
  Field: TField;
  fld: TIntegerField;
  i: Integer;
begin
  if DataSet.FindField( 'CalcRowNumber' ) = nil then
  begin
    DataSet.Close;
    Dataset.FieldDefs.Update;
    for I := 0 to Dataset.FieldDefs.Count - 1 do
    begin
      Field := Dataset.FieldDefs[I].CreateField(Dataset);
    end;
    fld :=  TIntegerField.Create( Dataset );
    fld.Name          := 'RowNumber';
    fld.FieldName     := 'RowNumber';
    fld.Calculated    := True;
    fld.DataSet       := DataSet;
    fld.Index         := ColNr-1;
    fld.DisplayLabel  := 'RijNr';
    DataSet.Open;
  end;
end;
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 18777938
Hi EddieShipman,

I will try out your suggestion and let you know the results.

Best regards,

Wim.
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 18778620
Hi again,

I tested out your suggestion and it seems to work ....

BUT  !!!!

When I scroll in the DBGrid, I see that the first and the last item of the grid is havinf value '-1' as a RowNumber. When I export the dataset to a csv-file(with a self-written procedure) I also see in the csv-file the -1-value.

Any idea what's causing this behavior ?
I should get this solved too before I can put this application live.

Thanks in advance.

Wim.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18779516
Nope, I don't have the code that produces the "recno" for that column, do you?
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 18806143
Hi EddieShipman,

I found a workaround for the -1 values in the first and last record of my dataset.
I put following code in my OnCalcFields-Event of my DataSet :

  if DataSet.FindField( 'RowNumber' ) <> nil then
  begin
    DataSet.FieldByName('RowNumber').AsInteger := DataSet.RecNo;
    if DataSet.FieldByName('RowNumber').AsInteger = -1 then
    begin
      if DataSet.Bof then
        DataSet.FieldByName('RowNumber').AsInteger := 1
      else if DataSet.Eof then
        DataSet.FieldByName('RowNumber').AsInteger := DataSet.Recordcount
    end;
  end;


This seems to work perfectly now.
I will grant you the points anyway for helping me out with the tricky part.
But I just wanted to post my code too, to offer a complete working solution.
Thanks again for your help.

Best regards,

Wim.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question