1. Using the Multi Select option of a DBGRID
2. Dropdownlist in a DBGrid, HOW ?
3. Sorting Columns in a DBGrid
4. A Dbgrid with colored cells ?
5. DBGrid that shows images
6. Db_QBF.PAS unit allowing Query By Form for DbGrid components in Delphi
7. DBGRID saving the user configuration
8. DBGrid resize
9. Dragging from DbGrid

Using the Multi Select option of a DBGRID

There is an example in the Delphi TIs... Have a look at

http://loki.borland.com/winbin/bds.exe?getdoc+2976+Delphi

{*
   This example iterates through the selected rows
   of the grid and displays the second field of
   the dataset.

   The Method DisableControls is used so that the
   DBGrid will not update when the dataset is changed.
   The last position of the dataset is saved as
   a TBookmark.

   The IndexOf method is called to check whether or
   not the bookmark is still existent.
   The decision of using the IndexOf method rather
   than the Refresh method should be determined by the
   specific application.
*}

procedure TForm1.SelectClick(Sender: TObject);
var
  x: word;
  TempBookmark: TBookMark;
begin
  DBGrid1.Datasource.Dataset.DisableControls;
  with DBgrid1.SelectedRows do
  if Count <> 0 then
  begin
    TempBookmark:= DBGrid1.Datasource.Dataset.GetBookmark;
    for x:= 0 to Count - 1 do
    begin
      if IndexOf(Items[x]) > -1 then
      begin
        DBGrid1.Datasource.Dataset.Bookmark:= Items[x];
        showmessage(DBGrid1.Datasource.Dataset.Fields[1].AsString);
      end;
    end;
  end;
  DBGrid1.Datasource.Dataset.GotoBookmark(TempBookmark);
  DBGrid1.Datasource.Dataset.FreeBookmark(TempBookmark);
  DBGrid1.Datasource.Dataset.EnableControls;
end;

Dropdownlist in a DBGrid, HOW ?

Q: How do I put components into a TDBGrid?

A: I saw this on compuserve and it is great!

HOW TO PUT COMPONENTS INTO A GRID

This article and the accompanying code shows how to put just about any component into a cell on a grid. By component I mean anything from a simple combobox to a more complicated dialog box. The techniques described below to anything that is termed a visual component. If you can put it into a form you can probably put it into a grid.

There are no new ideas here, in fact, the basic technique simply mimics what the DBGrid does internally. The idea is to float a control over the grid. Inside DBGrid is a TDBEdit that moves around the grid. It's that TDBEdit that you key you data into. The rest of the unfocused cells are really just pictures. What you will learn here, is how to float any type of visual control/component around the grid.

COMPONENT #1 - TDBLOOKUPCOMBO

You need a form with a DBGrid in it. So start an new project and drop a DBGrid into the main form.

Next drop in a TTable and set it's Alias to DBDEMOS, TableName to GRIDDATA.DB and set the Active property to True. Drop in a DataSource and set it's DataSet property to point to Table1. Go back to the grid and point it's DataSource property to DataSource1. The data from GRIDDATA.DB should appear in your grid..

The first control we are going to put into the grid is a TDBLookupCombo so we need a second table for the lookup. Drop a second TTable into the form. Set it's Alias also to DBDEMOS, TableName to CUSTOMER.DB and Active to True. Drop in a second data source and set its DataSet to Table2.

Now go get a TDBLookupCombo from the Data Controls pallet and drop it any where on the form, it doesn't matter where since it will usually be invisible or floating over the grid. Set the LookuoCombo's properties as follows.

        DataSource      DataSource1
        DataField       CustNo
        LookupSource    DataSource2
        LookupField     CustNo
        LookupDisplay   CustNo  {you can change it to Company later but keep it custno for now)

So far it's been nothing but boring point and click. Now let's do some coding.

The first thing you need to do is make sure that DBLookupCombo you put into the form is invisible when you run the app. So select Form1 into Object Inspector goto the Events tab and double click on the onCreate event. You should now have the shell for the onCreate event displayed on your screen.

procedure TForm1.FormCreate(Sender: TObject);
begin

end;

Set the LookupCombo's visible property to False as follows.

procedure TForm1.FormCreate(Sender: TObject);
begin
  DBLookupCombo1.Visible := False;
end;

Those of you who are paying attention are probably asking why I didn't just set this in the Object Inspector for the component. Actually, you could have. Personally, I like to initialize properties that change at run time in the code. I set static properties that don't change as the program runs in the object inspector. I think it makes the code easier to read.

Now we to be able to move this control around the grid. Specifically we want it to automatically appear as you either cursor or click into the column labeled DBLookupCombo. This involves defining two events for the grid, OnDrawDataCell and OnColExit. First lets do OnDrawDataCell. Double click on the grid's OnDrawDataCell event in the Object Inspector and fill in the code as follows.

procedure TForm1.DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect;
  Field: TField; State: TGridDrawState);
begin
  if (gdFocused in State) then
  begin
     if (Field.FieldName = DBLookupCombo1.DataField) then
     begin
       DBLookupCombo1.Left := Rect.Left + DBGrid1.Left;
       DBLookupCombo1.Top := Rect.Top + DBGrid1.top;
       DBLookupCombo1.Width := Rect.Right - Rect.Left;
      { DBLookupCombo1.Height := Rect.Bottom - Rect.Top; }
       DBLookupCombo1.Visible := True;
     end;
  end;
end;

The reasons for the excessive use begin/end will become clear later in the demo. The code is saying that if the State parameter is gdFocused then this particular cell is the one highlighted in the grid. Further if it's the highlighted cell and the cell has the same field name as the lookup combo's datafield then we need to move the LookupCombo over that cell and make it visible. Notice that the position is determined relative to the form not to just the grid. So, for example, the left side of LookupCombo needs to be the offset of the grid ( DBGrid1.Left) into the form plus the offset of the cell into the grid (Rect.Left).

Also notice that the Height of the LookupCombo has been commented out above. The reason is that the LookupCombo has a minimum height. You just can't make it any smaller. That minimum height is larger than the height of the cell. If you un-commented the height line above. Your code would change it and then Delphi would immediately change it right back. It causes an annoying screen flash so don't fight it. Let the LookupCombo be a little larger than the cell. It looks a little funny but it works.

Now just for fun run the program. Correct all you missing semi-colons etc. Once its running try moving the cursor around the grid. Pretty cool, hu? Not! We're only part of the way there. We need to hide the LookupCombo when we leave the column. So define the grid's onColExit. It should look like this;

procedure TForm1.DBGrid1ColExit(Sender: TObject);
begin
  If DBGrid1.SelectedField.FieldName = DBLookupCombo1.DataField then
    DBLookupCombo1.Visible := false;
end;

This uses the TDBGrids SelectedField property to match up the FieldName associated with the cell with that of the LookupCombo. The code says, "If the cell you are leaving was in the DBLookupCombo column then make it invisible".

Now run it again. Was that worth the effort or what?

Now things look right but we're still missing one thing. Try typing a new customer number into one of the LookupCombo. The problem is that the keystrokes are going to the grid, not to the LookupCombo. To fix this we need to define a onKeyPress event for the grid. It goes like this;

procedure TForm1.DBGrid1KeyPress(Sender: TObject; var Key: Char);
begin
  if (key <> chr(9)) then
  begin
    if (DBGrid1.SelectedField.FieldName = DBLookupCombo1.DataField) then
    begin

      DBLookupCombo1.SetFocus;
      SendMessage(DBLookupCombo1.Handle, WM_Char, word(Key), 0);
    end;
  end;
end;

This code is saying that if the key pressed is not a tab key (Chr(9)) and the current field in the grid is the LookupCombo then set the focus to the LookupCombo and then pass the keystroke over to the LookupCombo. OK so I had to use a WIN API function. You don't really need to know how it works just that it works.

But let me explain a bit anyway. To make Window's SendMessage function work you must give it the handle of the component you want to send the message to. Use the component's Handle property. Next it wants to know what the message is. In this case it's Window's message WM_CHAR which says I'm sending the LookupCombo a character. Finally, you need to tell it which character, so word(Key). That's a typecast to type word of the events Key parameter. Clear as mud, right? All you really need to know is to replace the DBLookupCombo1 in the call to the name of the component your putting into the grid. If you want more info on SendMessage do a search in Delphi's on-line help.

Now run it again and try typing. It works! Play with it a bit and see how the tab key gets you out of "edit mode" back into "move the cell cursor around mode".

Now go back to the Object Inspector for the DBLookupCombo component and change the LookupDIsplay property to Company. Run it. Imagine the possibilities.

COMPONENT #2 - TDBCOMBO

I'm not going to discuss installing the second component, a DBCombo, because I don't really have anything new to say. It's really the same as #1. Here's the incrementally developed code for your review.

procedure TForm1.FormCreate(Sender: TObject);
begin
  DBLookupCombo1.Visible := False;
  DBComboBox1.Visible := False;
end;

procedure TForm1.DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect;
  Field: TField; State: TGridDrawState);
begin
  if (gdFocused in State) then
  begin
     if (Field.FieldName = DBLookupCombo1.DataField) then

     begin
       DBLookupCombo1.Left := Rect.Left + DBGrid1.Left;
       DBLookupCombo1.Top := Rect.Top + DBGrid1.top;
       DBLookupCombo1.Width := Rect.Right - Rect.Left;
       DBLookupCombo1.Visible := True;
     end
     else if (Field.FieldName = DBComboBox1.DataField) then
     begin
       DBComboBox1.Left := Rect.Left + DBGrid1.Left;
       DBComboBox1.Top := Rect.Top + DBGrid1.top;
       DBComboBox1.Width := Rect.Right - Rect.Left;
       DBComboBox1.Visible := True;
     end
  end;
end;

procedure TForm1.DBGrid1ColExit(Sender: TObject);
begin
  If DBGrid1.SelectedField.FieldName = DBLookupCombo1.DataField then
    DBLookupCombo1.Visible := false
  else If DBGrid1.SelectedField.FieldName = DBComboBox1.DataField then
    DBComboBox1.Visible := false;
end;

procedure TForm1.DBGrid1KeyPress(Sender: TObject; var Key: Char);
begin
  if (key <> chr(9)) then
  begin
    if (DBGrid1.SelectedField.FieldName = DBLookupCombo1.DataField) then
    begin
      DBLookupCombo1.SetFocus;
      SendMessage(DBLookupCombo1.Handle, WM_Char, word(Key), 0);
    end
    else if (DBGrid1.SelectedField.FieldName = DBComboBox1.DataField)
then
    begin
      DBComboBox1.SetFocus;
      SendMessage(DBComboBox1.Handle, WM_Char, word(Key), 0);
    end;
  end;
end;

COMPONENT #3 - TDBCHECKBOX

The DBCheckBox gets even more interesting. In this case it seems appropriate to leave something in the non-focused checkbox cells to indicate that there's a check box there. You can either draw the "stay behind" image of the checkbox or you can blast in a picture of the checkbox. I chose to do the latter. I created two BMP files one that's a picture of the box checked (TRUE.BMP) and one that's a picture of the box unchecked (FALSE.BMP). Put two TImage components on the form called ImageTrue and ImageFalse and attach the BMP files to there respective Picture properties. Oh yes you also need to put a DBCheckbox component on the form. Wire it to the CheckBox field in DataSource1 and set the Color property to clWindow. First edit the onCreate so it reads as follows;

procedure TForm1.FormCreate(Sender: TObject);
begin
  DBLookupCombo1.Visible := False;
  DBCheckBox1.Visible := False;
  DBComboBox1.Visible := False;
  ImageTrue.Visible := False;
  ImageFalse.Visible := False;
end;

Now we need to modify the onDrawDataCell to do something with cells that do not have the focus. Here comes the code.

procedure TForm1.DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect;
  Field: TField; State: TGridDrawState);
begin
  if (gdFocused in State) then
  begin
     if (Field.FieldName = DBLookupCombo1.DataField) then
     begin
        ...SEE ABOVE
     end
     else if (Field.FieldName = DBCheckBox1.DataField) then
     begin
       DBCheckBox1.Left := Rect.Left + DBGrid1.Left + 1;
       DBCheckBox1.Top := Rect.Top + DBGrid1.top + 1;
       DBCheckBox1.Width := Rect.Right - Rect.Left{ - 1};
       DBCheckBox1.Height := Rect.Bottom - Rect.Top{ - 1};

       DBCheckBox1.Visible := True;
     end
     else if (Field.FieldName = DBComboBox1.DataField) then
     begin
        ...SEE ABOVE
     end
  end
  else {in this else area draw any stay behind bit maps}
  begin
    if (Field.FieldName = DBCheckBox1.DataField) then
    begin
     if TableGridDataCheckBox.AsBoolean then
       DBGrid1.Canvas.Draw(Rect.Left,Rect.Top,ImageTrue.Picture.Bitmap)
     else
       DBGrid1.Canvas.Draw(Rect.Left,Rect.Top,ImageFalse.Picture.Bitmap)
    end
  end;

It's the very last part we're most interested in. If the state is not gdFocused and the column in CheckBox then this last bit executes. All it does is check the value of the data in the field and if it's true it shows the TRUE.BMP otherwise it shows the FALSE.BMP. I created the bit maps so they are indented so you can tell the difference between a focused and unfocused cell. Make onColExit look like this;

procedure TForm1.DBGrid1ColExit(Sender: TObject);
begin
  If DBGrid1.SelectedField.FieldName = DBLookupCombo1.DataField then
    DBLookupCombo1.Visible := false
  else If DBGrid1.SelectedField.FieldName = DBCheckBox1.DataField then
    DBCheckBox1.Visible := false
  else If DBGrid1.SelectedField.FieldName = DBComboBox1.DataField then
    DBComboBox1.Visible := false;
end;

Edit onKeyPress to;

procedure TForm1.DBGrid1KeyPress(Sender: TObject; var Key: Char);
begin
  if (key <> chr(9)) then
  begin
    if (DBGrid1.SelectedField.FieldName = DBLookupCombo1.DataField) then
    begin
      DBLookupCombo1.SetFocus;
      SendMessage(DBLookupCombo1.Handle, WM_Char, word(Key), 0);
    end
    else if (DBGrid1.SelectedField.FieldName = DBCheckBox1.DataField)
then
    begin
      DBCheckBox1.SetFocus;
      SendMessage(DBCheckBox1.Handle, WM_Char, word(Key), 0);
    end
    else if (DBGrid1.SelectedField.FieldName = DBComboBox1.DataField)
then
    begin
      DBComboBox1.SetFocus;
      SendMessage(DBComboBox1.Handle, WM_Char, word(Key), 0);
    end;
  end;
end;

Finally, here's the last trick. The caption of the checkbox needs to change as the user checks or unchecks the box. My first thought was to do this in the TDBCheckBox's onChange event, the only problem is that it doesn't have one. So I had to go back to the Windows API and send another message. "SendMessage(DBCheckBox1.Handle, BM_GetCheck, 0, 0)" which returns a 0 if the box is unchecked, otherwise it's checked.

procedure TForm1.DBCheckBox1Click(Sender: TObject);
begin
  if SendMessage(DBCheckBox1.Handle, BM_GetCheck, 0, 0) = 0 then
     DBCheckBox1.Caption := '  ' + 'False'
  else
     DBCheckBox1.Caption := '  ' + 'True'
end;

That's it. Hopefully you learned something. I've tried this technique with dialog boxes. It works and it's simple. Have fun with it. You don't really need to completely understand it as long as you know how to edit the code and replace the above component names with with the name of the component you want to drop into the grid.

REVISED - 7/11/95

Fred Dalgleish was nice enough to point out 2 stichy points about the Original grid demo. First, once a component in the grid has the focus it takes 2 Tab presses to move to the next grid cell. The other has to do with adding new records.

Problem # 1 - Two Tab Presses Required.

A component installed in the grid is actually floating over the top of the grid and not part of the grid it self. So when that component has the focus it takes two tab presses to move to the next cell. The first tab moves from the floating component to the Grid cell underneath and the second to move to the next grid cell. If this behavior bugs you heres how to fix it.

First in the form that contains grid add private variable called WasInFloater of type boolean, like so.

type
  TForm1 = class(TForm)
   ...
   ...
  private
    { Private declarations }
     WasInFloater : Boolean;
   ...
   ...
   end;

Next create an onEnter event for the LookupCombo where WasInFloater is set to true. Then point the onEnter event for each component that goes into the grid at this same single onEnter event.

procedure TForm1.DBLookupCombo1Enter(Sender: TObject);
begin
  WasInFloater := True;
end;

Finally, and here's the tricky part, define the following onKeyUp event for the grid.

procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if (Key in [VK_TAB]) and WasInFloater then
  begin
    SendMessage(DBGrid1.Handle, WM_KeyDown, Key, 0);
    WasInFloater := False;
  end;
end;

What's happening here is that the grid's onKeyUp is sending it self a KeyDown when the focus just switched from one of the floating controls. This solution handles both tab and shift-tab.

Problems #2 - New record disappears when component gets focus

The second problem is that if you press add record on the navigator in the demo a new record is added but then when you click on one of the components installed in the grid the new record disappears. The reason for this is that there is a strange grid option called dgCancelOnExit which is True by default. Set it to False and the above problem goes away.

In my opinion Borland should have had this default set to False to begin with . I find it getting in the way all the time and based on forum messages I'm not alone. The option is basically saying that if the grid looses focus then cancel and current edit's! Anyway I've got it turned off in just about every grid I've ever installed.

Note: This was written by Alec Bergamini, at 75664,1224. His companyis Out & About Productions.

Sorting Columns in a DBGrid

Many professional applications will display data in grid fields and allow you to sort on any one of the columns simply by clicking on the column header. Although what is proposed here is not the best way to accomplish this, it is a fairly simple way to mimic the same behavior.

The key hurdle in this problem is the DBGrid itself. It has no OnClick or OnMouseDown events, so it really was not designed to capture this kind of input. It does provide an OnDoubleClick, but this really doesn't work too well. What we need is a way to make the column headers clickable. Enter the THeaderControl component.

THeaderControl is a component that comes in Delphi 2.0 and provides the basic functions that we want. It can detect clicks on its individual panels, and the panels even go up and down when pressed (like a button). The key is to connect the THeaderControl to the DBGrid. Here is how it is done:

First, start a new application. Drop a THeaderControl on the form. It will automatically align to the top edge of the form. Now drop a DBGrid on the form and set its Align property to alClient. Next, add a TTable, and TDataSource component. Set the Tables DatabaseName property to DBDEMOS and its TableName to EVENTS.DB. Set the DataSource's DataSet property to point at Table1 and the DBGrid's DataSource property to point to DataSource1. Set Table's Active property to False in case it has been turned on. Now the fun begins!

Now we need to setup the THeaderControl component to look like the DBGrid's column headers. his will be done in code in the Form's FormCreate method. DoubleClick on Form1's OnCreate event and enter the following code:

procedure TForm1.FormCreate(Sender: TObject);
var
  TheCap : String;
  TheWidth,a : Integer;
begin
  DBGrid1.Options := DBGrid1.Options - [dgTitles];
  HeaderControl1.Sections.Add;
  HeaderControl1.Sections.Items[0].Width := 12;
  Table1.Exclusive := True;
  Table1.Active := True;
  For a := 1 to DBGrid1.Columns.Count do
    begin
      with DBGrid1.Columns.Items[a-1] do
        begin
          TheCap := Title.Caption;
          TheWidth := Width;
        end;
      with HeaderControl1.Sections do
        begin
          Add;
          Items[a].Text := TheCap;
          Items[a].Width := TheWidth+1;
          Items[a].MinWidth := TheWidth+1;
          Items[a].MaxWidth := TheWidth+1;
        end;
      try
        Table1.AddIndex(TheCap,TheCap,[]);
      except
        HeaderControl1.Sections.Items[a].AllowClick := False;
      end;
    end;
  Table1.Active := False;
  Table1.Exclusive := False;
  Table1.Active := True;
end;

Since the THeaderControl will be taking the place of the Grid's column headers, we first remove (set to False) the dgTitles option in the DBGrid's Options property. Then, we add a column to the HeaderControl and set its width to 12. This will be a blank column that is the same width as the Grid's status area on the left.

Next we need to make sure the Table is opened for Exclusive use (no other users can be using it). I will explain why in just a bit.

Now we add the HeaderControl sections. For each one we add, we will be giving it the same text as the caption of that column in the DBGrid. We loop through the DBGrid columns, and for each one we copy over the column's caption and width. We also set the HeaderControl's MinWidth and MaxWidth properties to the same as the column width. This will prevent the column from being resized. If you need resizeable columns, you will need a bit more code, and I wanted to keep this short and sweet.

Now comes the interesting part. We are going to create an index for each column in the DBGrid. The name of the index will be the same as the columns title. This step is in a try..finally structure because there are some fields that cannot be indexed (Blobs & Memos for example). When it tries to index on these fields, it will generate an exception. We catch this exception and turn off the ability to click that column. This means that non-indexed columns will not respond to mouse clicks. The creation of these indexes is why we had to open the table in Exclusive mode. After we are all done, we close the table, set Exclusive off and reopen then table.

One last step. When the HeaderControl is clicked, we need to turn on the correct index for the Table. The HeaderControl's OnSectionClick method should be as follows:

procedure TForm1.HeaderControl1SectionClick(
                HeaderControl: THeaderControl; 
                Section: THeaderSection);
begin
  Table1.IndexName := Section.Text;
end;

That's it! When the column is clicked, the Table's IndexName property is set to the same as the HeaderControl's caption.

Pretty simple, huh? There is a lot of room for improvement however. It would be nice if clicking on a column a second time would reverse the sort order. Also, column resizing would be a nice added touch. I am going to leave these to you folks!

Improvements

The improvement over the previous version is in the usage of the fieldname as indexname instead of the caption.

This improves the flexibility. Changes are indicated as italics

    
procedure TfrmDoc.FormCreate(Sender: TObject);
Var
   TheCap    : String;
   TheFn     : String;
   TheWidth  : Integer;
   a         : Integer;
begin
     Dbgrid1.Options := DBGrid1.Options - [DGTitles];
     Headercontrol1.sections.Add;
     Headercontrol1.Sections.Items[0].Width := 12;
     For a := 1 to DBGRID1.Columns.Count do
     begin
        with DBGrid1.Columns.Items[ a - 1 ] do
        begin
           TheFn    := FieldName;
           TheCap   := Title.Caption;
           TheWidth := Width;
        end;
        With Headercontrol1.Sections DO
        BEGIN
          Add;
          Items[a].Text     := TheCap;
          Items[a].Width    := TheWidth + 1;
          Items[a].MinWidth := TheWidth + 1;
          Items[a].MaxWidth := TheWidth + 1;
        END; (* WITH Headercontrol1.Sections *)
        try (* except *)
           { Use indexes with the same name as the fieldname }
           (DataSource1.Dataset as TTable).IndexName := TheFn;   { Try to set the index name }
        except
           HeaderControl1.Sections.Items[a].AllowClick := False; { Index not Available }
        end; (* EXCEPT *)
     END; (* FOR *)
END; (* PROCEDURE *)

Use the fieldname property of the DBGrid to set an index with the same name as the fieldname.

procedure TfrmDoc.HeaderControl1SectionClick(HeaderControl: THeaderControl;
  Section: THeaderSection);
begin
     (DataSource1.Dataset as TTable).IndexName :=
           DBGrid1.Columns.Items[ Section.Index - 1 ].FieldName;

end;

A Dbgrid with colored cells ?

Ed_P._Hillmann@mail.amsinc.com (Ed Hillmann)

I created a form, dropped a TTable component it, and pointed it to the EMPLOYEE.DB database in the DBDEMOS database. I dropped a Datasource and DBGrid on the form so that it showed on the form.

I thought a simple test would be, for the employee number in the EMPLOYEE.DB table, check if it's an odd number. If it's an odd number, then turn that cell green.

Then, the only code I attached was to the DBGrid's OnDrawColumnCell event, which looks as follows....

procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect:
TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
var
  holdColor: TColor;
begin
  holdColor := DBGrid1.Canvas.Brush.Color; {store the original color}
  if Column.FieldName = 'EmpNo' then {only do for the cell displaying
EmpNo}
    if (Column.Field.AsInteger mod 2 <> 0) then begin
      DBGrid1.Canvas.Brush.Color := clGreen;
      DBGrid1.DefaultDrawColumnCell(Rect, DataCol, Column, State);
      DBGrid1.Canvas.Brush.Color := holdColor;
    end;
end;

DBGrid that shows images

From: sraike@iconz.co.nz (Bill Raike)

Here it is:

{
// DBPICGRD.PAS (C) 1995 W. Raike
//              ALL RIGHTS RESERVED.
//
//    DESCRIPTION:
//      Data-aware grid that can display graphic fields.
//    REVISION HISTORY:
//      15/04/95  Created.    W. Raike
}

unit DBPicGrd;

interface

uses
  DBGrids, DB, DBTables, Grids, WinTypes, Classes, Graphics;

type
  TDBPicGrid = class(TDBGrid)
  protected
    procedure DrawDataCell(const Rect: TRect;
      Field: TField; State: TGridDrawState); override;
  public
    constructor Create(AOwner : TComponent); override;
  published
    property DefaultDrawing default False;
  end;

procedure Register;

implementation

constructor TDBPicGrid.Create(AOwner : TComponent);
begin
  inherited Create(AOwner);
  DefaultDrawing := False;
end;

procedure TDBPicGrid.DrawDataCell(const Rect: TRect; Field: TField; 
State: TGridDrawState);
var
  bmp : TBitmap;
begin
  with Canvas do
  begin
    FillRect(Rect);
    if Field is TGraphicField then
        try
          bmp := TBitmap.Create;
          bmp.Assign(Field);
          Draw(Rect.Left, Rect.Top, bmp);
        finally
          bmp.Free;
        end
    else
      TextOut(Rect.Left, Rect.Top, Field.Text);
  end;
end;

procedure Register;
begin
  RegisterComponents('Custom', [TDBPicGrid]);
end;

end.

Db_QBF.PAS unit allowing Query By Form for DbGrid components in Delphi

From: 'Rick Rutt, Midland, MI' <RRUTT@delphi.com>

The lack of this as a built-in feature makes it harder for Delphi to compete with more resource-intensive tools like Oracle Forms. This unit is not as powerful as the built-in QBF features of Oracle Forms, but it does fill a significant gap in functionality.

Note that the unit is copyrighted, as required by Borland's license, and as desired by me to retain credit for its development. Note that the copyright terms allow free use for any purpose.

unit Db_QBF;  { Database Query By Form -- Version 19950731 }

{ Copyright 1995 by Rick Rutt.
  This work may be used, copied, or distributed by anyone for any purpose,
  provided all copies retain this copyright notice,
  and provided no fee is charged for the contents of this work.
  The author grants permission to anyone to create a derivative work,
  provided each derivative work contains a copyright notice and the notice
  "Portions of this work are based on Db_QBF.PAS as written by Rick Rutt."
}

{ This unit provides a basic but effective Query By Form service
  for database access applications written using Borland Delphi.
  This unit also provides a similar Sort By Form service.

  The Query By Form service displays a modal dialog box with a StringGrid
  of searchable fields, taken from the calling DbGrid.  The user may
  enter an exact search value for any number of fields, and may use
  drag and drop to rearrange the sort order of the fields.
  (Only the fields that contain search values are relevant to the sort.)
  When the user clicks the dialog's OK button, this unit modifies the
  calling DbGrid's IndexFieldNames property, applies a search range
  (of exact values), and refreshes the data.
  If the user leaves all search values empty, this unit clears the
  calling DbGrid's IndexFieldNames property, clears the search range,
  and refreshes the data.

  The Sort By Form service works in a similar manner, except that it
  does not accept search values from the user.  The user drags and drops
  the field sort order, then clicks the OK button.  This unit modifies
  the calling DbGrid's IndexFieldNames property, clears the search range,
  and refreshes the data.
}

{ Create the corresponding dialog form using the New Form action,
  selecting a Standard Dialog Box.  Place a StringGrid on the form
  (as found in the Additional tab of the component toolbar.
  Set the StringGrid's Height to 161 and its Width to 305.
  Finally, replace the new form's .PAS source with this unit.
}

interface

uses WinTypes, WinProcs, Classes, Graphics, Forms, Controls, Buttons,
  StdCtrls, ExtCtrls, Grids, DBGrids;

{ The following two procedures provide the mechanism for accessing
  the services of this unit.

  Have a button or menu item on the calling form call one of these
  procedures, passing the DbGrid as the argument.  (Remember to add
  "uses Db_QBF;" to the calling form's implementation section.)

  Restriction:  The DbGrid must reference a DataSource that, in turn,
  references a DataSet that is a Table.  This unit does not support
  a DataSet that is a Query, since it has no IndexFieldNames property.
}

procedure QueryByForm(grid: TDbGrid);

procedure SortByForm(grid: TDbGrid);

{ The following section is managed by the Delphi environment. }

type
  TdlgQBF = class(TForm)
    OKBtn: TBitBtn;
    CancelBtn: TBitBtn;
    HelpBtn: TBitBtn;
    gridQBF: TStringGrid;
    procedure OKBtnClick(Sender: TObject);
    procedure CancelBtnClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  dlgQBF: TdlgQBF;

implementation

{ The following section is managed by the programmer,
  with assistance from the Delphi environment. }

uses Dialogs, Db, DbTables;

{$R *.DFM}

const
  qbfRowHeight = 16;
  qbfColWidth  = 150;

  qbfFieldLabel = '<<Field>>';
  qbfValueLabel = '<<Value>>';

  qbfQueryCaption = 'Query for Table ';
  qbfSortCaption  = 'Sort Order for Table ';

var
  { Remember some things for use by the QBF dialog's OK button. }
  CallingGrid: TDbGrid;
  CallingMode: (modeQuery, modeSort);

procedure SetupAndShowForm;  { Called by the two exported procedures }
var
  i, j, n: integer;
  tbl: TTable;
  f: TField;
begin
  n := CallingGrid.FieldCount;
  if n <= 0 then begin { Exceptions may be raised instead of showing messages }
    MessageDlg(
        'Db_QBF unit called for a DbGrid without any Fields',
        mtWarning, [mbOK], 0);
  end else if CallingGrid.DataSource = NIL then begin
    MessageDlg(
        'Db_QBF unit called for a DbGrid without a DataSource',
        mtWarning, [mbOK], 0);
  end else if CallingGrid.DataSource.DataSet = NIL then begin
    MessageDlg(
        'Db_QBF unit called for a DbGrid with a DataSource without a DataSet',
        mtWarning, [mbOK], 0);
  end else if not (CallingGrid.DataSource.DataSet is TTable) then begin
    MessageDlg(
        'Db_QBF unit called for a DbGrid with a DataSource that is not a Table',
        mtWarning, [mbOK], 0);
  end else with dlgQBF.gridQBF do begin
    { These properties can also be set once at design time }
    DefaultRowHeight := qbfRowHeight;
    Scrollbars := ssVertical;
    ColCount := 2;  { Even the Sort service needs a dummy second column }

    { These properties must be set at run time }
    RowCount := Succ(n);
    Cells[0,0] := qbfFieldLabel;
    Options := Options + [goRowMoving];

    tbl := TTable(CallingGrid.DataSource.DataSet);

    if CallingMode = modeQuery then begin
      dlgQBF.Caption := qbfQueryCaption + tbl.TableName;
      Cells[1,0] := qbfValueLabel;
      Options := Options + [goEditing];  { Allow user to enter values }
      DefaultColWidth  := qbfColWidth;
    end else begin
      dlgQBF.Caption := qbfSortCaption + tbl.TableName;
      Cells[1,0] := '';  { Dummy "value" column to allow fixed "field" column }
      Options := Options - [goEditing];  { User just reorders the rows }
      DefaultColWidth  := (2 * qbfColWidth);  { Shove aside dummy 2nd column }
    end;

    j := 0;  { Actual number of fields shown to user }
    for i := 1 to n do begin
      f := CallingGrid.Fields[Pred(i)];
      if f.DataType in [ftBlob,ftBytes,ftGraphic,ftMemo,ftUnknown,ftVarBytes]
          then  RowCount := Pred(RowCount)  { Ignore unsearchable fields }
      else begin
        Inc(j);
        Cells[0,j] := f.FieldName;
        Cells[1,j] := '';  { Empty search value }
      end;
    end;

    dlgQBF.HelpBtn.Visible := False;  { We haven't implemented Help }
    dlgQBF.ShowModal;
  end;  { with dlgQBF.gridQBF }
end;

procedure QueryByForm(Grid: TDbGrid);
begin
  CallingGrid := Grid;  { Save for use by OK button }
  CallingMode := modeQuery;
  SetupAndShowForm;
end;

procedure SortByForm(Grid: TDbGrid);
begin
  CallingGrid := Grid;  { Save for use by OK button }
  CallingMode := modeSort;
  SetupAndShowForm;
end;

procedure TdlgQBF.CancelBtnClick(Sender: TObject);
begin
  { Just dismiss the dialog, without making changes to the calling grid. }
  dlgQBF.Hide;
end;

procedure TdlgQBF.OKBtnClick(Sender: TObject);
var
  flds, sep, val: string;
  i, n, nfld: integer;
begin
  flds := '';  { List of fields separated by ';'.}
  sep  := '';  { Becomes ';' after the 1st field is appended. }
  nfld := 0;   { Number of fields in the list. }

  with dlgQBF.gridQBF do begin
    n := Pred(RowCount);
    if n > 0 then for i := 1 to n do begin
      val := Cells[1,i];  { The user-entered search value (if any) }
      if (CallingMode = modeSort)
      or (val <> '') then begin
        flds := flds + sep + Cells[0,i];
        sep  := ';';
        nfld := Succ(nfld);
      end;
    end;

    with CallingGrid.DataSource.DataSet as TTable do begin
      IndexFieldNames := flds;
      if (CallingMode = modeSort)
      or (flds = '') then begin
        CancelRange;
      end else begin
        SetRangeStart;
        for i := 1 to n do begin
          val := Cells[1,i];
          if val <> '' then begin
            FieldByName(Cells[0,i]).AsString := val;
          end;
        end;

        SetRangeEnd;  { Set range end to match range start }
        for i := 1 to n do begin
          val := Cells[1,i];
          if val <> '' then begin
            FieldByName(Cells[0,i]).AsString := val;
          end;
        end;
        ApplyRange;
      end;

      Refresh;
    end;  { with CallingGrid.DataSource.DataSet }
  end;  { with dlgQBF.gridQBF }

  dlgQBF.Hide;
end;

end.

DBGRID saving the user configuration

Is their a way to save the column order of a grid after the user
reorders the columns via drag n drop.

procedure TMainForm.NewIni(const NomeIni: string);
var F: System.Text;
    i: Byte;
begin
  System.Assign(F, NomeIni);
  System.ReWrite(F);
  System.WriteLn(F, '[Campi_Ordine]');
  for i:=1 to Table1.FieldCount do
    System.WriteLn(F, 'Campo',i,'=',Table1.Fields[i-1].FieldName);
  System.WriteLn(F, '');
  System.WriteLn(F, '[Campi_Size]');
  for i:=1 to Table1.FieldCount do
    System.WriteLn(F, 'Campo',i,'=',Table1.Fields[i-1].DisplayWidth);
  System.Close(F);
end;

procedure TMainForm.SaveIni(const FN: String);
var Ini: TIniFile;
    i: Integer;
begin
  NewIni(FN);
  Ini := TIniFile.Create(FN);
  with Ini do
  begin
    for i:=1 to Table1.FieldCount do
    begin
      S:= Table1.Fields[i-1].FieldName;
      WriteString('Campi_Ordine', 'Campo'+IntToStr(i), 
        Table1.Fields[i-1].FieldName);
      WriteInteger('Campi_Size', 'Campo'+IntToStr(i),
        Table1.Fields[i-1].DisplayWidth);
    end;
  end;
  Ini.Free;
end;

procedure TMainForm.LoadIni(const FN: String);
var Ini: TIniFile;
    i: Integer;
    j: Longint;
    S: String;

    function MyReadInteger(const Section, Ident: string): Longint;
    begin
      result := Ini.ReadInteger(Section, Ident, -1);
      if result=-1 then
        raise Exception.Create('Errore nel file di configurazione.');
    end;

    function MyReadString(const Section, Ident: string): String;
    begin
      result := Ini.ReadString(Section, Ident, '');
      if result='' then
        raise Exception.Create('Errore nel file di configurazione.');
    end;

begin
  Ini := TIniFile.Create(FN);
  try
    with Ini do
    begin
      for i:=1 to Table1.FieldCount do
      begin
        S:= MyReadString('Campi_Ordine', 'Campo'+IntToStr(i));
        j:= MyReadInteger('Campi_Size', 'Campo'+IntToStr(i));
        Table1.FieldByName(S).Index := i-1;
        Table1.FieldByName(S).DisplayWidth := j;
      end;
    end;
  finally
    Ini.Free;
  end;
end;

DBGrid resize

I have a form. In that an Edit field, an SQL Query, a DBGrid and a Button.
I can write into the edit, and the Query result will put into the grid.
How can I resize the grid and the form to the fields size which appears in
the grid. The fields Which I select with the query does not fill the full
size of the grid or does not fit into it.

MyTableMyField.DisplayWidth := Length(MyTableMyField.value);

function NewTextWidth(fntFont : TFont; const sString : OpenString) :

  integer;
var
  fntSave : TFont;
begin
  result := 0;
  fntSave := Application.MainForm.Font;
  Application.MainForm.Font := fntFont;
  try
    result := Application.MainForm.Canvas.TextWidth(sString);
  finally
    Application.MainForm.Font := fntSave;
  end;
end;


{ calculate the width of the grid needed to exactly display with no   }
{ horizontal scrollbar and with no extra space between the last       }
{ column and the vertical scrollbar.  The grid's datasource must be   }

{ properly set and the datasource's dataset must be properly set,     }
{ though it need not be open.  Note:  this width includes the width   }
{ of the vertical scrollbar, which changes based on screen            }
{ resolution.  These changes are compensated for.                     }

function iCalcGridWidth
  (
  dbg : TDBGrid { the grid to meaure }
  )
  : integer; { the "exact" width }

const
  cMEASURE_CHAR   = '0';
  iEXTRA_COL_PIX  = 4;
  iINDICATOR_WIDE = 11;

var
  i, iColumns, iColWidth, iTitleWidth, iCharWidth : integer;
begin
  iColumns := 0;
  result := GetSystemMetrics(SM_CXVSCROLL);
  iCharWidth := NewTextWidth(dbg.Font, cMEASURE_CHAR);
  with dbg.dataSource.dataSet do
    for i := 0 to FieldCount - 1 do with Fields[i] do
      if visible then
      begin
        iColWidth := iCharWidth * DisplayWidth;
        if dgTitles in dbg.Options then
        begin
          iTitleWidth := NewTextWidth(dbg.TitleFont, DisplayLabel);

          if iColWidth < iTitleWidth then iColWidth := iTitleWidth;
        end;
        inc(iColumns, 1);
        inc(result, iColWidth + iEXTRA_COL_PIX);
      end;
  if dgIndicator in dbg.Options then
  begin
    inc(iColumns, 1);
    inc(result, iINDICATOR_WIDE);
  end;
  if dgColLines in dbg.Options
    then inc(result, iColumns)
    else inc(result, 1);
end;

Dragging from DbGrid

Has someone achieved dragging things from a DbGrid ?
You can create your own descendant of TDBGrid (or TDBCustomGrid) and
customize it to your needs.

Do an Origami with the code at the end of this message, save it as DBGrid.pas, and install it. You'll have a new component EDBGrid with two new events: OnMouseDown and OnMouseUp. I don't consider it a 'proprietary information': it's a Delphi Bug! Originally, this two events should have been part of the DBGrid component.

unit Dbgrid;

interface

uses
  DBGrids, Controls, Classes;

type
  TEDBGrid = class(TDBGrid)
  private
    FOnMouseDown: TMouseEvent;
    FOnMouseUp: TMouseEvent;
  protected
    procedure MouseDown(Button: TMouseButton; Shift: TShiftState; X, Y:
Integer); override;
    procedure MouseUp(Button: TMouseButton; Shift: TShiftState; X, Y:
Integer); override;
  published
    Property OnMouseDown : TMouseEvent read FOnMouseDown write
FOnMouseDown ;
    Property OnMouseUp : TMouseEvent read FOnMouseUp write FOnMouseUp ;
end;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('Data Controls',[TEDBGrid]);
end;

procedure TEDBGrid.MouseDown(Button: TMouseButton; Shift: TShiftState;
X, Y: Integer);
begin
  if Assigned(FOnMouseDown) then
    FOnMouseDown(Self,Button,Shift,X,Y);
  inherited MouseDown(Button,Shift,X,Y);
end;

procedure TEDBGrid.MouseUp(Button: TMouseButton; Shift: TShiftState; X,
Y: Integer);
begin
  if Assigned(FOnMouseUp) then
    FOnMouseUp(Self,Button,Shift,X,Y);
  inherited MouseUp(Button,Shift,X,Y);
end;

end.