* * *

Author Topic: How can get scroll position of DBGrid ?  (Read 1418 times)

mangakissa

  • Hero Member
  • *****
  • Posts: 689
Re: How can get scroll position of DBGrid ?
« Reply #15 on: June 14, 2017, 09:38:53 am »
TStringgrid is not the option for records on your dataset. Yes, TDBGrid derives from TStringgrid, but has extra layer for selecting / showing your requested data.
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

Handoko

  • Hero Member
  • *****
  • Posts: 1326
  • My goal: build my own game engine using Lazarus
Re: How can get scroll position of DBGrid ?
« Reply #16 on: June 14, 2017, 10:33:01 am »
I just checked the documentation, TDBGrid does not derive from TStringGrid. They only share a common ancestor TCustomGrid:
http://lazarus-ccr.sourceforge.net/docs/lcl/dbgrids/tdbgrid.html

I saw TDBGrid has OnPrepareCanvas too, it may be able to do the background color highlight. Using TDBGrid has its own advantages, it makes the data loading and saving automatically. But in TS case, I recommend to use TStringGrid not TDBGrid because:

- To achieve what TS want we need something more flexible than TDBGrid. For example in TStringGrid we can easily get and move the active cell to other row or column using code, and we can do multiple selections. I can't found it in TDBGrid.

- If for simply showing or editing data, I recommend to use TDBGrid. But if for doing search or filtering, I think it will be better to load data from database, disconnect it. And let users to manipulate the data and give them "Reload Data", "Save" and "Save As" features. It will prevent users accidentally mess up the data also good for preventing database error/corrupt when power failure happens when it opened for a long period of time.

I can be wrong, that's just my 2 cents.

i just want that Tedit exactly stay on culome and when user move scroll of DBGrid,i can move it.

It can be done using TStringGrid. You need use TStringGrid.Option > enable goEditing. Also you have to add more smarter codes:

- When loading data skip showing it on the first row in the TStringGrid, because it is used for searching.
- Check if the cells in the first row, if it is not empty then do the searching.
- Add code to prevent users to edit data (or lock) cells other than first row.

My previous example shows to how to search data if user provide text in LabelEdit1. You need to modify the code to read data from first-row cells of each coloumn.
« Last Edit: June 14, 2017, 10:38:23 am by Handoko »

mangakissa

  • Hero Member
  • *****
  • Posts: 689
Re: How can get scroll position of DBGrid ?
« Reply #17 on: June 14, 2017, 10:57:24 am »
Quote from: majid.ebru
i thinck there isn't deferent between search and filter.
Define search:
When searching on a particular value, the cursor of the dataset jumps to the first founded record

Define Filter:
When searching on a particular value, only the records that matches are visible on screen.

Quote
To achieve what TS want we need something more flexible than TDBGrid. For example in TStringGrid we can easily get and move the active cell to other row or column using code, and we can do multiple selections. I can't found it in TDBGrid.
TDBGrid can select multiple selections.
With TStringgrid you're really disconnected from the dataset. Locating records for updating is a lot more work to get the right record to be modified.  Therefore you have at least a primary key to find the right record.

It's his choice. I have somewhere code from a TDBGrid that has an filter/search built in. I shaw look at it.
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

Handoko

  • Hero Member
  • *****
  • Posts: 1326
  • My goal: build my own game engine using Lazarus
Re: How can get scroll position of DBGrid ?
« Reply #18 on: June 14, 2017, 11:09:51 am »
With TStringgrid you're really disconnected from the dataset. Locating records for updating is a lot more work to get the right record to be modified.  Therefore you have at least a primary key to find the right record.

Okay, I can see your points.

I have somewhere code from a TDBGrid that has an filter/search built in. I shaw look at it.

It would be great if you are willing to show us or at least explain the basic things how it works. Database programming is not my expertise, I spend most of my time playing with graphics and other time-wasting things. :D
« Last Edit: June 14, 2017, 11:11:23 am by Handoko »

Handoko

  • Hero Member
  • *****
  • Posts: 1326
  • My goal: build my own game engine using Lazarus
Re: How can get scroll position of DBGrid ?
« Reply #19 on: June 14, 2017, 03:03:18 pm »
@majid.ebru

I've modified the code. Now the search box is inside the grid, you can see a pale green row. Type your search text there and it will automatically do searching. You can do multiple column search.

Code: Pascal  [Select]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, Graphics, dbf, db, Forms, StdCtrls, ExtCtrls, ComCtrls,
  9.   Grids, Dialogs;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     btnOpen: TButton;
  17.     DataSource1: TDataSource;
  18.     Dbf1: TDbf;
  19.     Label1: TLabel;
  20.     StringGrid1: TStringGrid;
  21.     procedure btnOpenClick(Sender: TObject);
  22.     procedure StringGrid1Click(Sender: TObject);
  23.     procedure StringGrid1EditingDone(Sender: TObject);
  24.     procedure StringGrid1PrepareCanvas(sender: TObject; aCol, aRow: Integer;
  25.       aState: TGridDrawState);
  26.   end;
  27.  
  28. const
  29.   DataFileName = 'MyData.dbf';
  30.  
  31. type
  32.   TPosition = record
  33.     Col: Integer;
  34.     Row: Integer
  35.   end;
  36.  
  37. var
  38.   Form1: TForm1;
  39.   Results: array of TPosition;
  40.  
  41. implementation
  42.  
  43. {$R *.lfm}
  44.  
  45. { TForm1 }
  46.  
  47. procedure TForm1.btnOpenClick(Sender: TObject);
  48. var
  49.   i: Integer;
  50. begin
  51.  
  52.   // Open the database
  53.   Dbf1.FilePathFull := ExtractFileDir(ParamStr(0));
  54.   Dbf1.TableName := DataFileName;
  55.   Dbf1.Open;
  56.  
  57.   // Set StringGrid's row and column
  58.   StringGrid1.RowCount := Dbf1.RecordCount + 2; // +2 because it includes header and search row
  59.   for i := 1 to Dbf1.FieldCount do
  60.   begin
  61.     StringGrid1.Columns.Add;
  62.     StringGrid1.Columns[i-1].Title.Caption := Dbf1.FieldDefs[i-1].Name;
  63.     StringGrid1.Columns[i-1].Title.Alignment := taCenter;
  64.     StringGrid1.Columns[i-1].Title.Font.Style := [fsBold];
  65.     StringGrid1.Columns[i-1].Width := 120;
  66.   end;
  67.  
  68.   // Load data to StringGrid
  69.   Dbf1.First;
  70.   while not(Dbf1.EOF) do
  71.   begin
  72.     StringGrid1.Cells[0, Dbf1.RecNo+1] := IntToStr(Dbf1.RecNo);
  73.     for i := 0 to (Dbf1.FieldCount-1) do
  74.       StringGrid1.Cells[i+1, Dbf1.RecNo+1] := Dbf1.Fields[i].AsString;
  75.     Dbf1.Next;
  76.   end;
  77.  
  78.   // Close the database
  79.   Dbf1.Close;
  80.  
  81.   StringGrid1.Enabled := True;
  82.   StringGrid1.AutoAdvance := aaNone; // Disable it to prevent problem
  83.   btnOpen.Enabled := False;
  84.   Label1.Visible := True;
  85.   StringGrid1Click(Sender); // Show row:column info
  86.  
  87. end;
  88.  
  89. procedure TForm1.StringGrid1Click(Sender: TObject);
  90. var
  91.   ActiveRow, ActiveCol: Integer;
  92.   i: Integer;
  93. begin
  94.  
  95.   ActiveRow := StringGrid1.Row;
  96.   ActiveCol := StringGrid1.Col;
  97.  
  98.   // Allow editing on first row
  99.   if (ActiveRow = 1) then
  100.     StringGrid1.Options := StringGrid1.Options+[goAlwaysShowEditor, goEditing]
  101.   else
  102.     StringGrid1.Options := StringGrid1.Options-[goAlwaysShowEditor, goEditing];
  103.  
  104.   // Show row:column information
  105.   Label1.Caption := 'R:C = ' + IntToStr(ActiveRow-1) +':'+ IntToStr(ActiveCol);
  106.  
  107.   // Change selected column's header text color
  108.   for i := 0 to (StringGrid1.Columns.Count-1) do
  109.     if (i = StringGrid1.Col-1) then
  110.       StringGrid1.Columns[i].Title.Font.Color := clWhite
  111.     else
  112.       StringGrid1.Columns[i].Title.Font.Color := clBlack;
  113.  
  114. end;
  115.  
  116. // Use OnEditingDone to do searching
  117. procedure TForm1.StringGrid1EditingDone(Sender: TObject);
  118. var
  119.   SearchText: string;
  120.   c, r: Integer;
  121. begin
  122.   SetLength(Results, 0);
  123.   for c := 1 to StringGrid1.Columns.Count do
  124.   begin
  125.     SearchText := StringGrid1.Cells[c, 1];
  126.     if (SearchText <> '') then
  127.     begin
  128.       for r := 2 to (StringGrid1.RowCount-1) do
  129.       begin
  130.         if Pos(SearchText, StringGrid1.Cells[c, r]) > 0 then
  131.         begin
  132.           SetLength(Results, Length(Results)+1);
  133.           Results[High(Results)].Col := c;
  134.           Results[High(Results)].Row := r;
  135.         end;
  136.       end;
  137.       StringGrid1.Repaint;
  138.     end;
  139.   end;
  140. end;
  141.  
  142. procedure TForm1.StringGrid1PrepareCanvas(sender: TObject; aCol, aRow: Integer;
  143.   aState: TGridDrawState);
  144. var
  145.   i: Integer;
  146. begin
  147.  
  148.   if not(sender is TStringGrid) then Exit;
  149.  
  150.   // Background of the search row is moneygreen
  151.   if (aRow = 1) and (aCol > 0) then
  152.     (sender as TStringGrid).Canvas.Brush.Color := clMoneyGreen;
  153.  
  154.   // If search cell has text, change the cell color to red
  155.   if (aRow = 1) and (StringGrid1.Cells[aCol, aRow] <> '') then
  156.     (sender as TStringGrid).Canvas.Brush.Color := clAqua;
  157.  
  158.   // Highlight selected column's header with green
  159.   if (aRow = 0) and (aCol = StringGrid1.Col) then
  160.     (sender as TStringGrid).Canvas.Brush.Color := clGreen;
  161.  
  162.   // Background of search result cells is yellow
  163.   for i := Low(Results) to High(Results) do
  164.     if (Results[i].Col = aCol) and (Results[i].Row = aRow) then
  165.     begin
  166.       (sender as TStringGrid).Canvas.Brush.Color := clYellow;
  167.       Exit; // Exit immediately after found, to improve performance
  168.     end;
  169.  
  170. end;
  171.  
  172. end.

majid.ebru

  • Full Member
  • ***
  • Posts: 200
Re: How can get scroll position of DBGrid ?
« Reply #20 on: June 17, 2017, 03:06:08 pm »
thank you very much.

now : how can i change Appearance search row like This


or This
« Last Edit: June 17, 2017, 03:13:04 pm by majid.ebru »

Handoko

  • Hero Member
  • *****
  • Posts: 1326
  • My goal: build my own game engine using Lazarus
Re: How can get scroll position of DBGrid ?
« Reply #21 on: June 17, 2017, 03:22:05 pm »
I never tried but I think it is possible. You may need to use TDrawGrid. In TStringGrid we can change the appearance but very limited, only color and font. Using TDrawGrid you can draw the appearance to whatever you like.

mangakissa

  • Hero Member
  • *****
  • Posts: 689
Re: How can get scroll position of DBGrid ?
« Reply #22 on: June 19, 2017, 09:06:01 am »
Download this DBGrid. Look at the source and try it yourself.
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

Handoko

  • Hero Member
  • *****
  • Posts: 1326
  • My goal: build my own game engine using Lazarus
Re: How can get scroll position of DBGrid ?
« Reply #23 on: June 19, 2017, 09:16:52 am »
Downloaded and tested. But I don't know how to make Delphi code works on Lazarus. :'(

Anyway, I visited the website, it looks great.

 

Recent

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