Recent

Author Topic: [Solved] TDBGrid: how to see an additional column with the row number?  (Read 2753 times)

Hartmut

  • Sr. Member
  • ****
  • Posts: 280
My program reads some data from a SQLite-DB and shows them via TDBGrid. I want to see an additional first column with the row numbers and don't get it to work. In a TStringGrid this would be easy:

Code: [Select]
Stringgrid1.Options:=Stringgrid1.Options + [goFixedRowNumbering];
But when you try this with a TDBGrid you get a compiler error ("incompatible types").

Here is my program:

Code: Pascal  [Select]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2.    begin
  3.    DBConnection.DatabaseName:='f:\Firefox_Profile\places.sqlite'; {a Firefox-DB}
  4.    SQLQuery1.Close;
  5.    SQLQuery1.SQL.Text:= 'select * from moz_places'; {read Firefox-History}
  6.    DBConnection.Connected:= True;
  7.    SQLTransaction1.Active:= True;
  8.    SQLQuery1.Open;                
  9. // DBGrid1.Options:=DBGrid1.Options + [goFixedRowNumbering]; // gets a compiler error ("incompatible types")
  10.    end;  


Can somebody help please? I attached my little project. I use Lazarus 1.6.2 with FPC 3.0.0 on Windows 7.
« Last Edit: July 21, 2017, 06:55:04 pm by Hartmut »

mangakissa

  • Hero Member
  • *****
  • Posts: 944
Re: TDBGrid: how to see an additional column with the row number?
« Reply #1 on: July 05, 2017, 08:29:18 am »
DBGrid reads your data from your dataset.
You have two options.
1. Put a rowid in your query. Disadvantage: The id are not sequent.
2. Create a calculated field in your dataset.
    Read the the dataset.recno and put it in the calculated field.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

Hartmut

  • Sr. Member
  • ****
  • Posts: 280
Re: TDBGrid: how to see an additional column with the row number?
« Reply #2 on: July 05, 2017, 07:11:05 pm »
Thank you, mangakissa for your reply.

To your suggestion 1: afaIk has SQLite no possibility to get a rowid in a query.

To your suggestion 2: that seems to be the solution. But I don't get it to run. What I tried is:

Code: Pascal  [Select]
  1. procedure TForm.Button1Click(Sender: TObject);
  2.    var n,i: longint;
  3.    begin
  4.    DBConnection.DatabaseName:='f:\Firefox_Profile\places.sqlite'; {a Firefox-DB}
  5.    SQLQuery1.Close;
  6.       {create a dummy additional column for the row number: }
  7.    SQLQuery1.SQL.Text:= 'select 0 AS rownum, * from moz_places'; {read Firefox-History}
  8.    DBConnection.Connected:= True;
  9.    SQLTransaction1.Active:= True;
  10.    SQLQuery1.Open;
  11.  
  12.    SQLQuery1.Last; {fetches all records from the underlying DB}
  13.    n:=DBGrid1.DataSource.dataset.RecordCount;
  14.    for i:=1 to n do
  15.       begin
  16.       DBGrid1.DataSource.DataSet.RecNo:=i;
  17.       DBGrid1.DataSource.DataSet.FieldByName('rownum').AsInteger:=i;
  18.       end;
  19.    end;  
  20.  
But then I get a runtime error in line 17 'Operation not allowed, dataset "SQLQuery1" is not in an edit or insert state'.
My program shall not change the database.

Can you give me some help please how to "create a calculated column" in my dataset? Thanks in advance.
« Last Edit: July 05, 2017, 07:15:25 pm by Hartmut »

Hartmut

  • Sr. Member
  • ****
  • Posts: 280
Re: TDBGrid: how to see an additional column with the row number?
« Reply #3 on: July 21, 2017, 06:54:29 pm »
Because I did not find a better solution, I did a workaround by copying all the cells of the DBGrid1 into a TStringGrid. Then it was easy to have a 1st column with row numbers by:

Code: Pascal  [Select]
  1. Stringgrid1.Options:=Stringgrid1.Options + [goFixedRowNumbering];
  2. Stringgrid1.FixedCols:=1;

rfwoolf

  • Newbie
  • Posts: 1
The conventional solution to this problem is to create a calculated field, and in the OnCalcFields event, set the column value to the RecNo.
This would work for most people, however in my application, it was very buggy because I rapidly resort the table and apply indexes etc.
So I used this workaround:
Create a dummy field in the TDBGrid, call it 'RowNo'.
Then in the DBGrid OnDrawColumnCell event:

  If Column.Fieldname = 'RowNo' then
  begin
    DBGridNumbers.Canvas.textout(Rect.left, rect.top, inttostr(QryTblNumbers.Recno));
  end;

For me, this produced the desired results.
         

pascalarus

  • Newbie
  • Posts: 1
Re: [Solved] TDBGrid: how to see an additional column with the row number?
« Reply #5 on: August 11, 2019, 01:22:17 am »
I'm not able to check if it works (i uninstalled sqlite recently), but last time I used sqlite, i wrote queries like this :
SQLQuery1.SQL.Text:= 'select rowid, + an explicit list of fields, from table ...';
and if i remember, rowID was correctly extracted from table.