Delphi - reading the records of a database into a string grid

Question

So how do you write the records of a database (from a TADOTable component) into a String grid? (the record's fields are all strings)

I tried something like this but to no avail:

procedure TfrmPuntehou.WriteToList(tbl: TADOTable;grid:TStringGrid);
var
  iNewRowCount:integer;
  i,j,m: Integer;
  const
  separator = ',';
begin
  tempList:= TStringList.Create;
  try
    tbl.First;
    while not (tbl.Eof) do
    begin
      tempList.Add(tbl['Car Number']+separator+tbl['Racer Name']+separator+tbl['Licence']);
      tbl.Next;
    end;
        for j:= 1 to (tempList.Count - 1) do
      begin
      grid.Rows[j].Text := tempList.Strings[(J-1)] ;
      end;
  finally
   tempList.Free;
  end;

      //fill the row numbers
      for m := 1 to grid.rowcount do
      begin
      grid.Cells[0,m]:= IntToStr(m);
      end;
end;

Example of the output I'm trying to get on startup: (Row number column is not part of the db)

Thanks in advance for the help!
Kind Regards
PrimeBeat

You should use a TADOQuery instead of a TADOTable. You don't need at all to use an intermediate TStringList. You can write directly to the grid cells. Use two embedded for-loop: one on the record and one on the columns. You can set the stringgrid ColCount to the number of fields upfront but should expand RowCount as the loop thru the records progress (To avoid querying the record count first). — fpiette, Jan 21 '21 at 14:34
@TomBrunberg Because I have coded a lot of other functions into the grid and I only have to do this thing, then I'm done with my program. So I doesn't work for me to start over... — PrimeBeat, Jan 21 '21 at 15:04
@fpiette How would one implement an ADOQuery like that with the for-loops, etc? Could I ask for a code example of how to do it? — PrimeBeat, Jan 21 '21 at 15:07
"with the for-loops, etc?" Easy - don't use for loops for retrieving data from a dataset (TAdoTable, TAdoQuery, etc), because you shouldn't assume you know how many dataset rows there are (especially in a multi-user dataset). Use a `while` loop, like your own code ... — MartynA, Jan 21 '21 at 16:21
@MartynA Yes of course, I meant while loop for the records and for-loop for the fields. — fpiette, Jan 21 '21 at 16:29

Ken White · Accepted Answer · 2021-01-21T23:26:46.540

You're going through far too much work. You don't need the separate stringlist at all, and your code could be much simpler.

var
  i, Row: Integer;
begin
  // Populate header row
  Grid.Cells[0, 0] := 'Row';
  Row := 0;

  for i := 0 to Tbl.FieldCount - 1 do
    Grid.Cells[i + 1, Row] := Tbl.Fields[i].FieldName; // The +1 skips the Row column
  Inc(Row);
  // Populate cells
  Tbl.First;
  while not Tbl.Eof do
  begin
    for i := 0 to Tbl.FieldCount - 1 do
    begin
      Grid.Cells[i, Row] := IntToStr(i);                // Populate Row number
      Grid.Cells[i + 1, Row] := Tbl.Fields[i].AsString; // Fill rest of row with table data
    end;
    Inc(Row);
    Tbl.Next;
  end;
end;

Your for-loop on fields is incorrect. Populating row number is incorrect, incrementing row inside the for-loop is incorrect. — fpiette, Jan 21 '21 at 19:59

score 1 · Answer 2 · answered Jan 21 '21 at 18:22

Here is an example using TADOQuery and a StringGrid:

procedure TForm1.Button1Click(Sender: TObject);
var
    I    : Integer;
    ARow : Integer;
begin
    ADOConnection1.Open('user', 'pass');
    ADOQuery1.SQL.Text := 'SELECT * FROM dbo.Person';
    ADOQuery1.Open;
    if ADOQuery1.Eof then begin
        ShowMessage('Data not found');
        Exit;
    end;
    SGrid.RowCount := 1;
    SGrid.ColCount := ADOQuery1.Fields.Count + 1;
    // Create titles of row 0
    for I := 0 to ADOQuery1.Fields.Count - 1 do
        SGrid.Cells[I + 1, 0] := ADOQuery1.Fields[I].DisplayName;
    // Populate the cells with data from result set
    ARow := 1;
    while not ADOQuery1.Eof do begin
        Inc(ARow);
        SGrid.RowCount := ARow + 1;
        SGrid.Cells[0, ARow] := ARow.ToString;
        for I := 0 to ADOQuery1.Fields.Count - 1 do
            SGrid.Cells[I + 1, ARow] := ADOQuery1.Fields[I].AsString;
        ADOQuery1.Next;
    end;
end;

score 0 · Answer 3 · answered Jan 22 '21 at 05:58

Thanks to Ken White's answer, I managed to solve the problem!

procedure TfrmPuntehou.WriteToList(tbl: TADOTable;grid:TStringGrid);
var
  Row: Integer;
begin

  tbl.Active:=True;

  Row := 1;

  // Populate cells
  Tbl.First;
  while not Tbl.Eof do
  begin
    grid.Cells[0,Row]:= IntToStr(Row);
    grid.Cells[1,Row]:= tbl.fields[0].AsString;
    grid.Cells[2,Row]:= tbl.fields[1].AsString;
    grid.Cells[3,Row]:= tbl.fields[2].AsString;
    Inc(Row);
    IncreaseRowCount(grid);
    Tbl.Next;
  end;
  tbl.Active:=false;
end;

Delphi - reading the records of a database into a string grid

3 Answers3