Insert and InsertID Problem

mwilems1 · Post by **mwilems1** » Thu 17 Sep 2009 17:28

Using Delphi 2009 and MyDAC 5.80.0.46:

I have a form with several datasets all using TMyQuery/TMyDataSource. Because of complex data relationships I use Master/Detail relationships when editing and browsing data to simplify the code and then programatically break those relationships when Inserting.

Browsing and Editing work fine. Inserting does not. No records are inserted.

Among others, I have the following datasets; to locate records: "findPatient"; demographic information: "patEntity"; address information (currently 1:1 but will later be 1:many): "patAddress".

Because of the data relationships I can not use the Navigator to insert or post information. I must do this programatically. The logic is as follows:

User selects "insert button" to execute "patInsertBtnClick":

Code: Select all

begin
  patEntity.Close;
  patEntity.MasterSource := nil'
  patEntity.MasterFields := '';
  patEntity.DetailFields := '';
  patEntity.CachedUpdates := true;
  patEntity.Open;
  patEntity.Insert;
  patAddress.Close;
  patAddress.MasterSource := nil'
  patAddress.MasterFields := '';
  patAddress.DetailFields := '';
  patAddress.CachedUpdates := true;
  patAddress.Open;
  patAddress.Insert;
end;

User edits the form data using fields tied to dataset and selects the "save button" to execute the follwoing logic:

Code: Select all

begin
  patEntity.FieldByName('last_updated_date').AsDate := Now();
  patEntity.Post;
  patKey := patEntity.InsertID;
  patAddress.FieldByName('entity_fk').AsInteger := patKey;
  patAddress.FieldByName('last_updated_date').asDate := Now();
  patAddress.Post;
end;

If the user selects to search for patients, the Master/Detail fields are reset and cached updates are turned off as Master/Detail cannot be used with cached updates.

My problem is that no records are being inserted and the InsertID function always returns 0. I have "Select * from tableName" for the SQL where tableName is the actual name of the table and I user the Filter property or Master/Detail properties to select the specific records I want.

What am I doing wrong?

Dimon · Post by **Dimon** » Fri 18 Sep 2009 08:15

To save data in a database when you use the cached updates mode you should call the TMyQuery.ApplyUpdates and TMyQuery.CommitUpdates methods after calling the Post method.

Note: you can use the same way for inserting record as for editing without any problem.

mwilems1 · Post by **mwilems1** » Fri 18 Sep 2009 14:16

Fantastic! That was the trick. I figured it was something simple. Thanks so much.