Delphi7, dbExpress and Master Detail relationship?

I'll explain the complete scenario using AdventureWorks database for SQL Server 2008 R2 I'll also assume that you've already placed the TSQLConnection component and properly set its parameters to established connection with your database. For this example, I'll also assume the name for it to be Conn1 On a form, place 2 TSQLTable (named tableA and tableB ), 2 TDataSetProvider (named dspA and dspB ), 2 TClientDataSet (named cdsA and cdsB ), 2 TDataSource (named dsA and dsB ) and 2 TDBGrid (named gridA and gridB ) components Set properties as follows: tableA. SQLConnection = Conn1 tableA.

SchemaName = Sales tableA. TableName = Customer tableA. Active = True dspA.

DataSet = tableA cdsA. ProviderName = dspA cdsA. Active = True dsA.

DataSet = cdsA gridA. DataSource = dsA tableB. SQLConnection = Conn1 tableB.

SchemaName = Sales tableB. TableName = SalesOrderHeader tableB. Active = True dspB.

DataSet = tableB cdsB. ProviderName = dspB cdsB. MasterSource = cdsA cdsB.

MasterFields = CustomerID cdsB. Active = True dsB. DataSet = cdsB gridB.

DataSource = dsB In gridA you should see all Customers and in gridB you should see only Orders related to curently selected customer This is the basic example of establishing master/detail relationship between two TClientDataSet components in Delphi. However, there are other ways to do this.

I'll explain the complete scenario using AdventureWorks database for SQL Server 2008 R2. I'll also assume that you've already placed the TSQLConnection component and properly set its parameters to established connection with your database. For this example, I'll also assume the name for it to be Conn1.

On a form, place 2 TSQLTable (named tableA and tableB), 2 TDataSetProvider (named dspA and dspB), 2 TClientDataSet (named cdsA and cdsB), 2 TDataSource (named dsA and dsB) and 2 TDBGrid (named gridA and gridB) components. Set properties as follows: tableA. SQLConnection = Conn1 tableA.

SchemaName = Sales tableA. TableName = Customer tableA. Active = True dspA.

DataSet = tableA cdsA. ProviderName = dspA cdsA. Active = True dsA.

DataSet = cdsA gridA. DataSource = dsA tableB. SQLConnection = Conn1 tableB.

SchemaName = Sales tableB. TableName = SalesOrderHeader tableB. Active = True dspB.

DataSet = tableB cdsB. ProviderName = dspB cdsB. MasterSource = cdsA cdsB.

MasterFields = CustomerID cdsB. Active = True dsB. DataSet = cdsB gridB.

DataSource = dsB In gridA you should see all Customers, and in gridB you should see only Orders related to curently selected customer. This is the basic example of establishing master/detail relationship between two TClientDataSet components in Delphi. However, there are other ways to do this.

I think the problem stay in the query. Using dbExpress I must specify the foreing key field in the query's column set, not only in it's where clause. Is this true?

If (as I hope) not what did I miss? – Francesco Nov 22 at 10:43 No, you don't have to do that. I'd advise you start a new project and recreate what I've explained.

That may help you to fully understand how to properly establish master/detail relations in Delphi. Also, take a look at the database itself. The structure of tables may help you to further understand how foreign keys work and may point you to possible problem in design of your database.

– LightBulb Nov 22 at 18:43 If I don't specify a IndexFieldNames value I get a field index out of range. If TabA JOIN TabB on a = b, I must specify be as value for detail's IndexFieldNames property. If not the grid does not show anything.

I work on Vista, Delphi7, Oracle11g, Devart dbExpress driver (version 4.70). – Francesco Nov 23 at 20:11 You do not have to JOIN those tables. Each table is queried separately in its own TSQLTable component and then they are 'joined' to form a master/detail relationship using the MasterSource and MsterFields properties.

Nothing else is required for a simple relation, unless you're trying something more complex. – LightBulb Nov 23 at 21:53.

The way that I link ClientDataSets is outlined in Cary Jensen's book "Delphi In Depth: Client DataSets". Setup the Master and Detail datasets as per normal, and ensure that they are linked via a TDataSource (you will have a parameter in the Detail SQL that links it to the Master). However, CJ suggests then having only one DataSetProvider which is attached to the Master.

But the master (and therefore the DSP) will have a Nested DataSet reresenting the detail table. The detail / nested dataset can appear in the master table DBGrid or in its own DBGrid. Your gridB will get linked to the Nested Dataset.

The problem in linking gridB directly back to the TSQLQuery (as I understand) is that any updates to the master CDS are not reflected in gridB. If you want to see more then you can download the project NestedFromMasterDetail from Cary's web site. If you really want to know more, then buy a copy of Cary's book.

I have found it invaluable in understanding Client Data Sets. They are setup somewhat different and Cary does a good job of explaining their architecture.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.