Solved

Adding (none selected) to dblookupcombobox

Posted on 2004-08-26
7
1,318 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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