Recent

Author Topic: lookup dropdown in dbgrid  (Read 25835 times)

nelson777

  • Jr. Member
  • **
  • Posts: 68
    • http://www.aocontrario.com
lookup dropdown in dbgrid
« on: April 05, 2007, 02:10:48 am »
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

  • Jr. Member
  • **
  • Posts: 68
    • http://www.aocontrario.com
lookup dropdown in dbgrid
« Reply #1 on: April 20, 2007, 11:35:45 pm »
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

  • Jr. Member
  • **
  • Posts: 68
    • http://www.aocontrario.com
lookup dropdown in dbgrid
« Reply #2 on: May 08, 2007, 06:27:19 pm »
If someone finds a better way, I would really want to know about it.

nelson777

  • Jr. Member
  • **
  • Posts: 68
    • http://www.aocontrario.com
lookup dropdown in dbgrid
« Reply #3 on: July 03, 2007, 11:30:43 pm »
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

  • Full Member
  • ***
  • Posts: 234
lookup dropdown in dbgrid
« Reply #4 on: July 05, 2007, 07:24:54 pm »
SQLQuery1.AfterScroll event ?
The FRED Trainer. (Training FRED with Lazarus/FPC)

nelson777

  • Jr. Member
  • **
  • Posts: 68
    • http://www.aocontrario.com
lookup dropdown in dbgrid
« Reply #5 on: July 05, 2007, 10:27:17 pm »
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

  • Sr. Member
  • ****
  • Posts: 470
lookup dropdown in dbgrid
« Reply #6 on: July 05, 2007, 10:32:19 pm »
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

  • New Member
  • *
  • Posts: 15
    • http://lazarus-fr.espace-malin.fr/
lookup dropdown in dbgrid
« Reply #7 on: July 19, 2007, 10:53:20 am »
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

  • Jr. Member
  • **
  • Posts: 68
    • http://www.aocontrario.com
lookup dropdown in dbgrid
« Reply #8 on: July 25, 2007, 07:03:35 pm »
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

  • New Member
  • *
  • Posts: 15
    • http://lazarus-fr.espace-malin.fr/
lookup dropdown in dbgrid
« Reply #9 on: July 25, 2007, 09:36:12 pm »
Ok Nelson ,

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

Chris

nelson777

  • Jr. Member
  • **
  • Posts: 68
    • http://www.aocontrario.com
lookup dropdown in dbgrid
« Reply #10 on: July 27, 2007, 10:47:01 pm »
I don´t want to look intrusive but... can I get the Alfa version ? :)

mateus7778

  • New Member
  • *
  • Posts: 14
lookup dropdown in dbgrid
« Reply #11 on: April 04, 2008, 03:29:48 pm »
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

  • New Member
  • *
  • Posts: 15
    • http://lazarus-fr.espace-malin.fr/
lookup dropdown in dbgrid
« Reply #12 on: April 15, 2008, 08:09:57 pm »
Hi Mateus

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

There are a new dbgrid

Regards
chris

alexs

  • New Member
  • *
  • Posts: 19
    • http://alexs75.narod.ru/
lookup dropdown in dbgrid
« Reply #13 on: April 18, 2008, 07:43:51 am »

rkamarowski

  • New Member
  • *
  • Posts: 40
lookup dropdown in dbgrid
« Reply #14 on: June 12, 2008, 03:31:31 pm »
alexs,

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

bob k.

 

TinyPortal © 2005-2018