Tips and Frequently Asked Questions
Lookup other lookup source DB field values when editing with a TAdvDBLookupCombox in TDBAdvGrid
When you want to access or use values from other listsource fields used in a TAdvDBLookupComboBox when it is used in a TDBAdvGrid as inplace editor, the appropriate place where to access the dataset field values is from the FormControlEditLink.OnGetEditorValue() event. This event is triggered when the DBAdvGrid is about to be updated with the updated value from the TAdvDBLookupComboBox and thus, at this time, the values for other DB fields in the lookup source can be accessed by accessing the lookup source dataset fields.
In the sample, this is done by getting the value for the field ‘Capital’ in the COUNTRY table and storing that field value also in the main TDBAdvGrid in a readonly column.
The code is:
procedure TForm1.FormControlEditLink1GetEditorValue(Sender: TObject;
Grid: TAdvStringGrid; var AValue: string);
var
s:string;
begin
AValue := AdvDBLookupComboBox1.Text;
// get the value from the lookup dataset
s := adotable2.FieldByName(''Capital'').AsString;
// store the value in the main dataset
adotable1.FieldByName(''Capital'').AsString := s;
end;
A full sample source application can be downloaded via:
https://download.tmssoftware.com/download/advdblookupcomboboxindbadvgrid.zip
Doing custom dataset filtering in TDBAdvGrid from the filter edit row
Default, the grid performs built-in filtering of data. It can be desirable to not use the built-in grid filtering but instead perform the filtering directly on the dataset but still use the filter edit UI to enter the filter condition.
To perform the dataset filtering, implement the OnFilterEditUpdate and from this event, set the flag grid.DoAutoEditFilter = false. When this flag is set false, the OnFilterEditDone event will be triggered from where the code can be added to specify the filter.
Example implementation in code with a grid bound to a TADOQuery dataset assuming the filter condition is set in the filter UI for the ''BRAND'' or ''TYPE'' field in the dataset:
procedure TForm1.FormCreate(Sender: TObject); begin
adoquery1.SQL.Text := ''SELECT * FROM CARS'';
adoquery1.Active := true;
dbadvgrid1.FilterEdit.Enabled := true; end; end;
procedure TForm1.DBAdvGrid1FilterEditDone(Sender: TObject; ACol: Integer;
Condition: string; FilterType: TFilterType); begin
//event is triggered when built-in filtering is not used and from here custom dataset filtering can be done
if ACol = BrandColumn then
adoquery1.SQL.Text := ''SELECT * FROM CARS WHERE BRAND LIKE ''''''+
Condition+'''''''';
if ACol = TypeColumn then
adoquery1.SQL.Text := ''SELECT * FROM CARS WHERE TYPE LIKE ''''''+
Condition+'''''''';
adoquery1.Active := true;
end;
procedure TForm1.DBAdvGrid1FilterEditUpdate(Sender: TObject; ACol: Integer;
Condition: string; FilterType: TFilterType); begin
//just set the flag that built-in filtering will not be used
DBAdvGrid1.DoAutoEditFilter := false;
end;
Dynamically filtering lookup datasets in a TDBAdvGrid
It is easy to dynamically set a filter for a lookup dataset depending on other values for a lookup editor in the TDBAdvGrid. DB fields with a lookup relatoinship are automatically edited via a combobox. To filter the values to select from, filter the lookup dataset from the OnGetEditorProp event that is triggered just before the inplace editor becomes active. Following example filter can be applied in the ADOLookup demo for TDBAdvGrid that filters the lookup dataset for countries starting with ‘S’ when there is editing on an odd row.
procedure TForm1.DBAdvGrid1GetEditorProp(Sender: TObject; ACol, ARow: Integer;
AEditLink: TEditLink);
begin
if odd(arow) then
begin
adotable2.Filtered := false;
adotable2.Filter := ''COUNTRY LIKE "S%"'';
adotable2.Filtered := true;
end
else
begin
adotable2.Filtered := false;
end;
end;
How to show a hint when hovering over the column header
You can use the OnGridHint event and via the var parameter hintstr specify the hint for each cell.
Multi-column sorting in TDBAdvGrid
When:
DBAdvGrid.PageMode := False;
DBAdvGrid.SortSettings.Show := True;
DBAdvGrid.SortSettings.IndexShow := True;
a regular click on a column header starts the primary column sort, additional columns are then sorted by a shift-click on their column header.
TMS TDBAdvGrid & PageMode
How to format the numbers in the floatingfooter
The floatingfooter row can be treated as the last row in the grid. I.e. the format applied to the floating footer is the format of the cells at row position grid.RowCount -1.
You can use the event OnGetFloatFormat() to dynamically control this format of the last row.
Persisting column sizes & column order for a TDBAdvGrid
Assuming a user can resize & move columns around in a TDBAdvGrid and it is desirable that the last state is saved & restored when the application is restarted, this can be done in following way:
- Set default column order & size state after making the dataset active in the TDBAdvGrid with grid.SetColumnOrder
- When column states are found, restore these from INI file with grid.StringToColumnStates
- When the application closes, save the last state to INI file with grid.ColumnStatesToString
The full code becomes:
procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
var
inif: TINIFile;
begin
inif := TINIFile.Create('.\settings.ini');
inif.WriteString('STATES','DBADVGRID1',DBAdvGrid1.ColumnStatesToString);
inif.Free;
end;
procedure TForm1.FormCreate(Sender: TObject);
var
inif: TINIFile;
colstates: string;
begin
adotable1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\CARS.mdb;Persist Security Info=False';
adotable1.Active := true;
DBAdvGrid1.SetColumnOrder;
DBAdvGrid1.Options := DBAdvGrid1.Options + [goColSizing, goColMoving];
inif := TINIFile.Create('.\settings.ini');
colstates := inif.ReadString('STATES','DBADVGRID1','');
inif.Free;
if colstates <> '' then
DBAdvGrid1.StringToColumnStates(colstates);
end;
Changing font size
The font for header cells is set with:
grid.Columns[].HeaderFont
and the font for regular cells is set with:
grid.Columns[].Font
After sorting on multiple columns, how to keep the settings on the sorting after closing and reopening the connected dataset
The sort information for multicolumn sort can be get & set via DBAdvGrid.SortSettings This has the functions SaveToString / LoadFromString, so you could use these to persist the sort settings.
var
s: string;
s := DBAdvGrid.SortSettings.SaveToString;
DBAdvGrid.SortSettings.LoadFromString(s);
General tips
For optimum performance, grid.PageMode should be set to true. Depending on the TDataSet used, it might further improve
performance to implement the OnGetRecordCount event.
The grid supports internal sorting, filtering, grouping, lookupbar as opposed to sorting, filtering, grouping performed on dataset level.
When it is preferred to use the grids internal implementation as opposed to the dataset level implementation, set grid.PageMode = false.
Features like disjunct row selection, rearranging data (with goRowMoving = true in grid.Options or via drag & drop) also require that
grid.PageMode = false
The scrollbar is only limited to three positions
This behavior is caused by the dataset.
When grid.PageMode = true and DataSetType = dtNonSequenced
the grid only displays a buffer of visible records, the grid has no information at all where this buffer is positioned
in the database except that it is the first buffer, last buffer or not the first or last buffer. Hence, there are only 3
possible scrollbar positions.
When you set grid.PageMode = false, the grid loads & shows all records and can thus show an exact scrollbar position.
How to change a row or cell color depending on a value in the database
Please see the demo ADOSelColor in the TDBAdvGrid samples distribution, this shows how to dynamically set a color on cells depending on DB values.
Images that are stored in BLOB fields are displayed as '(GRAPHIC)''
Please make sure that:
1) the field's datatype is ftBlob
2) DBAdvGrid.ShowPictureFields = true
3) DBAdvGrid.Columns[columnindex].PictureField = true , for the column where the blob is to be displayed
This is also explained at page 191 of the
TMS Grid Pack Developers Guide.
After updating the ClientDataSets, data is missing in the grid and several rows are blank
Make sure that grid.PageMode = true and that grid.DataSetType = dtNonSequenced
That should improve the performance.
This is also explained at page 6 of the
TDBADVGRID
DEVELOPERS GUIDE:
Other than the column properties, some other settings need to be considered. With PageMode set to true, some datasets maintain internally an order for returning the pages of displayed rows to the grid and some not. This depends on the implementation of the TDataSet component that is used to connect to the database of choice. In general, when there is a problem with scrolling in the grid, it is recommended to set the property DataSetType to dtNonSequenced.
When I run my application in C++Builder I get an error: 'unresolved external Gdiplus...referenced from advgdip.pas'
When using components that use GDI+, make sure that your project's main CPP file
includes the line:
#pragma link "gdiplus.lib"
Filtering and sorting in TDBAdvGrid
If you set grid.PageMode= false,
you can use the same capabilities to filter as for a
TAdvStringGrid
If you want to use the built-in sorting capabilities of the grid, you need to set grid.PageMode= false, but you can as well perform sorting on dataset level instead with PageMode = true.
Please have a look at the demos ADOSort / BDESort in the TDBAdvGrid samples distribution as well as the topic on sorting in the
TDBAdvGrid PDF developers guide that explains how you can use the OnCanSort event to achieve this.
Selecting all rows in TDBAdvGrid
The index of the last row in the grid is RowCount -1.
To select all normal selectable rows use following code snippet:
Grid.SelectRows(Grid.FixedRows, Grid.RowCount - 1);
Slow loading date into TDBAdvGrid
Some dataset implementations have a slow MoveBy operation that causes in turn that TDBAdvGrid is slow.
A solution for this is to use a SQL 'SELECT COUNT' operation to supply the number of rows in the dataset to TDBAdvGrid via the event
OnGetRecordCount.
In this code snippet, OnGetRecordCount is implemented as:
procedure TForm1.DBAdvGrid1GetRecordCount(Sender: TObject; var Count: Integer);
begin
Query2.SQL.Text := 'select Count(*) from Country.db';
Query2.Active := True; Count := Query2.Fields[0].AsInteger;
Query2.Active := False;
end;
This is also explained in the
TDBAdvGrid PDF developers guide, page 6.
Using color banding in the grid
Select the colors for even rows and odd rows via Grid.Bands.PrimaryColor and Grid.Bands.SecondaryColor and set Grid.Bands.Active = true. For columns where you want to see the bands, set Grid.Columns[index].ShowBands = true.
Sorting a DB grid by clicking a column header
It is demonstrated in the sample applications ADOSort & BDESort how sorting can be performed by clicking a column header. See sample applications from TDBAdvGrid at the
TMS Grid Pack page.
RAD Studio