How to add a column to dataset?

[Sawlmgsj]

I want to query a set of tables in order to create a dataset that I can look
at (in a grid in my application) and decide manually whether I want to
include the records for further processing such as including in a report. I
want to have a Bit field which I can treat as a boolean value and then
check or uncheck it in my application. My tables do not contain the Bit
field - it only has to be present in my query.

Is it possible to generate such a field in my query which can be edited
subseqently by the operator in the grid in the application? I have tried
this with a query, but am not allowed to edit a derived field.

Can I add a field to a query which has been generated by a TMSQuery component?

Thanks,
Steve.

[Antaeus]

You can choose one of the following ways:
1)
Create a calculated field, and assign its values in the OnCalcFields event of the dataset dataset:

Code: Select all

var
  i: integer;
  f: TField;
begin
  MSQuery.FieldDefs.Update;
  for i := 0 to MSQuery.FieldDefs.Count-1 do
    f := MSQuery.FieldDefs[i].CreateField(MSQuery);

  f := TFloatField.Create(MSQuery);
  f.FieldName := 'Calculated';
  f.FieldKind := fkCalculated;
  f.DataSet := MSQuery;

  MSQuery.Open;
end;

2)
Request one extra field from the server and make it editable in your application:

Code: Select all

  MSQuery.SQL.Text := 'SELECT *, 0 AS ExtraField FROM dept';
  MSQuery.Open;
  MSQuery.FieldByName('ExtraField').ReadOnly := False;

[Sawlmgsj]

Many thanks for the help.

Regards,
Steve.

[Sawlmgsj]

I have used method 2 with SQL Server and I can edit the field.

When trying it with MySQL, it does not allow 'ExtraField' to be edited.
Is there a way to do that in MySQL?


Also tried method 1 with SQL Server as below
Is there a way that I can edit the calculated field in this method?


procedure TForm1.Button1Click(Sender: TObject);
var
i: integer;
f: TField;
begin
MSQuery1.FieldDefs.Update;
for i := 0 to MSQuery1.FieldDefs.Count-1 do
f := MSQuery1.FieldDefs.CreateField(MSQuery1);

f := TFloatField.Create(MSQuery1);
f.FieldName := 'Calculated';
f.FieldKind := fkCalculated;
f.DataSet := MSQuery1;

MSQuery1.Open;
MSQuery1.FieldByName('Calculated').ReadOnly := False;
end;

procedure TForm1.MSQuery1CalcFields(DataSet: TDataSet);
begin
MSQuery1.FieldByName('Calculated').AsFloat:=2.75;
MSQuery1.FieldByName('Calculated').ReadOnly := False;
end;

Thanks,
Steve.

[Antaeus]

Values of calculated fields can be edited only in the OnCalcFields event handler.
Why the second method does not work for MySQL? What happens when you try to edit that field? What is the exact version of MyDAC (x.xx.x.xx)?

[Sawlmgsj]

I have recently downloaded MySQL components - V 5.50.0.36

This is the code within the TMyQuery component:

SELECT Name, Address, 1 AS NewField FROM mytest

I open the query and display in a grid.

MyQuery1.FieldByName('NewField').ReadOnly:=False;

I try to edit NewField in the grid.
The message I get is: #42522Unknown column 'NewField' in 'field list'

I have tried adding the fields to the MyQuery1 - right click component - Fields Editor - Add all fields. That made no difference.

Can you help?

Thanks,
Steve.

[Antaeus]

When you edit the 'virtual' column, TMyQuery tries to post it to the server, but actually this field does not exists in the table. To prevent this you should properly setup SQLInsert, SLQUpdate, and other similar properties. For more information see the MyDAC help, please.

[Sawlmgsj]

Many thanks for your prompt reply.
I have used the SQL Generator to create this code for the SQLInsert:

INSERT INTO mytest
(Name, Address)
VALUES
(:Name, :Address)

The check box is greyed out for the SQLUpdate. I have tried entering my own code but not found anythng that works without an error. I could not find anythng in the help section.

Can you give me some more ideas?

Thanks,
Steve.

[Antaeus]

The Update checkbox is enabled if there are items in Key Fields and Update Fields listboxes and at least one item is selected in each listbox.