I setup the master detail with the master field/detail field. But when I insert a row to any master the forign keys are not matched with the detail record.
Is there an easy way to do this with the TMyQuery object?[/img]
Mutiple Master/Detail Insert Problem
Mutiple Master/Detail Insert Problem
Maybe I'm just using this wrong. I have several TMyQuery/TMYDataSource objects with multiple forign key references...
I setup the master detail with the master field/detail field. But when I insert a row to any master the forign keys are not matched with the detail record.
Is there an easy way to do this with the TMyQuery object?[/img]
I setup the master detail with the master field/detail field. But when I insert a row to any master the forign keys are not matched with the detail record.
Is there an easy way to do this with the TMyQuery object?[/img]
the way i have been doing it is to first Start a transaction (MSConnection)
then insert the master record, Post the master record, then get the ID of the master record just posted, then insert the detail record and post. Finally call MSConnection.commit or MSconnection.rollback to commit or cancel the changes
then insert the master record, Post the master record, then get the ID of the master record just posted, then insert the detail record and post. Finally call MSConnection.commit or MSconnection.rollback to commit or cancel the changes
Found an answer
Well, I found one way to get around this. By mapping all of the insert, update and post events I found the sequence.
Here's the problem:
Just taking three Query/Source combinations...
The PatientEntity has two children; PatientRecord and PatientAddress. When inserting new records for all three, sometimes the PatientRecord would end up with the key for the PatientEntity and sometimes not. The PatientAddress never had the key.
Here is the sequence of events:
1. Set Query to Insert
2. Source OnStateChange Event - Key is 0 - state is dsInsert
3. Query BeforePost Event
4. Query BeforeUpdateExecute Event
(Assuming "update" moves EditText values to dataset)
5. Query AfterUpdateExecute Event
6. Source OnStateChange Event - Key is set - state is dsBrowse
(Can use OnStateChange to force child queries to post causing same sequence for them)
7. Query AfterPost Event and Records Refresh.
By placing code to force updates and insert forign keys inside the Source OnStateChange event, you can capture the record key directly from the field rather than trying to use the InsertID which always gives the last inserted ID for that table which may not be what you want.
Hope this trace helps someone else.
Key words:
Master Detail Parent Child Relationship Post Record Key ID
Here's the problem:
Just taking three Query/Source combinations...
The PatientEntity has two children; PatientRecord and PatientAddress. When inserting new records for all three, sometimes the PatientRecord would end up with the key for the PatientEntity and sometimes not. The PatientAddress never had the key.
Here is the sequence of events:
1. Set Query to Insert
2. Source OnStateChange Event - Key is 0 - state is dsInsert
3. Query BeforePost Event
4. Query BeforeUpdateExecute Event
(Assuming "update" moves EditText values to dataset)
5. Query AfterUpdateExecute Event
6. Source OnStateChange Event - Key is set - state is dsBrowse
(Can use OnStateChange to force child queries to post causing same sequence for them)
7. Query AfterPost Event and Records Refresh.
By placing code to force updates and insert forign keys inside the Source OnStateChange event, you can capture the record key directly from the field rather than trying to use the InsertID which always gives the last inserted ID for that table which may not be what you want.
Hope this trace helps someone else.
Key words:
Master Detail Parent Child Relationship Post Record Key ID