Solved

Populating a string grid from a TQuery

Posted on 2000-03-23
23
575 Views
Last Modified: 2010-04-06
How would you populate a string grid with results from a TQuery that includes Column Headings?
0
Comment
Question by:evansj
  • 11
  • 4
  • 3
  • +3
23 Comments
 

Expert Comment

by:Carpathia
Comment Utility
First of all, you can get the column headings by..

MyGrid.ColCount := MyQuery.Fields.Count;
for index := 0 to MyQuery.Fields.Count - 1 do
  MyGrid.Cells[index, 0] := MyQuery.Fields[index].Name;
end;

then, fill the data, making sure the grid is big enough..

MyGrid.RowCount := MyQuery.RecCount + 1;
while (not MyQuery.eof) do
begin
  for index2 := 0 to MyQuery.Fields.Count - 1 do
  begin
    MyGrid.Cells[index2, index] := Fields[index2].AsString;
  end;
end;


This code works for SQL Server 7. If youre using BDE or another database, you will need to examine the Field.Type and get it into a string manually, if the field is a float or integer, or date.

Hope this helps

Carpathia
0
 
LVL 2

Expert Comment

by:aubs
Comment Utility
Hi evansj,

Just a comment...

Why do you want to. Can you not use a DBGrid?

I inherited a database program a few years ago that used several string grids instead of DBGrids. It was a nightmare to maintain because of all the unnecessary coding all over the place.

I have since replaced the SGs with DBGrids, and everything is much neater now.

Aubs
0
 

Author Comment

by:evansj
Comment Utility
I normally use dbgrids, but I have an annoying problem for the BDE, and I need a quick and dirty way to put it to bed.
0
 

Author Comment

by:evansj
Comment Utility
Code snippet does not work for D3. Property names for string grid are wrong, like RecCount brings error. Changes to the code snippet brought access violations.
0
 

Expert Comment

by:netbeto
Comment Utility
Hy evansy,
Try that..

procedure TForm1.BitBtn1Click(Sender: TObject);
var index, index2: integer;
begin
 StringGrid1.ColWidths[0] := 10;
 StringGrid1.RowHeights[0] := 16;
 StringGrid1.ColCount := Query1.Fields.Count + 1;
 for index := 1 to Query1.Fields.Count do
  begin
   StringGrid1.Cells[index, 0] := Query1.Fields[index -1].FieldName;
  end;
StringGrid1.RowCount := Query1.RecordCount + 1;
Query1.First;
index := 1;
while (not Query1.eof) do
begin
  for index2 := 1 to Query1.Fields.Count do
  begin
    StringGrid1.Cells[index2,index] := Query1.Fields[index2 -1].AsString;
    StringGrid1.RowHeights[index] := 16;
  end;
  Inc(index);
  Query1.Next;
end;
end;

This works fine, but may you have to make some little changes to get a more cool StringGrid.

Hope to Help you...

Cheers..

NetBeto
0
 

Author Comment

by:evansj
Comment Utility
Had to change Fields.Count to FieldCount
Still does not work. Gets list index out of bound error.
0
 
LVL 4

Expert Comment

by:jeurk
Comment Utility
Hi,
It has not much to do with the question I think netbeto has answered the question...

But If you also want to change some cosmetic things in your display you might want :

{
Name        : TFRM_VRF_reader.grd_voucher_detailDrawCell
Description : owner draw method to draw the grid with various cosmetic effects
              that proc is used by all the grids on the screen
Input       : Sender: TObject
              Col: Integer
              Row: Integer
              Rect: TRect
              State: TGridDrawState
Author      : Jkn
Date        : 24/03/2000
Revision history:
! Date       ! Sign   ! Description                                     !
! 24/03/2000 ! Jkn    ! Creation                                        !
}

procedure TFRM_VRF_reader.grd_voucher_detailDrawCell(Sender: TObject; Col,
  Row: Integer; Rect: TRect; State: TGridDrawState);
var
  s: string;
  theGrid: TStringGrid;
begin
  theGrid := (Sender as TStringGrid);

  //get the text to draw
  S := theGrid.Cells[Col, Row];

  //draw the column headers in bold
  if gdFixed in state then
  begin
    theGrid.Canvas.Font.Style := [fsBold];
  end
  else
  begin
    theGrid.Canvas.Font.Style := theGrid.Canvas.Font.Style - [fsBold];
    if Sender = grd_voucher_detail_sum then
      theGrid.Canvas.Font.Color := clWindowText;
  end;

  //highlight the focused lines
  if ((gdFocused in State) or (theGrid.selection.Top = Row)) and not (Sender = grd_voucher_detail_sum) then
  begin
    theGrid.Canvas.Brush.Color := clHighlight;
    theGrid.Canvas.FillRect(Rect);
  end
  else
  begin
    theGrid.Canvas.Brush.Color := clBtnFace;
    theGrid.Canvas.FillRect(Rect);
  end;

  // make the output rectangle a bit smaller to have a small distance from the borders
  InflateRect(Rect, -2, -2);

  //draw the text right or left aligned
  if (Col > 0) and not (gdFixed in state) then
    DrawText(theGrid.Canvas.Handle, PChar(S), Length(S), Rect, DT_RIGHT)
  else
    DrawText(theGrid.Canvas.Handle, PChar(S), Length(S), Rect, DT_LEFT);
end;

resize a grid is like that :
{
Name        : ResizeStringGridColumnsWithFirstFixed
Description : Resize the columns width of the given grid
              the first columns has a fixed size
Input       : aStringGrid: TStringGrid
              fixedColWitdh: integer = the size in pixel of the first column
Author      : Jkn
Date        : 24/03/2000
Revision history:
! Date       ! Sign   ! Description                                     !
! 24/03/2000 ! Jkn    ! Creation                                        !
}

procedure ResizeStringGridColumnsWithFirstFixed(aStringGrid: TStringGrid; fixedColWitdh: integer);
var
  scrollbarWidth: integer; //the scrollbar width
  i: integer; //counter
  colWidth: Integer; //the size of the last fields
  lastingWidth: integer; //the number of pixels we can share between the other fields
begin
  if not (aStringGrid.ColCount > 0) then
    exit;

  aStringGrid.ColWidths[0] := fixedColWitdh;

  scrollbarWidth := GetSystemMetrics(SM_CXVSCROLL);
  //the pixels in a grid are spreaded like follows :
  //------------------clientwidth---------------------------//
  //|---fixed col-----| ||| every lines width | [] the scrollbar width
  lastingWidth := aStringGrid.ClientWidth - fixedColWitdh - (aStringGrid.ColCount * aStringGrid.GridLineWidth) - scrollbarWidth;
  colWidth := lastingWidth div (aStringGrid.ColCount - 1);

  for i := 1 to aStringGrid.ColCount - 1 do
  begin
    aStringGrid.ColWidths[i] := colWidth;
  end;

end;

This is netbeto is calling a more cool StringGrid...
CU
0
 

Author Comment

by:evansj
Comment Utility
Had to change Fields.Count to FieldCount
Still does not work. Gets list index out of bound error.
0
 
LVL 4

Expert Comment

by:jeurk
Comment Utility
I tested it, with fieldcount, this is working perfectly. The problem must be elsewhere...
0
 

Author Comment

by:evansj
Comment Utility
I'm using D3, it doesn't like the fieldcount.
0
 
LVL 4

Expert Comment

by:jeurk
Comment Utility
I tested it under D3.01
Maybe you can give me your project
or a part of it so that we could check
the code ??
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:evansj
Comment Utility
I just run a query before this code and added the code snippet behind and get the following error when compiling or syntax check:

'[' Expected but '.' found  

Here is the routine (I've changed the name of tables and fields as to not divulge sensitive info):

procedure TForm1.Button4Click(Sender: TObject);
var my_seq,index, index2: integer;
begin
query1.Close;
query1.SQL.Clear;
query1.SQL.Add('alter session set nls_date_format = ''DD-MON-YYYY''');
query1.ExecSQL;

query1.Close;
query1.SQL.Clear;
query1.SQL.Add('select rpt_seq.nextval DA_SEQ from dual');
query1.Open;
my_seq:=query1.FieldByName('DA_SEQ').AsInteger;

query1.Close;
query1.SQL.Clear;
query1.SQL.Add('insert into tracked');
query1.SQL.Add('select :SEQ,suxxxx.cust_name,');
query1.ParamByName('SEQ').AsInteger :=my_seq;
query1.SQL.Add('axxxxxxx.atttt_login,fxxxxx.axxx_id,fxxxxx.asxxxx_login,');
query1.SQL.Add('fxxxxx.farxxxx,fxxxxx.axxxxdate,fxxxxx.item_serno,');
query1.SQL.Add('fxxxxx.exxxxx_name,fxxxxx.cust_compl,clxxxx_table.asxx,');
query1.SQL.Add('clxxxx_table.name,clxxxx_table.compl_date,');
query1.SQL.Add('anxxxxx_inv_memo.loadseq,clxxxx_act_memo.loadseq clxxxx_seq,');
query1.SQL.Add('round(nvl(clxxxx_table.compl_date,sysdate)-fxxxxx.farxxxx)');
query1.SQL.Add('from fxxx_xxx.fxxxxx,fxxx_xxx.suxxxx,fxxx_xxx.axxxxxxx,');
query1.SQL.Add('fxxx_xxx.anxxxxx_table,fxxx_xxx.clxxxx_table,');
query1.SQL.Add('fxxx_xxx.anxxxxx_inv_memo,fxxx_xxx.clxxxx_act_memo');
query1.SQL.Add('where fxxxxx.axxx_id = anxxxxx_table.axxx_id');
query1.SQL.Add('and fxxxxx.cxxx_no = suxxxx.cxxx_no');
query1.SQL.Add('and fxxxxx.assy_id = axxxxxxx.assy_id');
query1.SQL.Add('and anxxxxx_table.axxx_id = axxxxxxx.axxx_id');
query1.SQL.Add('and anxxxxx_table.clxxxx_actid = clxxxx_table.clxxxx_actid');
query1.SQL.Add('and anxxxxx_table.loadseq = anxxxxx_inv_memo.loadseq');
query1.SQL.Add('and clxxxx_table.loadseq = clxxxx_act_memo.loadseq');
query1.SQL.Add('and fxxxxx.rec_method not like ''X%''');
query1.SQL.Add('and fxxxxx.farxxxx >= ' + '''' + ConvertDate(start.Date)  + '''');
query1.SQL.Add('and fxxxxx.farxxxx <= ' + '''' + ConvertDate(ended.Date)  + '''');
query1.SQL.Add('and fxxxxx.exxxxx_name like ''%QXXX%''');
query1.SQL.Add('and ((clxxxx_table.asxx is not null');
query1.SQL.Add('and clxxxx_table.compl_date is null) or (fxxxxx.axxxxdate is null))');
query1.ExecSQL;

query1.Close;
query1.SQL.Clear;
query1.SQL.Add('update tracked');
query1.SQL.Add('set name = (select asxxxx_login from fxxx_xxx.fxxxxx where tracked.axxx_id = fxxxxx.axxx_id)');
query1.SQL.Add('where QXXX_no = :SEQ');
query1.ParamByName('SEQ').AsInteger :=my_seq;
query1.SQL.Add('and name = ''UNKNOWN''');
query1.SQL.Add('and axxxxdate is null');
query1.ExecSQL;

query1.Close;
query1.SQL.Clear;
query1.SQL.Add('update tracked');
query1.SQL.Add('set name = (select atttt_login from fxxx_xxx.axxxxxxx where tracked.axxx_id = axxxxxxx.axxx_id)');
query1.SQL.Add('where QXXX_no = :SEQ');
query1.ParamByName('SEQ').AsInteger :=my_seq;
query1.SQL.Add('and name = ''UNKNOWN''');
query1.SQL.Add('and axxxxdate is not null');
query1.ExecSQL;


query1.Close;
query1.SQL.Clear;
query1.SQL.Add('select rtrim(tracked.cust_name)||'' (''||rtrim(tracked.exxxxx_name)||'')'' customer,');
query1.SQL.Add('tracked.item_serno,tracked.cust_compl,');
query1.SQL.Add('to_char(tracked.farxxxx,''MM/DD'') farxxxx,');
query1.SQL.Add('to_char(tracked.axxxxdate,''MM/DD'') axxxxdate,');
query1.SQL.Add('to_char(tracked.asxx,''MM/DD'') asxx,substr(name,1,15) name,');
query1.SQL.Add('to_char(tracked.compl_date,''MM/DD'') compl_date,');
query1.SQL.Add('tracked.aging,anxxxxx_inv_memo.inv_memo fir,clxxxx_act_memo.act_memo action_memo');
query1.SQL.Add('from tracked,fxxx_xxx.anxxxxx_inv_memo,fxxx_xxx.clxxxx_act_memo');
query1.SQL.Add('where tracked.atttt_seq = anxxxxx_inv_memo.loadseq');
query1.SQL.Add('and tracked.clxxxx_seq = clxxxx_act_memo.loadseq');
query1.SQL.Add('and farxxxx >= ' + '''' + ConvertDate(start.Date)  + '''');
query1.SQL.Add('and farxxxx <= ' + '''' + ConvertDate(ended.Date)  + '''');
query1.SQL.Add('and QXXX_no = :SEQ');
query1.ParamByName('SEQ').AsInteger :=my_seq;
query1.SQL.Add('order by customer,aging desc');
query1.Open;


 StringGrid1.ColWidths[0] := 10;
 StringGrid1.RowHeights[0] := 16;
 StringGrid1.ColCount := Query1.Fields.Count + 1;
 for index := 1 to Query1.Fields.Count do
  begin
   StringGrid1.Cells[index, 0] := Query1.Fields[index -1].FieldName;
  end;
StringGrid1.RowCount := Query1.RecordCount + 1;
Query1.First;
index := 1;
while (not Query1.eof) do
begin
  for index2 := 1 to Query1.Fields.Count do
  begin
    StringGrid1.Cells[index2,index] := Query1.Fields[index2 -1].AsString;
    StringGrid1.RowHeights[index] := 16;
  end;
  Inc(index);
  Query1.Next;
end;

query2.Close;
query2.SQL.Clear;
query2.SQL.Add('delete from tracked');
query2.SQL.Add('where QXXX_no = :SEQ');
query2.ParamByName('SEQ').AsInteger :=my_seq;
query2.ExecSQL;
end;


0
 

Author Comment

by:evansj
Comment Utility
The above burps on the following line:

 StringGrid1.ColCount := Query1.Fields.Count + 1;
 
0
 
LVL 4

Expert Comment

by:jeurk
Comment Utility
is it not at for index := 1 to Query1.Fields.Count do ?
you should go from for index := 0 to Query1.Fields.Count - 1 do
Maybe you want to start from second field, but they are only count - 1 fields in the query...
and are you sure of the following : Grids must include at least one scrolling column. Do not set FixedCols to a value greater than ColCount - 1

LEt me know...
0
 

Author Comment

by:evansj
Comment Utility
It's not getting that far. It is getting the error on this line:

 StringGrid1.ColCount := Query1.Fields.Count + 1;
 
0
 

Expert Comment

by:netbeto
Comment Utility
I don't know why you are geting troubles, I've done the same and it's worked fine. But try change "Query1.Fields.Count" for "Query1.FieldCount".

Hope to help...

NetBeto.
0
 
LVL 4

Accepted Solution

by:
SurferJoe earned 150 total points
Comment Utility
procedure TForm1.Button1Click(Sender: TObject);
var
 i, myRow, myCol: Integer;

begin
 with Query1, SQL, StringGrid1 do begin
   // Give me Data ----------------------
   Close;
   Clear;
   Add('Select * FROM customer.db');
   Open;

   // figure the layout ----------------------
   ColCount := FieldCount -1;
   First;
   myRow := 1;
   repeat // This is faster than Record count on SQL or large data sets
     Inc(myRow);
     Next;

   until (Eof);

   // Set up the grid ---------------------------
   ColCount := FieldCount -1;
   RowCount := myRow;
   
   for i := 0 to FieldCount -1 do
     Cells[i, 0] := FieldDefs[i].Name;

   // Shovel that data in there bub --------------------------
   First;
   myRow := 1;
   repeat // This is faster than Record count on SQL or large data sets
     for i := 0 to FieldCount -1 do
       Cells[i, myRow] := Fields[i].Text;

     Inc(myRow);
     Next;

   until (Eof);


 end;


end;
0
 
LVL 4

Expert Comment

by:SurferJoe
Comment Utility
The presiding example is great way to display data you don't want people tampering with.
Many times I'll even do math on the results to display dynamic yet static info for the knuckleheads that use the programs we build.

This was built in Delphi 5.0 using the Dbdemos databases that shipped with the product. Don't know of any reason the code would not also run in D3, if you would like the entire project just let me know where to send it.
0
 

Author Comment

by:evansj
Comment Utility
Sorry It's taking so long to evaluate, I've got several projects, and I am immersed in them at the moment. I will try to get to this by weeks end.
0
 
LVL 4

Expert Comment

by:SurferJoe
Comment Utility
Welcome to the club, standing by.
0
 

Expert Comment

by:netbeto
Comment Utility
Hey,don't be ingrate!!! you had lots of solutions!!!
0
 

Author Comment

by:evansj
Comment Utility
Adjusted points from 100 to 150
0
 

Author Comment

by:evansj
Comment Utility
TADA!!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now