How can I use multiple TcxDBTextEdit with formatted data stored in a single DB field?

Question

I have a need to store two values in a single database field (yes, I agree that it is bad practice, but this is a legacy database that can't be altered). The data is stored as string1#4string2.

The data needs to be edited using two separate TcxDBTextEdit controls. But how can I connect them to the single database field so that I can edit string1 in one and string2 in the other?

I've tried adding two calculated (fkCalculated) fields to the TADOQuery, extracting/joining their values in OnGetText/OnSetText and reading/writing to the TStringField, but it didn't work.

So I tried creating a TdxMemData component with two fields and using them instead of the calculated fields, but it still doesn't work.

How can I achieve this (without altering the database structure)?

I'm afraid "didn't work" is not a useful problem description. Again, you need to add a [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) . — MartynA, Jun 28 '19 at 10:26
I guess, the first "didn't work" refers to the fact that calculated fields cannot be edited. — Uwe Raabe, Jun 28 '19 at 13:18
Have a look at this question and the accepted answer: https://stackoverflow.com/questions/7997932/its-possible-to-create-a-fake-data-field-in-a-delphi-dataset — Uwe Raabe, Jun 28 '19 at 13:21

MartynA · Accepted Answer · 2019-06-29T11:02:57.627

The sample project below does what you seem to want.

Update The code below replaces the code I originally posted and avoids the use of a dataset type (TClientDataSet) which supports fkInternalCalc fields. It will work with a TAdoQuery.

Although there is no difficulty in principle in parsing a string field into two subfields and surfacing them in your gui for editing, the problem with straightforward ways of doing this with a TAdoQuery is that it only supports fkCalculated calculated fields and db-aware gui controls treat these as not modifiable by the user.

I'm not sure why this restriction exists, but I imagine that it is related to the fact that Delphi's db-aware controls were originally developed for the BDE (and in aby case before fkInternalCalc was added to support TClientDataSet). The code in DB.Pas which enforces the restriction is in DB.Pas:

function TField.GetCanModify: Boolean;
begin
  if FieldNo > 0 then
    if DataSet.State <> dsSetKey then
      Result := not ReadOnly and DataSet.CanModify else
      Result := IsIndexField
  else
    Result := False;
end;

The code below works by adding an interposer class for TStringField which removes the restriction for stringfields whose FieldKind is fkCalculated which are not ReadOnly and belong to a dataset which is modifiable (though this latter restriction could be removed, I think). The interposer TStringField overrides GetCanModify like so:

function TStringField.GetCanModify: Boolean;
begin
  if (FieldKind = fkCalculated) and DataSet.CanModify and not ReadOnly then
    Result := True
  else
  if DataSet.State <> dsSetKey then
    Result := not ReadOnly and DataSet.CanModify else
    Result := IsIndexField
end;

The full code of an example project is below. Note that I've used regular TDBEdits because I have a problem with my current Devex set-up but the code should work fine with TcxDBEdit as well.

Code:

type
  TStringField = class(db.TStringField)
  protected
    function GetCanModify : Boolean; override;
  end;

type
  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    DataSource1: TDataSource;
    DBEdit1: TDBEdit;
    DBEdit2: TDBEdit;
    ADOQuery1: TADOQuery;
    cxDBMaskEdit1: TcxDBMaskEdit;
    DBEdit3: TDBEdit;
    btnDataLinks: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1ID: TIntegerField;
    ADOQuery1Field1: TWideStringField;
    ADOQuery1Field2: TWideStringField;
    ADOQuery1SubField1: TStringField;
    ADOQuery1SubField2: TStringField;
    procedure FormCreate(Sender: TObject);
    procedure ADOQuery1BeforePost(DataSet: TDataSet);
    procedure ADOQuery1CalcFields(DataSet: TDataSet);
  private
    procedure UpdateSubFields(DataSet : TDataSet);
    procedure UpdateField1(DataSet: TDataSet);
  end;

[...]
const
  scSeparator = '#4';   // could be a literal #4 instead

procedure TForm1.UpdateField1(DataSet : TDataSet);
var
  S : String;
begin
  if DataSet.FieldByName('SubField1').IsNull or DataSet.FieldByName('SubField2').IsNull then exit;

  S := DataSet.FieldByName('SubField1').AsString + scSeparator +
    DataSet.FieldByName('SubField2').AsString;
  S := Trim(S);
  if Length(S) > DataSet.FieldByName('Field1').Size then
    raise exception.Create('tthe combined size of the subfields is too long');

  DataSet.FieldByName('Field1').AsString := S;
end;

procedure TForm1.UpdateSubFields(DataSet : TDataSet);
var
  S,
  SF1,
  SF2 : String;
  P,
  SF2Start : Integer;
begin
  S := DataSet.FieldByName('Field1').AsString;
  P := Pos(scSeparator, S);
  SF1 := Copy(S, 1, P-1);
  SF1 := Trim(SF1);
  SF2Start :=  P + Length(scSeparator);
  SF2 := Copy(S, Sf2Start, Length(S));
  SF2 := Trim(SF2);

  DataSet.FieldByName('SubField1').AsString := SF1;
  DataSet.FieldByName('SubField2').AsString := SF2;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  AdoQuery1.Open;
end;

procedure TForm1.CDS1CalcFields(DataSet: TDataSet);
begin
  UpdateSubFields(DataSet);
end;

function TStringField.GetCanModify: Boolean;
begin
  if (FieldKind = fkCalculated) and DataSet.CanModify and not ReadOnly then
    Result := True
  else
  if DataSet.State <> dsSetKey then
    Result := not ReadOnly and DataSet.CanModify else
    Result := IsIndexField
end;

procedure TForm1.ADOQuery1BeforePost(DataSet: TDataSet);
begin
  UpdateField1(AdoQuery1);
end;

procedure TForm1.ADOQuery1CalcFields(DataSet: TDataSet);
begin
  UpdateSubFields(DataSet);
end;

So, has the updated version of my answer answered your q or not? — MartynA, Jun 30 '19 at 11:30

How can I use multiple TcxDBTextEdit with formatted data stored in a single DB field?

1 Answers1