* * *

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

Hartmut

  • Jr. Member
  • **
  • Posts: 94
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: 689
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.64 (32b) / FPC 3.0
Windows 10

Hartmut

  • Jr. Member
  • **
  • Posts: 94
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

  • Jr. Member
  • **
  • Posts: 94
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;

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus