Devart Forums

I'm trying out sdac and I have a few questions for you:

I Have two tables:

Table Master
ID_Master : Int => AutoInc and primary key
Name : varchar

Table Detail
ID_Detail : Int => AutoInc and primary key
ID_Master : Int => fk to master table
Name : varchar

So, In my delphi application I have two tmsquery components:

Master:

SQL = select * from master
Insert = INSERT INTO master(name) VALUES (:name) SET :id_master = SCOPE_IDENTITY()

Detail

SQL = select * from detail
Insert = INSERT INTO detail (id_master, name) VALUES (:id_master, :name) SET :id_detail = SCOPE_IDENTITY()

My detail query has these propertys enabled: CachedUpdates, Options.ReturnParams.
My master query has only this property enabled: Options.ReturnParams.

I Wrote these events:

procedure TForm1.detailBeforeUpdateExecute(Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
if stInsert in StatementTypes then begin
Params.ParamByName('Id_Detail').ParamType := ptInputOutput;
Params.ParamByName('Id_Master').AsInteger := masterid_master.Value;
end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
master.Active := True;
detail.Active := True;
end;

procedure TForm1.masterAfterPost(DataSet: TDataSet);
begin
detail.ApplyUpdates;
end;

procedure TForm1.masterBeforeUpdateExecute(Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
if stInsert in StatementTypes then begin
Params.ParamByName('Id_Master').ParamType := ptInputOutput;
end;
end;


Everything works fine. But if I configure the detail query mastersource property (doing a master/detail) when I post the master query, nothing happens with the detail table and the records I inserted in the detail query are not posted to the sql server and the event BeforeUpdateExecute of the detail query is not triggered. What am I doing wrong?

I think that you insert a record in the master table, post it, and then insert records in the detail dataset.
The ApplyUpdates in this case is called before a record is inserted into the detail dataset. Therefore detailBeforeUpdateExecute is not raised.
You should reconsider logic of your application.

Ok .. but I'd like to insert both master and detail at the same time, posting the detail when user posts the master .. is that possible?

The record in the master table should be posted before inserting records in the detail dataset in order to fill the ID_Detail field of the Detail table with the correct value.
I can suggest using transactions. The scenario may the following:
1) Start transaction (TMSConnection.StartTransaction).
2) Insert and post a master record.
3) Insert and post detail records.
4) Commit or rollback changes (TMSConnection.Commit, (TMSConnection.Rollback).

For more information see the SDAC help.

That is not what I want. I want user to add a record in the master and then insert records in the detail. After that, the user posts the master and the detail is posted in sequence.
I figured out how to do that. I just dont use autoinc fields so I can generate my own ids.
Thanks anyway.