Re: Problem with Master/Detail tables


Hello everybody.
I use Delphi 2006, Access 2003, ADO. Provider is Microsoft.Jet.OLEDB.4.0.
I'm writing a stand alone application that uses two tables with
master/detail relationship.
I decided to use TADODataset. CursorLocation is clUseClient and CursorType
is ctStatic.
In the AfterOpen event of both ADODatasets I set
ADODataset.Properties['Update Criteria'].Value := adCriteriaKey;
Master primary key is based on two fields: Date (that is ftDateTime) and
Time (ftString).
Detail table contains the same two fields (Date and Time) and an index based
on those fields.
Master/detail relationship is consequently based on Date and Time.
In Access I set up the relation and I set also the referential integrity
with cascading update and cascading delete.
Everything worked fine during all the tests.
But one day I had to change the Date value of a record in the Master table
and here is my problem.
Date field in Detail table is NOT updated and the corresponding record
disappears from detail grid until I move back to the old Date value in the
master table. That is, the relation is broken for that record.
Why does this happen? What's wrong? If I do the same change of Date value
using Access instead of my application, the corresponding value in Detail
table is regularly updated.
Thanks for your help.

With client side cursors delphi is just using two tables with all the records in
memory. If you have cascading updates set these updates take place at the
database level. Those changes do not get set back to the ADO tables.

What you need to do is Requery the detail table when you change the key value of
a master record. That will reread the data from the database into the detail
and they keys should again match up.


--
Brian Bushay (TeamB)
Bbushay@xxxxxxxxx
.

Relevant Pages

  • Problem with Master/Detail tables
    ... CursorLocation is clUseClient and CursorType ... Master/detail relationship is consequently based on Date and Time. ... with cascading update and cascading delete. ... But one day I had to change the Date value of a record in the Master table ...
    (borland.public.delphi.database.ado)
  • Re: Problem with Master/Detail tables
    ... In the OnChange event of the two fields that make the PrimaryKey in the ... One solution could be adding an AutoInc field in the master table as primary ... Master/detail relationship is consequently based on Date and Time. ... with cascading update and cascading delete. ...
    (borland.public.delphi.database.ado)
  • Re: OTHER BIG BUG : deleting detail master delete
    ... database and i want to delete 'simple' a detail record in a table, ADO ... In a master detail relation with transaction and batchoptimistic ...
    (borland.public.delphi.database.ado)
  • Re: FK Relation
    ... >Yes the detail table has cascading delete too(at database level). ... >I use to connect ADO and OLEDB provider for sql server. ... Do you have any triggers on the Master table that update the master based on ... Borland's ADO components use all the fields in the record's selected fields to ...
    (borland.public.delphi.database.ado)
  • Re: ADO Master/Detail and Batch Update Problem
    ... I don't know what might be wrong with your code but, to my experience, ADO batch updates were messy, so instead I chose the ClientDataSet approach which works just fine. ... Detail ADO Query SQL statement is: "select * from detail where idmaster =:idmaster order by iddetail;" ... Detail ADO Query is linked to Master ADO Query through Master DataSource ...
    (borland.public.delphi.database.ado)