Using Bergsoft's Next(DB)Grid with Delphi

Temp

grid styles: Report & Slides

Input line for quick input

19 Column types

Standalone Inplace Editors

Column Footers

Column may be sorted in 7 different sorting types: Alphabetic, Boolean, CaseInsensitive, Custom (with using OnCompare event), Date, Numeric and sorting IP addresses.

Introduction

Bergsoft's Next(DB)Grid is one of the main alternative grids to the basic TStringGrid that ships with Delphi. It comes in two versions: Unbound (NextGrid), and Database-aware (NextDBGrid).

In addition to the .CHM file and stuff under \Demos, NextGrid Quick Start and other articles are available online.

A grid is actually two parts: Columns, and Rows. Rows themselves are split into Cells.

Note: The real site is BergSoft.net. Stuff on BergSoftware.net is outdated and is left over after the two developpers who built the BergGridView split (they're fighting over ownership.)

The main sources of information:

Setup

Two passwords are provided when buying a component: One is to have access to the Members section, from which components and upgrades can be downloaded; The other is provided and required when running the installer (located after you click on file name in "Downloads" section > "Serial Number")

The Installer only installs the Berg files on the disk, and optionally updates the IDE's Library section to include paths to the Berg directory. It doesn't actually compile and install the components. For this, open the IDE, check the PDF to open the right .DPK project, compile, and install each component in the right order.

Here's how to install the grid in Delphi 2007:

  1. Close the IDE
  2. Download and run nextgrids_XXXX.exe
  3. Choose the right IDE to update the Library path
  4. Open the IDE
  5. Go the Library Path section, and remove greyed-out paths wrongly added by the installer
  6. Go to where Berg NextGrid was installed, open the 2007.bdsgroup, then compile and install the following packages in this order (choose relevant packages):
    1. NxCommonRun.dpk, NxCommonDsgn.dpk, NxGridRun.dpk, NxGridDsgn.dpk, NxDBGridRun.dpk, NxDBGridDsgn.dpk
    2. NxCollectionRun.dpk, NxCollectionDsgn.dpk,
    3. NxInspectorRun.dpk, NxInspectorDsgn.dpk, NxSheetRun.dpk
    4. NxAddonsRun.dpk
    5. NxThirdPartyRun.dpk (Optional: Required for NxDataCellSource, NxGridPrint)

Create a new VCL project: Four new groups should have been added to the Palette: Next Suite, Next Editors, Next Collection, Next Addons.

As of May 2008, Bergsoft doesn't provide an updater, and you must perform upgrades yourself. Here's how to do it when using the EXE version:

  1. Run the Delphi IDE, and remove the Berg packages through Component > Install Packages
  2. Remove the paths to Berg components through Tools > Options > Library Win32. Otherwise, the installer will re-add them
  3. Close the Delphi IDE (important)
  4. Run the Uninstaller located in the “Next Suite” sub-folder, or inside the Berg program group
  5. Check that all of the files were removed in eg. C:\... Berg
  6. Run the new installer

Here's how to do it using the ZIP version:

  1. Run the Delphi IDE, and remove the Berg packages through Component > Install Packages
  2. Close the Delphi IDE (important)
  3. Go into the "Next Suite" sub-folder, and remove all the files
  4. Sign in, and download the latest ZIP package from Berg's site in the same directory, or update the IDE (Tools > Options > Library) accordingly
  5. Relaunch the IDE
  6. Go into the Berg\Packages directory, open eg. "Delphi 2007.bdsgroup", and install each required package per the instructions above. You may have some errors if the project group file contains references to components you don't have
  7. Close all projects without saving

You can check that the grid was updated by adding a widget on a form, right-clicking on it, and choosing the Version item. More information on updating from the ZIP file in Quick update.

Coding

Important:

  1. You must add this to the Uses clause:

    NxColumns, NxColumnClasses

    Otherwise, you'll get this error: "E2003 Undeclared identifier: 'coAutoSize'"
  2. When using methods/properties that deal with how columns are displayed, the order in which the instructions are called is important. For instance, try to reverse the order of those two instructions, and see how the columns look in the grid:

        for index := 0 to Columns.Count - 1 do begin
          BestFitColumn(index, bfBoth);
        end;
        Columns.ResizeColumns();

Column Types

Auto-incremented row ID

Create a column of type TNxIncrementColumn so as to display each row's ID number.

Handling joined SELECT in NextGrid

The issue when using the (non-DB-aware) NextGrid to display the output of a SELECT that grabs data from multiple tables, ie. a joined query, is how to handle updates, since columns don't belong to the same table, so we have to find the row ID to which a given column belongs to when we build the relevant UPDATE query.

A solution I found is the following:

  1. In the SELECT, includes all the non-ID columns that the user needs; If the user might need to update some of them, make those editable while leaving other columns read-only
  2. For any column that might need to be edited, include its row ID in the SELECT
  3. Unless the user has the need to see them, hide the ID columns (ColumnByName['id'].Visible := False), since they're only used internally to handle updates
  4. For each column, use the Tag property: If the column contains a table's ID, set it to nil; If it contains a data column, set it to the column that contains its row ID. "Tag" isn't very explicite, and it'd be better if NextGrid had eg. a ParentColumn property, but it works

Here's an example:

CREATE TABLE t1 (t1_id INTEGER PRIMARY KEY, t1_somedatacolumn VARCHAR);
CREATE TABLE t2 (t2_id INTEGER PRIMARY KEY, t2_somedatacolumn VARCHAR);
 
Columns.Add(TNxTextColumn,'Table1 ID');
Columns[0].Name := 't1_id';
Columns[0].Tag := nil;
 
Columns.Add(TNxTextColumn,'Table1 SomeData');
Columns[1].Name := 't1_somedatacolumn';
Columns[1].Tag := ColumnByName['t1_id'].Index;
 
Columns.Add(TNxTextColumn,'Table2 ID');
Columns[2].Name := 't2_id';
Columns[2].Tag := nil;
 
Columns.Add(TNxTextColumn,'Table2 SomeData');
Columns[3].Name := 't2_somedatacolumn';
Columns[3].Tag := ColumnByName['t2_id'].Index;

Enabling sorting in NextDBGrid

By default, the up/down arrow is shown when clicking on a cell, but rows aren't actually sorted. This is due to the fact that data are fetched from the dataset. More info here.

Wrong sorting order in numeric columns in NextGrid

If the column was created as TNxTextColumn instead of TNxNumberColumn, you need to tell NextGrid that this column is actually numeric:

NextGrid1.ColumnByName['id'].SortType := stNumeric;

Alternatively, create the column as TNxNumberColumn:

//Caption
NextGrid1.Columns.Add(TNxNumberColumn,MySL.Values['id']);
//Name
NextGrid1.Columns[0].Name := 'id';

Reading fields from the currently-selected row in NextDBGrid

Uses [...], NxColumns, NxColumnClasses;
//Read column names from dataset, and set each column's Name property
//so as to referer to columns through their name instead of index
DataSource1.DataSet := ASQLite3Query1;
 
With NextDBGrid1 do begin
    DataSource := DataSource1;
    for i := 0 to Columns.Count - 1 do begin
        Columns[i].Name := Columns[i].FieldName;
    end;
end;
  
ShowMessage((NextDBGrid1.ColumnByName['label'] as TNxDBCustomColumn).Field.AsString);

Customizing a NextGrid look

Here's how to make sure the right-most column fills the whole available space, selecting a cell selects the whole row, and the user can select more than one row at a time:

With NextDBGrid1 do begin
    Options := Options + [goMultiSelect,goSelectFullRow];
end;

Finding the selected row

Here's how to read the first column in the currently row that was selected by double-clicking on it:

With Frame21.NextGrid1 do begin
    ShowMessage(Cell[0,SelectedRow].AsString);
end;

Looping through selected rows

With NextDBGrid1 do begin
    Options := Options + [goMultiSelect,goSelectFullRow];
end;

ShowMessage('Select Count:' + IntToStr(NextDBGrid1.SelectedCount));

for i := 0 to NextDBGrid1.RowCount - 1 do begin
    if NextDBGrid1.Selected[i] then
        ShowMessage(NextDBGrid1.Cells[0, i]);
end;

Refering to a specific column

NextGrid

Either NextGrid1.Columns.Column[0]

or

NextGrid1.Columns.Item[0]

Note that some properties like Caption are unique to header/footer and not available for columns. Also, when refering to column-type specific properties, you need to use typecast such as:

TNxComboBoxColumn(NextGrid1.Columns[3]).Items...

NextDBGrid

NextDBGrid1.Columns[4].Field.AsInteger;

It's also possible to refer to a column by its name instead:

NextDBGrid1.Columns.ColumnByName['name'].Field

Using names instead of numbers for columns

procedure TForm1.FormCreate(Sender: TObject);
var
  i : Integer;
begin
  [...]
 
  With NextDBGrid1 do begin
    DataSource := DataSource1;
    for i := 0 to Columns.Count - 1 do begin
      BestFitColumn(i, bfBoth);
      Columns[i].Name := Columns[i].FieldName;
    end;
  end;
end;
 
//Pop-up menu has item "CD"
procedure TForm1.CD1Click(Sender: TObject);
var
  i : Integer;
begin
  for i := 0 to NextDBGrid1.RowCount - 1 do begin
    if NextDBGrid1.Selected[i] then begin
  
    //E2010 Incompatible types: 'Integer' and 'string'
      ShowMessage(NextDBGrid1.Columns[i].Name + '=' + NextDBGrid1.Cells['id', i]);
 
    end;
  end;
end;

Filling a NextGrid with a TStringList

If you need to use a NextGrid instead of a NextDBGrid and still work with a Query object, it's more user-friendly to provide beautified column names instead of SQL column names(eg. "Identification" vs. "id".) An easy way to do this, is to use a TStringList, where the SQL name acts as the key, and the beautified name as the value:

var
  MySL : TStringList;
begin
  MySL := TStringList.Create;
 
  try
    MySL.CommaText := 'id=Identification,label=Label';
    //In case some values contain spaces
    MySL.StrictDelimiter := True; 
 
    With NextGrid1 do begin
      AddRow(1);
 
      for I := 0 to 1 do begin
        Columns.Add(TNxTextColumn,MySL.ValueFromIndex[I]);
        Columns[index].Name := MySL.Names[index];
        BestFitColumn(i, bfCells);
      end;
      Columns[1].Options := Columns[1].Options + [coAutoSize];
      AppearanceOptions := AppearanceOptions + [aoHideFocus];
      Options := Options + [goSelectFullRow,goMultiSelect];
 
      J := 0;
      Cells[0,J] := 'item1';
      Cells[1,J] := 'item2';
      //E2035 Not enough actual parameters
      {
      CellByName['key,I] := 'item1';
      CellByName['value,I] := 'item2';
      }
    end;
  finally
    MySL.Free;
  end;

Customizing column captions in a NextDBGrid

Array?

Adding columns to a NextGrid at run-time

With NextGrid1 do begin
    Columns.Add(TNxTextColumn,'Col1');
    Columns.Add(TNxTextColumn,'Col2');
 
    //No longer needed Columns[0].Header.Caption :=     ASQLite3Query1.Fields[0].FieldName;
    //Columns[1].Header.Caption := ASQLite3Query1.Fields[1].FieldName;
    Columns[1].Options := NextGrid1.Columns[1].Options + [coAutoSize];
 
    AddRow(ASQLite3Query1.RecordCount);
end;

Having a NextDBGrid column fit widest string

NextDBGrid1.BestFitColumn(0, bfBoth);

If you want all columns to be set that way, you'll need to loop through them:

var i: Integer;
begin
  for i := 0 to NextDBGrid1.Columns.Count - 1 do begin
    NextDBGrid1.BestFitColumn(i);
  end;

Clearing a grid

NextGrid1.Columns.Clear;

NextGrid1.ClearRows;

Adding rows

The first thing you must do to add data to a grid is to create rows for them using NextGrid1.AddRow(x).

Converting datatypes

NextGrid1.Tag := NextGrid1.Cell[0,NextGrid1.SelectedRow].AsInteger;

Filling cells

The first digit is the column, the second digit is the row:

GridView1.Cells[0, 1] := 'Col 0, Row 1';
 
Cells[0, i] := ASQLite3Query1.Fields[i].FieldName;
 
NextGrid1.Cells[i, row] := ASQLite3Query1.FieldByName('id').AsString;

Here's how to loop through a query, and fill a NextGrid widget:

NOTE : define row/i and create number of rows in NextGrid before filling it!

row := 0;
ASQLite3Query1.First;
while not ASQLite3Query1.Eof do begin
    for i := 0 to ASQLite3Query1.Fields.Count - 1 do begin
        NextGrid1.Cells[i, row] := ASQLite3Query1.Fields[i].AsString;
    end;
    inc(row);
    ASQLite3Query1.Next;
end;

Using the NextDBGrid

To use NextDBGrid, the DB-aware version of NextGrid, you just need to connect a DataSource widget to a query or table, and connect the NextDBGrid widget to the DataSource:

DataSource1.DataSet := ASQLite3Query1;

NextDBGrid1.DataSource := DataSource1;

Building a TValueListEditor-like grid

Here's how to build a two-column grid to display key-value tuples. First, add a NextGrid to the form, and the following items in the Uses section: NxColumns, NxColumnClasses, ExtCtrls;

Next, select the grid, double-click on its OnCustomDrawCell event, and paste this code:

//NextGrid1.OnCustomDrawCell event
procedure TForm1.NextGrid1CustomDrawCell(Sender: TObject; ACol, ARow: Integer; CellRect: TRect; CellState: TCellState);
var
  R: TRect;
begin
  with NextGrid1.Canvas do begin
    R := CellRect;
    Frame3D(NextGrid1.Canvas, R, clBtnHighlight, clBtnShadow, 1);
    Brush.Color := clBtnFace;
    FillRect(R);
    TextRect(R, CellRect.Left + 4, CellRect.Top + 2, NextGrid1.Cells[ACol, ARow]);
  end;
end;

Finally, double-click on the OnActivate form event, and paste this code:

procedure TForm1.FormActivate(Sender: TObject);
begin
    //NextGrid1.AppearanceOptions := NextGrid1.AppearanceOptions + [aoHideFocus];

    //Second parameter = caption, not name
    Columns.Add(TNxTextColumn,'Key');
    Columns.Add(TNxTextColumn,'Value');
    Columns[0].Name := 'Key';
    Columns[1].Name := 'Value';
 
    NextGrid1.FixedCols := 1;
    NextGrid1.Columns[0].DrawingOptions := doCustomOnly;
 
    //If you'd rather not specify the index of the right-most column:
    //NextDBGrid1.Columns[ASQLite3Query1.FieldCount-1].Options :=    //    NextDBGrid1.Columns[ASQLite3Query1.FieldCount-1].Options + [coAutoSize];
    NextGrid1.Columns[1].Options := NextGrid1.Columns[1].Options + [coAutoSize];
 
    NextGrid1.AddRow;
 
    CellsByName['Key', 0] := 'key 1';
    CellsByName['Value', 0] := 'value 1';
end;
 

Building a key/value NextGrid with key column as a TNxTreeView

In case we want to display different sections, it's useful to have the first column be a TNxTreeView, so that the user can hit the +/- button to display or hide the key/value tuples that belong to this item in the tree:

 

Making a NextDBGrid editable

It's not possible to make the whole grid editable in one go. Instead, you must loop through each column, and set its coEditing property to true:

NextDBGrid1.Columns[0].Options := NextDBGrid1.Columns[0].Options + [coEditing];

Adding a checkbox in a cell

procedure TForm1.NextDBGrid1ColumnCreate(Sender: TObject; Field: TField; var ColumnClass: TNxDBColumnClass; var AddColumn: Boolean);
begin
    if Field.FieldName = 'Sent' then begin
        ColumnClass := TNxDBCheckBoxColumn;
    end;
end;
 
procedure TForm1.NextDBGrid1ColumnAdded(Sender: TObject; Column: TNxDBCustomColumn);
begin
    if Column is TNxDBCheckBoxColumn then begin
        with Column as TNxDBCheckBoxColumn do begin
            ValueChecked := '1';
            ValueUnchecked := '0';
 
            //How to get column id, and avoid hard-coding column width? BestFitColumn(0, bfBoth);
            Width := 50;
        end;
    end;
end;

Widening a NextDBGrid

Here's how to make sure a NextDBGrid column is wide enough so that its header can be read:

Scrolling down to the last row in NextGrid

ScrollToRow(RowCount);

Connecting to SQLite with the Aducom controls

UpdateSQL only needed when using a DB-aware control. Otherwise just use Query control.

Creating a Tree column at run-time

Some of TNxTreeView's properties such as ExpandLock and ShowButtons require typecasting to be accessed at run-time:

TNxTreeColumn(ColumnByName['key']).ExpandLock := False;
TNxTreeColumn(ColumnByName['key']).ShowButtons := False;

Tips & Tricks

Note that if you use a TEXT field in SQLite, you must choose a a NxDBMemoColumn item in the NextDBGrid to display it (available in 4.2.4 and above), and set its MemoDisplayOptions property to mdContent. Otherwise, you'll get a "(MEMO)". This is not necessary when using the NextGrid widget. Alternative: Use a VARCHAR.

Resources