Inserting records with Nested ClientDataSet with autoincrementing link

Question

I'm teaching myself Delphi database programming using a MySQL database. I'm trying to add a record from a nested ClientDataSet with the link between master and detail tables an autoincrement field in the master table. I found a question/answer pair that appears to answer my question at: Inserting records with autoincrementing primary keys

The thing I don't understand is setting the required flag in the Query. I can't figure out how to do that as I'm too inexperienced, nor do I understand why it is necessary.

Similar to the question linked above, I have a

(SQLConnection->TSQLDataSet->DataSetProvider->ClientDataSet using dbexpress.
              |            |->LinkDataSource
              ->TSQLDataSet2->LinkDataSource

I load data into my nested ClientDataSet fine, so the component links to create the nested structure work. After loading the master/detail tables into the nested dataset, the following code gives an error.

MasterCDS1.Append;
MasterCDS1.FieldByName('TLNo').Required := False;
MasterSDS.FieldByName('TLNo').Required := False; { Error: Field 'TLNo' not found }
MasterCDS1.FieldByName('TLNo').ProviderFlags := [pfInWhere, pfInKey];
{ ... Populate Master table Fields}
MasterCDS1.Post;
MasterCDS1.ApplyUpdates(0);

TLNo is the field linking the tables and part of the primary key of the master table, and part of the primary key of the detail table. The third line where I try to set the TSQLDataSet generates the error shown in the comment. MasterSDS is where I put my 'Select * from master' query. MasterCDS learns the Schema from this query and that the field TLNo is a required field in both master and detail MySQL tables. That third line of code is my "interpretation" of what Mr Uwe Raabe said to do. Clearly I did this wrong. Can someone provide a code example so that this Delphi noob won't misinterpret the instructions? Thanks in advance.

I have no idea what your actual question is here. I see a whole lot of text and code, and then "Can someone provide a code example? Thanks in advance." Can you edit to reduce the extraneous information and ask a question we can answer? — Ken White

Uwe Raabe Uwe Raabe · Accepted Answer · 2013-05-18T09:31:23

The only reason I can imagine for the error you describe is that MasterSDS is not open when you execute that third line. "Field not found" raises either when the field does not exist in the table or the dataset (i.e. query in this case) is not open and has no static fields defined.

This leads to another point I want to mention: place the Required and the ProviderFlags settings in the AfterOpen event of the corresponding dataset. There is no need to repeat these settings whenever you append a record. If you work with static fields you can even do these settings in the Object Inspector.

For being a starter I suggest you always use static fields which can be adjusted inside the IDE. This will simplify things significantly.

Inserting records with Nested ClientDataSet with autoincrementing link

1 Answers