lookup dropdown in dbgrid
lazarus / 16-Databases

----------------------------------------------------------------------
nelson777 · 2007-04-05 00:10

I want to have a lookup field displayed in a DBGrid with a little
combo for the user to choose the value as I used to do in Delphi. I
found an old post (2005) saying this is not possible yet (at that
time). Is this functional now  ?
the link to the post: http://www.lazarus.freepascal.org/index.php?name
=PNphpBB2&file=viewtopic&t=1325&highlight=lookup+field

In the case this is working, can anyone show me how to do this ? My
lookup is empty.

----------------------------------------------------------------------
nelson777 · 2007-04-20 21:35

Well... after some days of frustration... I did it. Here´s how, in
case someone had the same problem:

I got a very basic personalized ComboBox component I made some years
ago,  that has just another published TStringList property named
"values". So in the combo we have 2 stringlists: Items and Values. I
use these 2 lists to put the primary key field values in the "Values"
property and corresponding text or whatever field in the "Items"
property. As we load the whole table to this combo this method should,
of course, be used only for small tables. Anyway I don´t think anyone
would be so silly to use a lookup field for a 100000 lines table.

So to start we load the data of the secondary table to the combo and
set it´s visible property to false.
Then we create the lookup field in the dataset as usual, and Zeos
components goes as far as initialy showing the right value for the
field in the dbgrid. It´s nice to create some test values in the
database first so we can see it is working as we progress.
After that, we should be able to see the dbgrid with the right values
in the lookup field, but when we try to edit it, we come up with an
empty column.
So now the not-so-tricky part:
Create a TRect variable in your form (better make it private) and name
it anything you like (we´ll use FRect for this example).
Then in the OnDrawColumnCell for your DBGrid put the following code:
Code: [Select]
  if (gdFocused in State) then
    FRect := Rect;

  (Sender as TDBGrid).DefaultDrawColumnCell(Rect, DataCol, Column,
State);


DefaultDrawing for the DBGrid should be true. This will guarantee that
we know the position for the cell we´re editing.
Then in the OnSelectEditor event put the following code:
Code: [Select]
  if (Column.DesignIndex = <whatever column index>) then
  begin
      ComboName.BoundsRect := FRect;
      Editor := ComboName;
  end;


finally in the OnChange event for the ComboBox put the following:
Code: [Select]
  if not(yourTableOrQuery.State in [dsEdit, dsInsert]) then
    yourTableOrQuery.Edit;
   
  yourTableOrQuery.KeyFieldName.AsString :=
comboBox.Values[comboBox.ItemIndex];


if all works well this is all it takes.  I said table or query because
I were using a query with CachedUpdates to make this work.

the code for my combobox component is as simple as this:
Code: [Select]

interface

type

  TValComboBox = class(TComboBox)
  private
    FValues: TStringList;
    FExecutarChange: Boolean;
    FPressBKSP: Boolean;
    FPressDEL: Boolean;
    FHaviaSelessaum: Boolean;
    procedure SetValues(Value: TStringList);
  protected
    procedure KeyDown(var Key: Word; Shift: TShiftState); override;
    procedure Change; override;
    procedure DropDown; override;

  public
    Selecionado: Integer;
    procedure Sort;
    constructor Create(AOwner: TComponent); override;
  published
    property Values: TStringList read FValues write SetValues;
  end;

implementation

{ TValComboBox }

procedure TValComboBox.Change;
var
  Ix,
  SelSt
    : Integer;
begin
  if not FExecutarChange then
    Exit;

  FExecutarChange := False;

  inherited Change;

  try
    if Text = '' then
    begin
      Selecionado := -1;
      Exit;
    end;

    SelSt := Length(Text);
    Ix := 0;
    while Ix <= Items.Count do
      if UpperCase(Copy(Items[Ix], 1, Length(Text))) = UpperCase(Text)
then
        Break
      else
        Inc(Ix);

    if Ix <= Items.Count then
    begin
      Selecionado := Ix;

      if FPressBKSP then
      begin
        if FHaviaSelessaum then
        begin
          Text := Copy(Text, 1, Length(Text) - 1);
          Dec(SelSt);
        end;
      end;

      if FPressDel then
      begin
        if FHaviaSelessaum then
        begin
          SelLength := 0;
          Selecionado := -1;
          Exit;
        end;
      end;
      Text := Items[IX];

      SelStart := SelSt;
      SelLength := Length(Text) - SelSt;
    end
    else
      Selecionado := -1;
  finally
    FExecutarChange := True;
  end;
end;

procedure TValComboBox.SetValues(Value: TStringList);
begin
  Values.Assign(Value);
end;

procedure TValComboBox.KeyDown(var Key: Word; Shift: TShiftState);
begin
  FPressBKSP := Key = VK_BACK;
  FPressDEL  := Key = VK_DELETE;
  FHaviaSelessaum := SelLength > 0;

  inherited KeyDown(Key, Shift);
end;

constructor TValComboBox.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);

  FValues := TStringList.Create;
  FExecutarChange := True;
  Selecionado := -1;
end;

procedure TValComboBox.Sort;
var
  Itms, Vls
    : array of string;
  Ix
    : Integer;

  procedure QuickSort(var A, B: array of string; iLo, iHi: Integer);
  var
    Lo, Hi
      : Integer;
    Mid, T :
      string;
  begin
    Lo := iLo;
    Hi := iHi;
    Mid := A[(Lo + Hi) div 2];
    repeat
      while A[Lo] < Mid do Inc(Lo);
      while A[Hi] > Mid do Dec(Hi);
      if Lo <= Hi then
      begin
        T := A[Lo];
        A[Lo] := A[Hi];
        A[Hi] := T;
        try
          T := B[Lo];
          B[Lo] := B[Hi];
          B[Hi] := T;
        except
        end;
        Inc(Lo);
        Dec(Hi);
      end;
    until Lo > Hi;
    if Hi > iLo then QuickSort(A, B, iLo, Hi);
    if Lo < iHi then QuickSort(A, B, Lo, iHi);
  end;

begin
  SetLength(Itms, Items.Count);
  SetLength(Vls , Items.Count);
  try
    for Ix := Low(Itms) to High(Itms) do
    begin
      Itms[Ix] := Items[Ix];
      try
        Vls[Ix]  := Values[Ix];
      except
      end;
    end;
    QuickSort(Itms, Vls, Low(Itms), High(Itms));
    Items.Clear;
    Values.Clear;
    for Ix := Low(Itms) to High(Itms) do
    begin
      Items.Add(Itms[Ix]);
      try
        Values.Add(Vls[Ix]);
      except
      end;
    end;
  finally
    Finalize(Itms);
    Finalize(Vls);
  end;
end;             

procedure TValComboBox.DropDown;
begin
  FPressBKSP := False;
  FPressBKSP := False;

  inherited DropDown;
end;


Hope it helps someone.

Nelson

----------------------------------------------------------------------
nelson777 · 2007-05-08 16:27

If someone finds a better way, I would really want to know about it.

----------------------------------------------------------------------
nelson777 · 2007-07-03 21:30

I´m having some troubles with the combo, if the user, changes the
line, but doesn´t change the column of the selection. Apparently, Rect
property of the OnDrawColumnCell event is updated only on column
change. Anyone knows how I can force this to be updated on record
change ?

----------------------------------------------------------------------
RudieD · 2007-07-05 17:24

SQLQuery1.AfterScroll event ?

----------------------------------------------------------------------
nelson777 · 2007-07-05 20:27

Yes... but how do I make DBGrid object update cell location
information for the OnDrawCell Event ? Just doing a dbgrid.paint; or
dbgrid.refresh; won´t do.

----------------------------------------------------------------------
jesusr · 2007-07-05 20:32

OnDrawColumnCell it's about to change to make it more delphi
compatible, can you send me your proyect (or one similar) that use the
event?, I will take a look for possible issues. jesusrmx a
yahoo.com.mx

----------------------------------------------------------------------
Chris37 · 2007-07-19 08:53

Hello !

Excuse me for bad English ...

There is simpler !!!

I'm develop a dbgrid with automatic detect lookup field on the dbgrid
dataset !
it's also finish and it's work

Tested for the moment with zeoslib and sqlite3 and Ubuntu 7.04 and
Lazarus 0.9.22

it's without another combobox !!!
Native function lookup to dataset and native picklist to datagrid
column

Contact me on French forum
http://lazforum-fr.tuxfamily.org/forum.php

cordially

Chris

----------------------------------------------------------------------
nelson777 · 2007-07-25 17:03

Well Chris, I tried  but unless my French is so bad that the answer
for

"Combien font deux plus deux " isn't "quatre"

..., I would think that the subscription has some problem. I tried
again with other questions and still couldn't subscribe to the forum.

"Combien font cinq moins deux " -> my answer: "trois"
"Combien font neuf moins sept " -> my answer: "deux"

I'm very interested in your component. Can u send it to me or tell me
where to download ? my personal email is nt777@hotmail.com

Nelson

----------------------------------------------------------------------
Chris37 · 2007-07-25 19:36

Ok Nelson ,

Beta version will update soon on my web site... wait please

Chris

----------------------------------------------------------------------
nelson777 · 2007-07-27 20:47

I don´t want to look intrusive but... can I get the Alfa version ?

----------------------------------------------------------------------
mateus7778 · 2008-04-04 13:29

Hi! Nelson and Chris!

I'm having the same problem, i have to insert data into a dbgrid, and
in some coloumns, i have to use a search object, like listbox, for
example.... Did you have the solution?!?!?
I'm from Brazil... Did you speak portuguese?

Thankx

Mateus ( matsampaio_no_spam_hotmail_dot_com )

----------------------------------------------------------------------
Chris37 · 2008-04-15 18:09

Hi Mateus

Look here :
http://www.espace-malin.fr/download/LZComponents/stable/

There are a new dbgrid

Regards
chris

----------------------------------------------------------------------
alexs · 2008-04-18 05:43

try use rxDBGrid from Lazarus CCR
https://lazarus-ccr.svn.sourceforge.net/svnroot/lazarus-
ccr/components/rx

----------------------------------------------------------------------
rkamarowski · 2008-06-12 13:31

alexs,

How do I get the rxDBGrid into the IDE, so I can select it for my
project?

bob k.