Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Populating a string grid from a TQuery

Posted on 2000-03-23
23
Medium Priority
?
596 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 4
  • 3
  • +3
23 Comments
 

Expert Comment

by:Carpathia
ID: 2651381
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
ID: 2652427
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
ID: 2653261
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:evansj
ID: 2653643
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
ID: 2654523
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
ID: 2654856
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
ID: 2654884
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
ID: 2654987
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
ID: 2659979
I tested it, with fieldcount, this is working perfectly. The problem must be elsewhere...
0
 

Author Comment

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

Expert Comment

by:jeurk
ID: 2679305
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
 

Author Comment

by:evansj
ID: 2685556
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
ID: 2685564
The above burps on the following line:

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

Expert Comment

by:jeurk
ID: 2686733
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
ID: 2687055
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
ID: 2687819
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:
Greg Rowland earned 600 total points
ID: 2729060
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:Greg Rowland
ID: 2729075
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
ID: 2747803
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:Greg Rowland
ID: 2747851
Welcome to the club, standing by.
0
 

Expert Comment

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

Author Comment

by:evansj
ID: 2757066
Adjusted points from 100 to 150
0
 

Author Comment

by:evansj
ID: 2757067
TADA!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

704 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