TMSQuery

[nijazicitaku]

Hi there,


is there any possibility to apply MSQuery calculated fields to a table with no calculate dfields?

[azyk]

You can write the value of the dataset calculated field to a table field using the BeforeUpdateExecute event handler by passing the value via query parameter. The below sample demonstrates how to do this on record adding.

Generate an INSERT query:

Code: Select all

INSERT INTO TestTable
  (... , TestField)
VALUES
  (... , :TestField)
...

Set the query as a value for the TMSQuery.SQLInsert.Text property. In the BeforeUpdateExecute event handler, set the calculated field of the dataset as a value for the parameter.

Code: Select all

procedure TMainForm.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if (StatementTypes = [stInsert]) then
    Params.ParamByName('TestField').Value := MSQueryCalcField.Value;
...

[nijazicitaku]

Hi there,


thank you very much for your prompt reply, but my question was is it possible to
do on MSQuery.ApplyUpdates?




Thanks

[AlexP]

Hello,

When calling the ApplyUpdates method, the BeforeUpdateExecute event will be triggered for each changed record.

[nijazicitaku]

Hi,


ok but what if updated record already exists in table?



Cheers

[azyk]

Generate an UPDATE query:

Code: Select all

UPDATE TestTable SET
...
  TestField = :TestField
WHERE
...

Set the retrieved query as a value for the TMSQuery.SQLUpdate.Text property in the BeforeUpdateExecute handler, add code that would set a value for the parameter on editing an existing record;

Code: Select all

procedure TMainForm.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin

...
  if (StatementTypes = [stUpdate]) then
    Params.ParamByName('TestField').Value := MSQueryCalcField.Value;
...

[nijazicitaku]

Hi,

As first HAPPY EASTER.

Here is the code I'm using.
Calculated fields are not recognized as parameters on BeforeUpdateExecute.

with QryMallDetal do begin
SQL.Clear;
SQL.Add('SELECT TOP 1 MHDUI, MHDViti, MHDNjOrg, MHDLlDok, MHDNum, MHDRen, MHDLldokt, MHDData, MHdKomId, MHDDudLloji, MHDDudZyra, MHDDudNum, MHDDudData, MHDKursi, MHDArtId,');
SQL.Add('MHDArtKodi, MHDArtEmri, MHDArtRap, MHDNrSerik, MHDDatSkad, MHDPeshaSpec, MHDRafti, MHDSasiPor, MHDSasia, MHDCmimFatV, MHDRabPrc, MHDRabVleV, MHDShpePrc, MHDShpeVleV,');
SQL.Add('MHDRivPrc, MHDRivVle, MHDTranPrc, MHDTranVle, MHDDogPrc, MHDDogVle, MHDAkcPrc, MHDAKcVle,');
SQL.Add('MHDTVShPrc, MHDTVShVle, MHDTVShKred, MHDTvsh, MHDShpenPrc, MHDShpenVle, MHDTatShit, MHDPakMarPrc, MHDPakMarVle, MHDPakCmim, MHDShumMarPrc,');
SQL.Add('MHDShumMarVle, MHDShumCmim, MHDProj, MHDShtoUser, MHDShtoKoha, MHDEditUser, MHDEDitKoha, MHDAktiv FROM tblMallHyrDetajet');
Prepared;
Open;
end;
QryMallDetal.Close;

QryMallDetal.FieldDefs.Update;
for i := 0 to QryMallDetal.FieldDefList.Count - 1 do
with QryMallDetal.FieldDefList do
if (DataType <> ftUnknown) and not (DataType in ObjectFieldTypes) then
CreateField( QryMallDetal, nil, QryMallDetal.FieldDefList.Strings);

with QryMallDetal do begin
FieldDefs.Update;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDCmimiV';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin FieldKind := fkCalculated;
FieldName := 'MHDCmimiFat';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDRabVle';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDShpeVle';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDCmimi';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDBazaTVSh';
DataSet := QryMallDetal;
Calculated:= true;
end;
with TFloatField.Create(QryMallDetal) do begin
FieldKind := fkCalculated;
FieldName := 'MHDCmKost';
DataSet := QryMallDetal;
Calculated:= true;
end;

end;

[azyk]

Compose a small test project to demonstrate how you attempt to pass the value of the calculated field via parameter to BeforeUpdateExecute. Include CREATE scripts for test tables into the project and send the project to andreyz*devart*com .

[nijazicitaku]

Hi there,

does TMSQuery supports InternalCalc fields?

[ViktorV]

SDAC doesn't support FieldKind "fkInternalCalc". If you want us to implement the feature, please post it at our user voice forum: https://devart.uservoice.com/forums/104 ... components. If the suggestion gets a lot of votes, we will consider the possibility to implement it.