Recent

Author Topic: [SOLVED] How to copy the cells of a TDBGrid into a TStringGrid?  (Read 5131 times)

Hartmut

  • Hero Member
  • *****
  • Posts: 742
My program reads some data from a SQLite-DB and stores them in a TDBGrid. For later processing I want to copy all cells of the TDBGrid into a TStringGrid. The following works, but is very slow, because it copies cell by cell:

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2.    var ax,ay, x,y: longint;
  3.    begin
  4.    DBConnection.DatabaseName:='f:\Firefox_Profile\places.sqlite'; {a Firefox-DB}
  5.    SQLQuery1.Close;
  6.    SQLQuery1.SQL.Text:= 'select * from moz_places'; {reads Firefox-History}
  7.    DBConnection.Connected:= True;
  8.    SQLTransaction1.Active:= True;
  9.    SQLQuery1.Open;
  10.  
  11.    SQLQuery1.Last; {fetches all records from the underlying DB}
  12.  
  13.    StringGrid1.FixedCols:=0;   {no fixed columns}
  14.  
  15.    ax:=DBGrid1.Columns.Count;  {number of columns}
  16.    ay:=DBGrid1.DataSource.dataset.RecordCount; {number of records}
  17.    StringGrid1.ColCount:=ax;   {number of columns}
  18.    StringGrid1.RowCount:=1+ay; {number of rows incl. titles}
  19.  
  20.    StringGrid1.BeginUpdate;
  21.                                {copy titles row: }
  22.    for x:=0 to ax-1 do  StringGrid1.Cells[x,0]:=DBGrid1.Columns[x].FieldName;
  23.  
  24.    for y:=1 to ay do           {copy all cells: }
  25.       begin
  26.       DBGrid1.DataSource.DataSet.RecNo:=y;
  27.       for x:=0 to ax-1 do
  28.          StringGrid1.Cells[x,y]:=
  29.             DBGrid1.DataSource.DataSet.Fields.Fields[x].AsString;
  30.       end;
  31.  
  32.    StringGrid1.EndUpdate(true);
  33.    end;            
           

The following should copy the whole matrix of cells at once, but does not work (it gives no error, but the destination cells are empty):

Code: [Select]
StringGrid1.Columns:=DBGrid1.Columns;
The following should copy all cells column by column, but also does not work (it gives no error, but the destination cells are empty too):

Code: [Select]
for x:=0 to ax-1 do  StringGrid1.Columns[x]:=DBGrid1.Columns[x];
Can somebody help please? How can I copy the cells at once or column by column? I'm a beginner to the LCL.
I attached my little project. I use Lazarus 1.6.2 with FPC 3.0.0 on Windows 7.
« Last Edit: July 11, 2017, 05:50:06 pm by Hartmut »

PatBayford

  • Full Member
  • ***
  • Posts: 125
Re: How to copy the cells of a TDBGrid into a TStringGrid?
« Reply #1 on: July 11, 2017, 01:28:07 am »
Quote from the Help file :-
TCustomGrid.Columns The properties of the columns in this grid

Description
The Columns property of a grid refers to the physical column in the table, and determines its size, shape, colour etc. It should not be confused with the COLUMNS construct frequently used in SQL programming, which refers to Fields of data. The contents of Fields can be displayed as Columns in a Grid, but they must be seen as separate and distinct. It is irrelevant to refer to the colour, font or width of a data field from a database, but these are all very relevant in specifying the appearance of a column in a grid.

In other words your routines are doing what you tell them, but the Columns collection carry no data!
Lazarus 1.8.0 FPC 3.0.2 SVN 56594 Windows 10 64bit (i386-win32-win32/win64)

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: How to copy the cells of a TDBGrid into a TStringGrid?
« Reply #2 on: July 11, 2017, 01:58:32 am »
Try this one.
Code: Pascal  [Select][+][-]
  1.  
  2. //it might require to add the unit DB in the uses clause.
  3.  
  4. procedure TForm1.Button1Click(Sender: TObject);
  5.    var
  6.      ax,ay, x,y: longint;
  7.      vDataset:TDataset;
  8.      vBkm    :TBookMark;
  9.    begin
  10.    DBConnection.DatabaseName:='f:\Firefox_Profile\places.sqlite'; {a Firefox-DB}
  11.    SQLQuery1.Close;
  12.    SQLQuery1.SQL.Text:= 'select * from moz_places'; {reads Firefox-History}
  13.    DBConnection.Connected:= True;
  14.    SQLTransaction1.Active:= True;
  15.    SQLQuery1.Open;
  16.  
  17.    SQLQuery1.Last; {fetches all records from the underlying DB}
  18.  
  19.    StringGrid1.FixedCols:=0;   {no fixed columns}
  20.    vDataset := DBGrid1.DataSource.Dataset;
  21.    ax:=DBGrid1.Columns.Count;  {number of columns}
  22.    ay:=DBGrid1.DataSource.dataset.RecordCount; {number of records}
  23.    StringGrid1.ColCount:=ax;   {number of columns}
  24.    StringGrid1.RowCount:=1+ay; {number of rows incl. titles}
  25.  
  26.    StringGrid1.BeginUpdate;
  27.    vBkm := vDataset.Bookmark;
  28.    vDataset.DisableControls;
  29.    try
  30.      vDataset.First;
  31.                                  {copy titles row: }
  32.      for x:=0 to ax-1 do  StringGrid1.Cells[x,0]:=DBGrid1.Columns[x].FieldName;
  33.      y:=0;
  34.      while not vDataset.EOF do;           {copy all cells: }
  35.       begin
  36.         for x:=0 to ax-1 do
  37.           StringGrid1.Cells[x,y]:= vDataset.Fields[x].AsString;
  38.         Inc(y);
  39.         vDataset.Next;
  40.       end;
  41.  
  42.    finally
  43.      StringGrid1.EndUpdate(true);
  44.      vDataset.GotoBookmark(vBkm);
  45.      vDataset.EnableControls;
  46.    end;
  47. end;
  48.  
There are 2 main differences in my approach
  • As you have discovered dbgrid is nothing more than a visual representation of what is going on with the underline dataset. IT has not data of its own nor it keeps a copy of them so use the sequential dataset.next instead of the more random RecNo.
  • Most of the times accessing data becomes slow because the dataset waits for the visual controls to repaint them self, so we stop updating them (vdataset.disablecontrols) until we finish (vdataset.enablecontrols).
  • And last but not least the use of bookmark to keep the last known position and return to it after our operation has finished.
« Last Edit: July 11, 2017, 02:04:10 am by taazz »
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Hartmut

  • Hero Member
  • *****
  • Posts: 742
Re: How to copy the cells of a TDBGrid into a TStringGrid?
« Reply #3 on: July 11, 2017, 05:49:42 pm »
Thanks a lot to PatBayford and taazz for your valuable help, the explanations and the improvement to my code, which runs up to 9 times (!) faster then mine. And now I understand why my "improvements" did not work.

PatBayford

  • Full Member
  • ***
  • Posts: 125
Re: [SOLVED] How to copy the cells of a TDBGrid into a TStringGrid?
« Reply #4 on: July 12, 2017, 02:27:21 am »
You are welcome - it is hard to see the wood for the trees sometimes :D
Lazarus 1.8.0 FPC 3.0.2 SVN 56594 Windows 10 64bit (i386-win32-win32/win64)

bytebites

  • Hero Member
  • *****
  • Posts: 633
Re: [SOLVED] How to copy the cells of a TDBGrid into a TStringGrid?
« Reply #5 on: July 12, 2017, 09:01:29 am »
Code: Pascal  [Select][+][-]
  1.  while not vDataset.EOF do;

Is the semicolon intentionally here?

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: [SOLVED] How to copy the cells of a TDBGrid into a TStringGrid?
« Reply #6 on: July 12, 2017, 09:15:13 am »
Code: Pascal  [Select][+][-]
  1.  while not vDataset.EOF do;

Is the semicolon intentionally here?
no it was inserted by the code tools and I missed it, it should not be there.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

 

TinyPortal © 2005-2018