Solved

Adding (none selected) to dblookupcombobox

Posted on 2004-08-26
7
1,308 Views
Last Modified: 2010-05-18
How can I add the value (none selected) to a dblookupcombobox dropdownlist?

When user selects this value the table's fieldvalue must be set to 0 or cleared. So I guess selecting it will result in a combobox indexvalue of -1.

Please supply some working code.
Thanks. The Wiz,

PS: I'm using Developer Express 5.13 components, so if you know how to use it with those components I would be very greatfull. Otherwise I will figure that part out myself.
0
Comment
Question by:Delphiwizard
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11910276
usual you could do this in the LookupDataset itself like
(should be a query then)

Select KeyfieldName, ResultFieldName from LookupTableName
Union all  
Select 0, 'none selected' from LookupTableName

hope this helps you

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11910287
btw. if you want this entry also direct after editing a new record
you could use

Select KeyfieldName, ResultFieldName from LookupTableName
Union all  
Select NULL, 'none selected' from LookupTableName

instead

meikl ;-)
0
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 500 total points
ID: 11911334
Let's say for arguments sake you have a client master data table with 2 fields
1. ClientCode
2. ClientName

You could Add a record for your purpose such as
1. ClientCode = 0
2. ClientName = "None Selected"

This will then also appear as an option in the lookup list box.

You can then set the NullValueKey property of the lookup control to "Del" so that if the user presses the delete key when on the control, it would automatically select the None Selected option.

I have put together a demo for you using the clients table from the DBDemos Database that comes with Delphi. Once you start the app, simply add a record in the grid and set the FirstName field to "None selected" and the ACCT_NBR field to 0.

unit main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBClient, Grids, DBGrids, DBTables, DBCtrls, StdCtrls;

type
  TForm1 = class(TForm)
    DBLookupComboBox1: TDBLookupComboBox;
    DataSource1: TDataSource;
    Table1: TTable;
    DBGrid1: TDBGrid;
    ClientDataSet1: TClientDataSet;
    DataSource2: TDataSource;
    ClientDataSet1AccNum: TFloatField;
    DBText1: TDBText;
    Label1: TLabel;
    procedure ClientDataSet1AccNumGetText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure FormCreate(Sender: TObject);
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ClientDataSet1AccNumGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  if Sender.AsInteger = 0
    then Text:= 'None selected.'
    else Text:= Sender.AsString;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  ClientDataSet1.Edit;
  ClientDataSet1.FieldByName('AccNum').AsInteger:= 0;
  ClientDataSet1.Post;
end;

end.
object Form1: TForm1
  Left = 191
  Top = 115
  Width = 366
  Height = 298
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBText1: TDBText
    Left = 168
    Top = 24
    Width = 3
    Height = 13
    AutoSize = True
    DataField = 'AccNum'
    DataSource = DataSource2
  end
  object Label1: TLabel
    Left = 168
    Top = 8
    Width = 81
    Height = 13
    Caption = 'Keyfield value'
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clWindowText
    Font.Height = -11
    Font.Name = 'MS Sans Serif'
    Font.Style = [fsBold]
    ParentFont = False
  end
  object DBLookupComboBox1: TDBLookupComboBox
    Left = 16
    Top = 24
    Width = 145
    Height = 21
    DataField = 'AccNum'
    DataSource = DataSource2
    KeyField = 'ACCT_NBR'
    ListField = 'FIRST_NAME'
    ListSource = DataSource1
    NullValueKey = 46
    TabOrder = 0
  end
  object DBGrid1: TDBGrid
    Left = 16
    Top = 56
    Width = 320
    Height = 185
    DataSource = DataSource1
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DataSource1: TDataSource
    DataSet = Table1
    Left = 96
    Top = 152
  end
  object Table1: TTable
    Active = True
    DatabaseName = 'DBDEMOS'
    TableName = 'clients.dbf'
    Left = 136
    Top = 152
  end
  object ClientDataSet1: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    Left = 312
    Top = 8
    Data = {
      290000009619E0BD010000001800000001000000000003000000290006416363
      4E756D08000400000000000000}
    object ClientDataSet1AccNum: TFloatField
      FieldName = 'AccNum'
      OnGetText = ClientDataSet1AccNumGetText
    end
  end
  object DataSource2: TDataSource
    DataSet = ClientDataSet1
    Left = 280
    Top = 8
  end
end

Kind regards
Pierre Cornelius

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 11911661
You could also derive your own ComboBox control to do this e.g.

  TMyDBComboBox = class(TDBComboBox)
    FLookupSource: TDataSource;
    FLookupField: TField;
    FKeyField: TField;
    FValues: TStringList;
  private
    procedure GetLookupList;
    procedure SetLookupSource(AValue: TDataSource);
    procedure SetKeyField(AValue: TField);
    procedure SetLookupField(AValue: TField);
    procedure ItemSelected(Sender: TObject);
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
  published
    property LookupSource: TDataSource read FLookupSource write SetLookupSource;
    property LookupField: TField read FLookupField write SetLookupField;
    property KeyField: TField read FKeyField write SetKeyField;
  end;

{ TMyDBComboBox }

constructor TMyDBComboBox.Create(AOwner: TComponent);
begin
  inherited;
  FLookupSource:= TDataSource.Create(self);
  FLookupField:= TField.Create(FLookupSource.DataSet);
  FKeyField:= TField.Create(FLookupSource.DataSet);
  FValues:= TStringList.Create;
  OnSelect:= ItemSelected;
  GetLookupList;
end;

destructor TMyDBComboBox.Destroy;
begin
  FValues.Free;
  inherited;
end;

procedure TMyDBComboBox.GetLookupList;
begin
  if (FlookupSource.DataSet <> nil)
      AND (FLookupField.FieldName <> '')
      AND (FKeyField.FieldName <> '') then
  with FLookupSource do
  begin
    if DataSet.Active then
    begin
      Items.Clear;
      FValues.Clear;
      DataSet.First;
      Items.Add('None selected');
      FValues.Add('0');
      While not DataSet.Eof do
      begin
        Items.Add(DataSet.FieldByName(FLookupField.FieldName).AsString);
        FValues.Add(DataSet.FieldByName(FKeyField.FieldName).Value);
        DataSet.Next;
      end;
    end;
  end;
end;

procedure TMyDBComboBox.ItemSelected(Sender: TObject);
begin
  try
    DataSource.DataSet.FieldByName(DataField).Value:= FValues[self.ItemIndex];
  except
  end;
end;

procedure TMyDBComboBox.SetKeyField(AValue: TField);
begin
  if AValue <> FKeyField then
  begin
    FKeyField:= AValue;
    GetLookupList;
  end;
end;

procedure TMyDBComboBox.SetLookupField(AValue: TField);
begin
  if AValue <> FLookupField then
  begin
    FLookupField:= AValue;
    GetLookupList;
  end;
end;

procedure TMyDBComboBox.SetLookupSource(AValue: TDataSource);
begin
  if AValue <> FLookupSource then
  begin
    FLookupSource:= AValue;
    GetLookupList;
  end;
end;


NOTE:
The above is very basic and serves just as illustration.
For example, you could add a event handler in case of the dataset changing to call the GetLookupList again.
The point I'm trying to make is that you can customise it in just about any way you want depending on the amount of time you want to spend on it.

Kind regards
Pierre
0
 

Author Comment

by:Delphiwizard
ID: 11923941
Hi Pierre,

You mention this in your first example.

  object ClientDataSet1: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    Left = 312
    Top = 8
    Data = {
      290000009619E0BD010000001800000001000000000003000000290006416363
      4E756D08000400000000000000}
    object ClientDataSet1AccNum: TFloatField
      FieldName = 'AccNum'
      OnGetText = ClientDataSet1AccNumGetText
    end
  end

How do I get this Datapacked into my project? Where does it come from, it's not a property of the ClientDataSet?
0
 

Author Comment

by:Delphiwizard
ID: 11924012
And second, Pierre:

How to implement the TMyComboBox (without adding it as a new component to my Delphi Ide).

Thanks Stef
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 11929217
Your first question:
===========

I defined the 2 fields at design time and right-clicked on the ClientDataSet and selected "create dataset". That's where it comes from. To get it to work simply do the following:
1. Create a new project
2. View the form
3. press Alt + F12
4. copy and paste the following into the .dfm file:

object Form1: TForm1
  Left = 191
  Top = 115
  Width = 366
  Height = 298
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBText1: TDBText
    Left = 168
    Top = 24
    Width = 3
    Height = 13
    AutoSize = True
    DataField = 'AccNum'
    DataSource = DataSource2
  end
  object Label1: TLabel
    Left = 168
    Top = 8
    Width = 81
    Height = 13
    Caption = 'Keyfield value'
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clWindowText
    Font.Height = -11
    Font.Name = 'MS Sans Serif'
    Font.Style = [fsBold]
    ParentFont = False
  end
  object DBLookupComboBox1: TDBLookupComboBox
    Left = 16
    Top = 24
    Width = 145
    Height = 21
    DataField = 'AccNum'
    DataSource = DataSource2
    KeyField = 'ACCT_NBR'
    ListField = 'FIRST_NAME'
    ListSource = DataSource1
    NullValueKey = 46
    TabOrder = 0
  end
  object DBGrid1: TDBGrid
    Left = 16
    Top = 56
    Width = 320
    Height = 185
    DataSource = DataSource1
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DataSource1: TDataSource
    DataSet = Table1
    Left = 96
    Top = 152
  end
  object Table1: TTable
    Active = True
    DatabaseName = 'DBDEMOS'
    TableName = 'clients.dbf'
    Left = 136
    Top = 152
  end
  object ClientDataSet1: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    Left = 312
    Top = 8
    Data = {
      290000009619E0BD010000001800000001000000000003000000290006416363
      4E756D08000400000000000000}
    object ClientDataSet1AccNum: TFloatField
      FieldName = 'AccNum'
      OnGetText = ClientDataSet1AccNumGetText
    end
  end
  object DataSource2: TDataSource
    DataSet = ClientDataSet1
    Left = 280
    Top = 8
  end
end

5. Press ALT + F12 again to return to form view


Your second question:
==============

Add the following to the FormCreate event:

  with TMyDBComboBox.Create(self) do
  begin
    parent:= self;
    Left:= 16;
    Top:= 248;
    DataSource:= DataSource2;
    DataField:= 'AccNum';
    LookupSource:= DataSource1;
    KeyField   := Table1.FieldByName('ACCT_NBR');
    LookupField:= Table1.FieldByName('Address_1');
  end;


Let me know if you have any more hassles...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now