TDbLookupComboBox or equivalent control with a set

Question

Does a TDbComboBox-like control exist, that gets its displayed values by an fixed list (to use for an enumerated type, e.g. TSomeValueEnum = (svSmall=1, svMedium=2, svLarge=3))?

With for instance:

1=small
2=medium
3=large

In the database I save 1 or 2 or 3, but in the ComboBox only the corresponding value should be displayed.

Answer 1

I ended up implementing the following workaround (using Firebird as database, other implementations will differ):

QueryForComboBox.SQL.Text := 
  'SELECT 1 as VAL, ''small'' as TXT FROM RDB$DATABASE'
  + ' UNION ALL SELECT 2 as VAL, ''medium'' as TXT FROM RDB$DATABASE'
  + ' UNION ALL SELECT 3 as VAL, ''large'' as TXT FROM RDB$DATABASE';
//...
ComboBox.KeyField := 'VAL';
ComboBox.ListField := 'TXT';

For my little problem, this seems to be enough. For "real code" I think, I would prefer a TClientDataset-based solution (as proposed by Sir Rufo)

An other alternative would be to use TDbComboBox with custom drawing as described here.

Answer 2

You can use a TDbLookupComboBox combined with a memory-table (fi TClientDataSet). Fill the memory-table with the desired values and the TDbLookupComboBox will do the rest.

But there is one missing link, between the stored value in database, the string representation for the UserInterface and the Enum for your application. To put all together in one place you should build a class that will handle all this conversion for you in a convenient, safe and documented-by-code way.

TSomeValueEnum = (svSmall, svMedium, svLarge);

TSomeValue = class
private
  FAsInteger : Integer;
  FAsEnum : TSomeValueEnum;
  FAsString : string;
public
  // returns a new created list with all values
  class function CreateAsList : TObjectList;

  // constructors

  constructor Create( Value : Integer ); overload;
  constructor Create( Value : TSomeValueEnum ); overload;
  constructor Create( const Value : string ); overload;

  // equal comparer with other values    
  function Equals( Obj : TObject ) : Boolean; override;
  function SameValueAs( Other : TSomeValue ) : Boolean;

  // properties
  property AsEnum : TSomeValueEnum read FAsEnum;
  property AsInteger : Integer read FAsInteger;
  property AsString : string read FAsString;

  // Same properties but with different names, just for clarification

  // Value used in Database
  property DbValue : Integer read FAsInteger;
  // Value used for UserInterface
  property UIValue : string read FAsString;
  // Value used inside the applicatiom
  property AppValue : TSomeValueEnum read FAsEnum;
end;

implementation

type
  TSomeValueRec = record
    Int : Integer;
    Str : string;
  end;

// Translation-Array for DbValue and UIValue
const
  C_SomeValues : array[TSomeValueEnum] of TSomeValueRec = ( 
    {svSmall}  (Int:1; Str:'small'), 
    {svMedium} (Int:2; Str:'medium'), 
    {svLarge}  (Int:3; Str:'large') );

function TSomeValue.Equals( Obj : TObject ) : Boolean;
begin
  Result := ( Self = Obj ) or Assigend( Obj ) and (Self.ClassType = Obj.ClassType) and SameValueAs( Obj as TSomeValue );
end;

function TSomeValue.SameValueAs( Other : TSomeValue ) : Boolean;
begin
  Result := ( Self = Other ) or Assigned(Other) and (Self.FAsEnum = Other.FAsEnum);
end;

constructor Create( Value : Integer );
var
  LEnum : TSomeValueEnum;
begin
  inherited Create;
  for LEnum := Low(LEnum) to High(LEnum) do
    if C_SomeValues[LEnum].Int = Value then
      begin
        FAsEnum := LEnum;
        FAsInteger := C_SomeValues[LEnum].Int;
        FAsString := C_SomeValues[LEnum].Str;
        Exit;
      end;  
  raise EArgumentException.CreateFmt('unsupported value %d',[Value]);
end;

constructor Create( const Value : string );
var
  LEnum : TSomeValueEnum;
begin
  inherited Create;
  for LEnum := Low(LEnum) to High(LEnum) do
    if SameText( C_SomeValues[LEnum].Str, Value ) then
      begin
        FAsEnum := LEnum;
        FAsInteger := C_SomeValues[LEnum].Int;
        FAsString := C_SomeValues[LEnum].Str;
        Exit;
      end;  
  raise EArgumentException.CreateFmt('unsupported value "%s"',[Value]);
end;

constructor Create( Value : TSomeValueEnum );
begin
  inherited Create;
  FAsEnum := Value;
  FAsInteger := C_SomeValues[Value].Int;
  FAsString := C_SomeValues[Value].Str;
end;

class function TSomeValue.CreateAsList : TObjectList;
var
  LEnum : TSomeValueEnum;
begin
  Result := TObjectList.Create( True );
  for LEnum := Low(TSomeEnum) to High(TSomeEnum) do
    Result.Add( Self.Create( LEnum ) );
end;

To fill up the memory table get the list from TSomeValue.CreateAsList and fill the table from that list.

Answer 3

Raize Components has a TRzDbComboBox where you have separate lists for Items and Values.

From the help:

The TRzDBComboBox does support a Values property, which can be used to define a list of associated values to be stored in the selected database field instead of the string values maintained in the Items list. For example, the Items list could be set up to contain the following items: Visa, MasterCard, American Express; while the Values list would contain the following values: VISA, MC, AMEX. If the user selected the American Express item in the drop down, the AMEX value would be stored in the database table.