How to edit Dataset?

Question

Short Version

How do i call:

dataset1.FieldByName(fieldName).AsString := 'Something';

and have it work?

Long version

I have a DataSet:

var
    ds: TDataSet;

    ds := GetSomeSortOfDataSetFromSomewhere();

This dataset will be exported (e.g. to Excel, cSV, TSV, Markdown, HTML, XML):

ExportDataSet(ds);

and the export will contain all columns and all rows:

Username	Fullname
ian	IAN BOYD
MartynA	MARTIN
ngal	NASREDDINE GALFOUT
uewr	UWE RAABE

Now i want to modify the Fullname field for each row in-memory before doing something else with it (i.e. it's never going back into a database, i don't know where it came from , it might not have come from a database):

while not ds.EOF do
begin
    ds.FieldByName('Fullname').AsString := FormatNamePrettyLike(ds.FieldByName('Fullname').AsString;
    ds.Next;
end;

Trying to modify a field gives the exception:

Dataset not in edit or insert mode

The solution is to clone the dataset into an in-memory TClientDataset:

///<summary>Clones a dataset into a TClientDataSet; which is an editable in-memory DataSet.</summary>
function CloneDataSet(dsSource: TDataSet): TDataSet; //TDataSet > TCustomClientDataSet > TClientDataSet
var
    tempProvider: TDataSetProvider;
    data: OleVariant;
    ds: TClientDataSet;
begin
    tempProvider := TDataSetProvider.Create(nil);
    try
        tempProvider.DataSet := dsSource;
        data := tempProvider.Data;
    finally
        tempProvider.Free;
    end;

    ds := TClientDataSet.Create(nil);
    ds.Data := data;

    Result := ds;
end;

Which gives larger code:

var
   ds: TDataset;
   dsEditable: TDataSet;

   ds := GetDataSomeOfSomeSortFromSomewhere();

   //Clone to dataset to an in-memory dataset so we can modify it.
   dsEditable := CloneDataSet(ds);
   ds.Free;
   ds := edEditable;

   while not ds.EOF do
   begin
       ds.FieldByName('Fullname').AsString := FormatNamePrettyLike(ds.FieldByName('Fullname').AsString;
       ds.Next;
    end;

But this gives the error:

Dataset not in edit or insert mode

The solution is to put the dataset in edit mode:

//The in-memory ClientDataSet won't be editable until you mark it editable.
ds.Edit; 

 

///<summary>Clones a dataset into a TClientDataSet; which is an editable in-memory DataSet.</summary>
function CloneDataSet(dsSource: TDataSet): TDataSet; //TDataSet > TCustomClientDataSet > TClientDataSet
var
    tempProvider: TDataSetProvider;
    data: OleVariant;
    ds: TClientDataSet;
begin
    tempProvider := TDataSetProvider.Create(nil);
    try
        tempProvider.DataSet := dsSource;
        data := tempProvider.Data;
    finally
        tempProvider.Free;
    end;

    ds := TClientDataSet.Create(nil);
    ds.Data := data;

    //The in-memory ClientDataSet won't be editable until you mark it editable.
    ds.Edit;

    Result := ds;
end;

Repeating the excerise now gives the error:

Field Fullname cannot be modified.

The solution is to set Field.ReadOnly to false:

//Even after marking the in-memory data-set as editable, you still can't edit it 
//until you mark all fields as editable.
for i := 0 to ds.FieldCount-1 do
   ds.Fields[i].ReadOnly := False;

 

///<summary>Clones a dataset into a TClientDataSet; which is an editable in-memory DataSet.</summary>
function CloneDataSet(dsSource: TDataSet): TDataSet; //TDataSet > TCustomClientDataSet > TClientDataSet
var
    tempProvider: TDataSetProvider;
    data: OleVariant;
    ds: TClientDataSet;
begin
    tempProvider := TDataSetProvider.Create(nil);
    try
        tempProvider.DataSet := dsSource;
        data := tempProvider.Data;
    finally
        tempProvider.Free;
    end;

    ds := TClientDataSet.Create(nil);
    ds.Data := data;

    //The in-memory ClientDataSet won't be editable until you mark it editable.
    ds.Edit;

    //Even after marking the in-memory data-set as editable, you still can't edit it 
    //until you mark all fields as editable.
    for i := 0 to ds.FieldCount-1 do
        ds.Fields[i].ReadOnly := False;

    Result := ds;
end;

Repeating the exercise gives the error:

Trying to modify read-only field.

So i give up. How do i edit a DataSet field?

The cloned in-memory TCustomClientDataSet contents are all there; i just want to edit them on the client for display purposes.

Bonus Chatter

Obviously i can't be adding new columns to the data set:

Username	Fullname	PrettyFullname
ian	IAN BOYD	Ian Boyd
MartynA	MARTIN	Martin
ngal	NASREDDINE GALFOUT	Nasreddine Galfout
uewr	UWE RAABE	Uwe Raabe

Obviously i can't attach an event handler to the data set:

• as that data handler would be invalid when the data set is passed onto the next person in a chain (e.g. a thread), and the original form is freed
• and it's also not what i'm asking; which is about modifying the contents of a data set
• the updating of values hits other systems (e.g. databases, web-services, etc). I want the changes done once, and then in the data set

Answer 1

Assuming I actually understand your question correctly, it boils down to change the field content of FullName to some pretty formatted string for display.

So as you don't want to change the actual field content, the best place to do this is in the fields OnGetText event. A proper event handler for your task could look like this:

procedure TMyClass.MakeFullNamePrettyGetText(Sender: TField; var Text: string; DisplayText: Boolean);
begin
  Text := FormatNamePrettyLike(Sender.AsString);
end;

Now you have to wire that event handler to the field. As you are working with dynamic fields this has to be done each time after opening the dataset:

qry.FieldByName('Fullname').OnGetText := MakeFullNamePrettyGetText;

As long as this happens outside of the class the event is declared in, you need to prefix the event name with a class instance of TMyClass (or whatever you may call it).

Answer 2

Below is a completely self-contained example of edting data from a Sql Server using ADO + a TClientDataSet. All the components are simply dropped from the palette onto the form and then all necessary properties are set up in code in the SetUp procedure.

At each step, I've tried to use the simplest code to do the job so as not to obscure the elegant simplicity of how the CDS + TDataSetProvider work to edit data. See the TSqlResolver.GenUpdateSql method in Provider.Pas to see how it generates the necessary Sql UPDATE statements for the DSP to emit to update the data in the server table. These are sent to the server via a special type of datapacket which the DSP uses to communicate between its CDS and source dataset.

Hopefully the code is self-explanatory with minimal comments.

As you will see, absolutely no fiddling with the attributes of the CDS's TFields is necessary. Btw, I have done this as a VCL app rather than a console one simply so that it's trivial to visually confirm that everything is working.

As a kind of lowest common denominator I've used D7. In post-Unicode Delphi, the FullName field on the server would be an NVarChar type and the string field type of the CDS would adjust automatically.

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    CDS1: TClientDataSet;
    DataSetProvider1: TDataSetProvider;
    DBGrid1: TDBGrid;
    procedure FormCreate(Sender: TObject);
  private
    procedure SetUp;
  end;
[...]
const
  scConnString = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MATest;Data Source=MAT430\ss2014';
  scCreateTable = 'create table TestTable(ID int not null primary key, FullName varchar(40))';
  scSelectAll = 'select * from TestTable';

procedure TForm1.SetUp;
begin
  AdoConnection1.ConnectionString := scConnString;

{.$define CreateTable}  // to do a one-off creation of the server table and data
{$ifdef CreateTable}
  AdoConnection1.Execute(scCreateTable);
{$endif}

  AdoQuery1.Connection := AdoConnection1;
  AdoQuery1.SQL.Text := scSelectAll;

{$ifdef CreateTable}
  AdoQuery1.Open;
  AdoQuery1.InsertRecord([1, 'Joe Blow']);
  AdoQuery1.Close;                             
{$endif}

  DataSetProvider1.DataSet := AdoQuery1;
  CDS1.ProviderName := 'DataSetProvider1';
  DataSource1.DataSet := CDS1;
  DBGrid1.DataSource := DataSource1;

  CDS1.Open;
  CDS1.Edit;
  CDS1.FieldByName('FullName').AsString := 'Mr ' + CDS1.FieldByName('FullName').AsString;
  CDS1.Post;

  //  Post the chamges back to the server table if desired
  CDS1.ApplyUpdates(0);
end;

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

end.

Addendum One point which was raised in comments was the possibility of effectivel prettifying the FullName field by using a calculated field. I can't immediately think of a way to set this up in code but basicially I would do this:

• On the CDS, set up persistent TFields (from the CDS's context menu).
• In the Fields editor, add a calculated field of type fkInternalCalc. For a CDS, this is better to use than fkCalculated because an fkInternalCalc can be included in an index on the CDS
• Do whatever calculation is necessary in the CDS's OnCalcFields event. No traversal (in your code) of the CDS records is then necessary to do the calculations because the CDS does the calculations in its own machinations.

Update It turns out that it is straightforward, if a bit of a rigmarole, to add an fkInternal calc field to a CDS entirely in code. The trick is to retrieve the FieldDefs from the server, persist them in the CDS and then re-create its TFields and re-open it. Like so:

  CDS1.Open;
  CDS1.StoreDefs := True;
  CDS1.Close;
  for i := 0 to CDS1.FieldDefs.Count - 1 do begin
    Field := CDS1.FieldDefs[i].CreateField(Self, Nil, CDS1.FieldDefs[i].DisplayName);
  end;
  Field := TStringField.Create(Self);
  Field.Size := CDS1.FieldByName('FullName').Size;
  Field.FieldKind := fkInternalCalc;
  Field.FieldName := 'EnhFullName';
  Field.DataSet := CDS1;

  CDS1.Open;