How can get scroll position of DBGrid ?

[mangakissa]

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.

[Handoko]

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.

[mangakissa]

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.

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.

[Handoko]

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.

[Handoko]

@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]

thank you very much.

now : how can i change Appearance search row like This


or This

[Handoko]

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]

Download this DBGrid. Look at the source and try it yourself.

[Handoko]

Downloaded and tested. But I don't know how to make Delphi code works on Lazarus.

Anyway, I visited the website, it looks great.

[majid.ebru]

Downloaded and tested. But I don't know how to make Delphi code works on Lazarus.

Anyway, I visited the website, it looks great.

Hi

@ Handoko

how did you tested it?

i can't instal pakage or open project??

thank you

[Handoko]

You can convert the delphi project to lazarus project:
Lazarus main menu > Tools > Delphi Conversion

I use Linux but my Lazarus/FPC is capable to compile Windows projects. Unfortunately, I failed to compile the SMDBGrid demo. I got error: RXUtils.pas(316,9) Error: Identifier not found "TransparentBlt" when compiling this line:

Code: Pascal  [Select][+][-]

1.         TransparentBlt(Dest.Handle, DstX, DstY, DstW, DstH,
2.           Temp.Canvas.Handle, SrcX, SrcY, SrcW, SrcH,
3.           TransparentColor);

[valdir.marcos]

I got error: RXUtils.pas(316,9) Error: Identifier not found "TransparentBlt"

RX components are already converted to Lazarus. Are you using them from OPM?

[Handoko]

Thanks for informing me that the RX components is available on OPM.

I've just installed it and 'force' the code to use the installed RXUtils. It seemed the issue has been solved but now I got a new problem:

SMDBGrid.pas(193,15) Error: There is no method in an ancestor class to be overridden: "RestoreDefaults;" on the code below (line #40):

Code: Pascal  [Select][+][-]

1.   TSMDBColumn = class({$IFDEF USE_TNT} TTntColumn {$ELSE} TColumn{$ENDIF})
2.   private
3.     FBandIndex: Integer;
4.  
5.     FSortCaption: TSMGString;
6.     FSortType: TSMSortType;
7.     FInplaceEditor: TSMInplaceEditorType;
8.  
9.     FFooterValue: Variant;
10.     FFooterType: TSMFooterType;
11.     FFooterText: TSMGString;
12.  
13.     FTag: Longint;
14.     FTextEllipsis: TSMTextEllipsis;
15.  
16.     FFilterValue: TSMGString;
17.     FFilterList: TStrings;
18.  
19.     // Mark a column to be able to get filtered. Eliminates the filter box for this column.
20.     FFilterable: Boolean;
21.  
22.     procedure SetFooterValue(Value: Variant);
23.     procedure SetFooterType(Value: TSMFooterType);
24.     procedure SetSortCaption(Value: TSMGString);
25.     procedure SetSortType(Value: TSMSortType);
26.     procedure SetInplaceEditor(Value: TSMInplaceEditorType);
27.     procedure SetFilterValue(const Value: TSMGString);
28.     procedure SetFooterText(const Value: TSMGString);
29.     function GetFilterList: TStrings;
30.     procedure SetFilterList(const Value: TStrings);
31.   protected
32.     intInternalCount: Integer; {required for Avg calculation}
33.  
34.     function GetGrid: TSMDBGrid;
35.   public
36.     constructor Create(Collection: TCollection); override;
37.     destructor Destroy; override;
38.  
39.     procedure Assign(Source: TPersistent); override;
40.     procedure RestoreDefaults; override;
41. ...

It seems the code is not very compatible with Lazarus/FPC.

[valdir.marcos]

now I got a new problem:
SMDBGrid.pas(193,15) Error: There is no method in an ancestor class to be overridden: "RestoreDefaults;" on the code below (line #40):

Since there is nothing to be overridden, just delete the "override;" part and let the "new" RestoreDefaults method be used:

Code: Pascal  [Select][+][-]

1.   procedure RestoreDefaults;

[Handoko]

Well, removing that "override" keyword solved that issue. But now I got
 SMDBGrid.pas(304,17) Error: Identifier not found "TSMGradientDirection":

Code: Pascal  [Select][+][-]

1.   TSMGradientDraw = class(TPersistent)
2.   private
3.     FGrid: TSMDBGrid;
4.     FDirection: TSMGradientDirection;

Here, this are the things in the uses clause of SMDBGrid.pas:

Code: Pascal  [Select][+][-]

1. unit SMDBGrid;
2.  
3. {$MODE Delphi}
4.  
5. interface
6.  
7. {$I SMVersion.inc}
8.  
9. {used only if BLOB fields with graphics contains the jpeg's}
10. //{$DEFINE USE_JPEG}
11.  
12. {$IFDEF USE_DBEXPRESS}
13.         {$IFNDEF VER_ENTERPRISE}
14.                 {$DEFINE VER_ENTERPRISE}
15.         {$ENDIF}
16. {$ENDIF}
17.  
18. uses
19.   LCLIntf, LCLType, LMessages, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
20.   Menus, Grids, DBGrids, {$IFDEF USE_TNT} TntDBGrids, {$ENDIF} DB, StdCtrls, SMCnst,
21.   {$IFDEF SMForDelphi6} Variants, {$ENDIF}
22.   {$IFDEF USE_JPEG} Jpeg, {$ENDIF}
23.   RXUtils {ex-VCLUtils from RX-Lib};

And even if the problem above has solved, there still 9 more 7 more errors in the messages window: