Difference between revisions of "TDBLookupComboBox"

From Free Pascal wiki
Jump to navigationJump to search
m (→‎Unbound use: Minor change to code example and improved comments now I better understand unbound DBLookupCombo)
m (Fixed syntax highlighting)
 
(12 intermediate revisions by 5 users not shown)
Line 1: Line 1:
{{Translate}}
+
{{TDBLookupComboBox}}
  
==Definition==
+
'''TDBLookupComboBox''' [[image:tdblookupcombobox.png]] is a (doubly) data-bound [[TComboBox]]. A TDBLookupComboBox can be found on the [[Data Controls tab]] of the [[Component Palette]].
Unit: Lazarus '''DbCtrls'''
 
  
Official documentation: [http://lazarus-ccr.sourceforge.net/docs/lcl/dbctrls/tdblookupcombobox.html TDBLookupComboBox]
+
The TDBLookupCombobox control gets a list of values from its ''ListSource'' (which e.g. represents a table with product info, "Products"). It then:
  
==Description==
 
A (doubly) data-bound combobox. The '''TDBLookupCombobox''' control gets a list of values from its '''ListSource''' (which e.g. represents a table with product info, "Products"). It then
 
 
* displays the values in the '''ListField''' (e.g. a "ProductName" field) while
 
* displays the values in the '''ListField''' (e.g. a "ProductName" field) while
 
* remembering the values in the '''KeyField''' (e.g. an "ID" field)  
 
* remembering the values in the '''KeyField''' (e.g. an "ID" field)  
 +
 
The combobox stores the result (i.e. the '''KeyField''' value in the '''DataField''', e.g. the "ProductID" field in the '''DataSource''' property (e.g. an "Orders" table).
 
The combobox stores the result (i.e. the '''KeyField''' value in the '''DataField''', e.g. the "ProductID" field in the '''DataSource''' property (e.g. an "Orders" table).
  
The difference with the [http://lazarus-ccr.sourceforge.net/docs/lcl/dbctrls/tdbcombobox.html TDBComboBox] is that the DBComboBox is a (as it were) "singly data bound control": it stores the results in a database fields but the list of values to select from is supplied by code/via the Object Inspector.
+
The difference with the [[TDBComboBox]] is that the TDBComboBox is a (as it were) "singly data bound control": it stores the results in a database field but the list of values to select from is supplied by code/via the [[Object Inspector]].
  
 
==Unbound use==
 
==Unbound use==
You can get the combobox to look up values from one table without storing the results in another by leaving the '''DataSource''' and the '''KeyField'''(1) properties empty.<br/>
+
 
Notes<br/>
+
You can get the combobox to look up values from one table without storing the results in another by leaving the ''DataSource'' and the ''KeyField'' properties empty.
(1)  I cannot get unbound mode to display any text without setting '''KeyField''' (see below) - Mike Thompson 05 June 2014<br/>
+
 
(2)  If '''DBLookupCombo.ScrollListDataset''' is set, then the component will only scroll the dataset when the user interacts with it.<br/>
 
Setting '''DBLookupCombo.KeyValue''' in code will '''not''' cause the DBLookupCombo to scroll the dataset.
 
<br/>
 
<br/>
 
 
The following sample code snippet will:
 
The following sample code snippet will:
* Allow a DBLookupCombo box to be configured in unbound mode (i.e. no changes made to any table in the database)
+
 
* Ensure the visible data in the DBLookupCombo is from the selected record in the dataset (i.e. choosing a new value in DBLookupComboBox contents will scroll the dataset to the correct record)
+
* Allow a TDBLookupCombo box to be configured in unbound mode (i.e. no changes made to any table in the database)
* Set the initial displayed value in the DBLookupCombo and the selected record in the datset to previously remembered values.
+
* Ensure the visible data in the TDBLookupComboBox is from the selected record in the dataset (i.e. choosing a new value in TDBLookupComboBox contents will scroll the dataset to the correct record)
 +
* Set the initial displayed value in the TDBLookupComboBox and the selected record in the datset to previously remembered values.
  
 
The following sample code snipper will NOT:
 
The following sample code snipper will NOT:
* Automatically update the DBLookupComboBox value if the dataset is scrolled (you need to add your own handler to Dataset.OnScroll to achieve this)
 
  
<syntaxhighlight>
+
* Automatically update the TDBLookupComboBox value if the dataset is scrolled (you need to add your own handler to Dataset.OnScroll to achieve this)
Interface
+
 
   Type
+
<syntaxhighlight lang=pascal>
     TForm1 = Class(TForm)
+
interface
 +
   type
 +
     TForm1 = class(TForm)
 
       cboLookup: TDBLookupComboBox;
 
       cboLookup: TDBLookupComboBox;
 
       dsSource: TDatasource;
 
       dsSource: TDatasource;
 
       dsetSource: TDataset;
 
       dsetSource: TDataset;
 
       ...
 
       ...
     Protected
+
     protected
       FDisplayField: String;
+
       FDisplayField: string;
       FKeyField: String;
+
       FKeyField: string;
       FPreviousKeyValue: String;  // May be any datatype - see below
+
       FPreviousKeyValue: string;  // May be any datatype - see below
 
     ...
 
     ...
     End;
+
     end;
  
 
...
 
...
  
Implementation
+
implementation
  
 
...
 
...
 
   // Remember previous value
 
   // Remember previous value
   FPreviousValue := load_from_settings_in_your_preferred_way_;
+
   FPreviousKeyValue:= load_from_settings_in_your_preferred_way_;
  
 
...
 
...
Line 73: Line 69:
  
 
   // Remember a previous value
 
   // Remember a previous value
   If FPreviousKeyValue <> '' Then
+
   if FPreviousKeyValue <> '' then
   Begin
+
   begin
 
     dsetSource.Locate(FKeyField, FPreviousKeyValue, []);  // Note: This will fail to correctly set the ComboBox,  
 
     dsetSource.Locate(FKeyField, FPreviousKeyValue, []);  // Note: This will fail to correctly set the ComboBox,  
 
                                                           // But will set the Datatset to the correct record.
 
                                                           // But will set the Datatset to the correct record.
Line 82: Line 78:
 
     If (cboLookup.KeyValue = Null) And (dsetSource.RecordCount > 0) Then
 
     If (cboLookup.KeyValue = Null) And (dsetSource.RecordCount > 0) Then
 
       cboLookup.KeyValue := dsetSource.FieldByName(FKeyField).AsVariant;
 
       cboLookup.KeyValue := dsetSource.FieldByName(FKeyField).AsVariant;
   End;
+
   end;
  
 
...  
 
...  
 
   // Save current value for next time
 
   // Save current value for next time
   FPreviousValue := cboLookup.KeyValue;
+
   FPreviousKeyValue:= cboLookup.KeyValue;
 
   write_to_settings_in_your_preferred_way_(FPreviousValue);
 
   write_to_settings_in_your_preferred_way_(FPreviousValue);
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
==Bugs==
 
==Bugs==
At least for DBLookupComboBox, there is a bug with FPC 2.6.0 (used in Lazarus 1.0, 1.0.2 etc) that requires the '''listfield''' to be present in the datasource as well.
+
 
 +
At least for TDBLookupComboBox, there is a bug with FPC 2.6.0 (used in Lazarus 1.0, 1.0.2 etc) that requires the '''listfield''' to be present in the datasource as well.
  
 
Workaround: you can bypass this by declaring a calculated field with the same name as the listfield in the datasource's dataset that does nothing.  
 
Workaround: you can bypass this by declaring a calculated field with the same name as the listfield in the datasource's dataset that does nothing.  
Line 97: Line 94:
 
==Alternative control==
 
==Alternative control==
 
The Rx controls (in the RxNew package) have the RxDBLookupCombobox which has some extra functionality; it e.g. allows to display multiple fields/columns next to each other (much like MS Access comboboxes):
 
The Rx controls (in the RxNew package) have the RxDBLookupCombobox which has some extra functionality; it e.g. allows to display multiple fields/columns next to each other (much like MS Access comboboxes):
<syntaxhighlight>
+
 
 +
<syntaxhighlight lang=pascal>
 
RxDBLookupCombo.LookupDisplay = 'field1;field2'; //takes a semicolon-delimited list of fields
 
RxDBLookupCombo.LookupDisplay = 'field1;field2'; //takes a semicolon-delimited list of fields
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 
It additionally has a property DisplayAllFields.
 
It additionally has a property DisplayAllFields.
  
[[category:Lazarus]]
+
==See also==
[[category:dbctrls]]
+
 
[[Category:Databases]]
+
* [[doc:lcl/dbctrls/tdblookupcombobox.html|TDBLookupComboBox doc]]
 +
* [[TComboBox]]
 +
* [[TDBComboBox]]
 +
 
 +
{{LCL Components}}

Latest revision as of 05:28, 29 February 2020

English (en) français (fr)

TDBLookupComboBox tdblookupcombobox.png is a (doubly) data-bound TComboBox. A TDBLookupComboBox can be found on the Data Controls tab of the Component Palette.

The TDBLookupCombobox control gets a list of values from its ListSource (which e.g. represents a table with product info, "Products"). It then:

  • displays the values in the ListField (e.g. a "ProductName" field) while
  • remembering the values in the KeyField (e.g. an "ID" field)

The combobox stores the result (i.e. the KeyField value in the DataField, e.g. the "ProductID" field in the DataSource property (e.g. an "Orders" table).

The difference with the TDBComboBox is that the TDBComboBox is a (as it were) "singly data bound control": it stores the results in a database field but the list of values to select from is supplied by code/via the Object Inspector.

Unbound use

You can get the combobox to look up values from one table without storing the results in another by leaving the DataSource and the KeyField properties empty.

The following sample code snippet will:

  • Allow a TDBLookupCombo box to be configured in unbound mode (i.e. no changes made to any table in the database)
  • Ensure the visible data in the TDBLookupComboBox is from the selected record in the dataset (i.e. choosing a new value in TDBLookupComboBox contents will scroll the dataset to the correct record)
  • Set the initial displayed value in the TDBLookupComboBox and the selected record in the datset to previously remembered values.

The following sample code snipper will NOT:

  • Automatically update the TDBLookupComboBox value if the dataset is scrolled (you need to add your own handler to Dataset.OnScroll to achieve this)
interface
  type
    TForm1 = class(TForm)
      cboLookup: TDBLookupComboBox;
      dsSource: TDatasource;
      dsetSource: TDataset;
      ...
    protected
      FDisplayField: string;
      FKeyField: string;
      FPreviousKeyValue: string;  // May be any datatype - see below
    ...
    end;

...

implementation

...
  // Remember previous value
  FPreviousKeyValue:= load_from_settings_in_your_preferred_way_;

...
  // Configure DBLookupCombo
  dsSource.Dataset := dsetSource;
  cboLookup.ListSource := dsSource;

  cboLookup.ScrollListDataset := True;   // This ensures that changing the ComboBox will automatically scroll
                                         // the dataset
  cboLookup.Style := csDropDownList;

  cboLookup.KeyField := FKeyField;       
  cboLookup.ListField := FDisplayField;  // This is the field that will appear in the contents of the ComboBox
  
  dsetSource.Open;  // The ComboBox should now be populated, however intially no text is displayed
                    // So right now there is no relationship between the selected record in the dataset
                    // and the contents of the ComboBox

  // cboLookup.KeyValue reads and writes a Variant, so you may actually define
  // FPreviousKeyValue as any data type you feel is appropriate.

  // Remember a previous value
  if FPreviousKeyValue <> '' then
  begin
    dsetSource.Locate(FKeyField, FPreviousKeyValue, []);  // Note: This will fail to correctly set the ComboBox, 
                                                          // But will set the Datatset to the correct record.
 
    // Now the dataset is on the correct record, lets ensure the DBLookupCombo
    // is showing the same value as the selected record
    If (cboLookup.KeyValue = Null) And (dsetSource.RecordCount > 0) Then
      cboLookup.KeyValue := dsetSource.FieldByName(FKeyField).AsVariant;
  end;

... 
  // Save current value for next time
  FPreviousKeyValue:= cboLookup.KeyValue;
  write_to_settings_in_your_preferred_way_(FPreviousValue);

Bugs

At least for TDBLookupComboBox, there is a bug with FPC 2.6.0 (used in Lazarus 1.0, 1.0.2 etc) that requires the listfield to be present in the datasource as well.

Workaround: you can bypass this by declaring a calculated field with the same name as the listfield in the datasource's dataset that does nothing.

Alternative control

The Rx controls (in the RxNew package) have the RxDBLookupCombobox which has some extra functionality; it e.g. allows to display multiple fields/columns next to each other (much like MS Access comboboxes):

RxDBLookupCombo.LookupDisplay = 'field1;field2'; //takes a semicolon-delimited list of fields

It additionally has a property DisplayAllFields.

See also


LCL Components
Component Tab Components
Standard TMainMenu • TPopupMenu • TButton • TLabel • TEdit • TMemo • TToggleBox • TCheckBox • TRadioButton • TListBox • TComboBox • TScrollBar • TGroupBox • TRadioGroup • TCheckGroup • TPanel • TFrame • TActionList
Additional TBitBtn • TSpeedButton • TStaticText • TImage • TShape • TBevel • TPaintBox • TNotebook • TLabeledEdit • TSplitter • TTrayIcon • TControlBar • TFlowPanel • TMaskEdit • TCheckListBox • TScrollBox • TApplicationProperties • TStringGrid • TDrawGrid • TPairSplitter • TColorBox • TColorListBox • TValueListEditor
Common Controls TTrackBar • TProgressBar • TTreeView • TListView • TStatusBar • TToolBar • TCoolBar • TUpDown • TPageControl • TTabControl • THeaderControl • TImageList • TPopupNotifier • TDateTimePicker
Dialogs TOpenDialog • TSaveDialog • TSelectDirectoryDialog • TColorDialog • TFontDialog • TFindDialog • TReplaceDialog • TTaskDialog • TOpenPictureDialog • TSavePictureDialog • TCalendarDialog • TCalculatorDialog • TPrinterSetupDialog • TPrintDialog • TPageSetupDialog
Data Controls TDBNavigator • TDBText • TDBEdit • TDBMemo • TDBImage • TDBListBox • TDBLookupListBox • TDBComboBox • TDBLookupComboBox • TDBCheckBox • TDBRadioGroup • TDBCalendar • TDBGroupBox • TDBGrid • TDBDateTimePicker
Data Access TDataSource • TCSVDataSet • TSdfDataSet • TBufDataset • TFixedFormatDataSet • TDbf • TMemDataset
System TTimer • TIdleTimer • TLazComponentQueue • THTMLHelpDatabase • THTMLBrowserHelpViewer • TAsyncProcess • TProcessUTF8 • TProcess • TSimpleIPCClient • TSimpleIPCServer • TXMLConfig • TEventLog • TServiceManager • TCHMHelpDatabase • TLHelpConnector
Misc TColorButton • TSpinEdit • TFloatSpinEdit • TArrow • TCalendar • TEditButton • TFileNameEdit • TDirectoryEdit • TDateEdit • TTimeEdit • TCalcEdit • TFileListBox • TFilterComboBox • TComboBoxEx • TCheckComboBox • TButtonPanel • TShellTreeView • TShellListView • TXMLPropStorage • TINIPropStorage • TJSONPropStorage • TIDEDialogLayoutStorage • TMRUManager • TStrHolder
LazControls TCheckBoxThemed • TDividerBevel • TExtendedNotebook • TListFilterEdit • TListViewFilterEdit • TLvlGraphControl • TShortPathEdit • TSpinEditEx • TFloatSpinEditEx • TTreeFilterEdit • TExtendedTabControl •
RTTI TTIEdit • TTIComboBox • TTIButton • TTICheckBox • TTILabel • TTIGroupBox • TTIRadioGroup • TTICheckGroup • TTICheckListBox • TTIListBox • TTIMemo • TTICalendar • TTIImage • TTIFloatSpinEdit • TTISpinEdit • TTITrackBar • TTIProgressBar • TTIMaskEdit • TTIColorButton • TMultiPropertyLink • TTIPropertyGrid • TTIGrid
SQLdb TSQLQuery • TSQLTransaction • TSQLScript • TSQLConnector • TMSSQLConnection • TSybaseConnection • TPQConnection • TPQTEventMonitor • TOracleConnection • TODBCConnection • TMySQL40Connection • TMySQL41Connection • TMySQL50Connection • TMySQL51Connection • TMySQL55Connection • TMySQL56Connection • TMySQL57Connection • TSQLite3Connection • TIBConnection • TFBAdmin • TFBEventMonitor • TSQLDBLibraryLoader
Pascal Script TPSScript • TPSScriptDebugger • TPSDllPlugin • TPSImport_Classes • TPSImport_DateUtils • TPSImport_ComObj • TPSImport_DB • TPSImport_Forms • TPSImport_Controls • TPSImport_StdCtrls • TPSCustomPlugin
SynEdit TSynEdit • TSynCompletion • TSynAutoComplete • TSynMacroRecorder • TSynExporterHTML • TSynPluginSyncroEdit • TSynPasSyn • TSynFreePascalSyn • TSynCppSyn • TSynJavaSyn • TSynPerlSyn • TSynHTMLSyn • TSynXMLSyn • TSynLFMSyn • TSynDiffSyn • TSynUNIXShellScriptSyn • TSynCssSyn • TSynPHPSyn • TSynTeXSyn • TSynSQLSyn • TSynPythonSyn • TSynVBSyn • TSynAnySyn • TSynMultiSyn • TSynBatSyn • TSynIniSyn • TSynPoSyn
Chart TChart • TListChartSource • TRandomChartSource • TUserDefinedChartSource • TCalculatedChartSource • TDbChartSource • TChartToolset • TChartAxisTransformations • TChartStyles • TChartLegendPanel • TChartNavScrollBar • TChartNavPanel • TIntervalChartSource • TDateTimeIntervalChartSource • TChartListBox • TChartExtentLink • TChartImageList
IPro TIpFileDataProvider • TIpHtmlDataProvider • TIpHttpDataProvider • TIpHtmlPanel
Virtual Controls TVirtualDrawTree • TVirtualStringTree • TVTHeaderPopupMenu