CODEWITHSUNDEEP
delphidelphi-7master-detaildbexpress
Francesco
Francesco

Reputation: 1792

Delphi7, dbExpress and Master Detail relationship

I'm new to Delphi. I got two TSQLTables (say A and B) linked to two TClientDataSets (say cdsA and cdsB respectively) by two TDataSetProviders, two DataSources (dsA and dsB) complete the scenario.

Let A be the master one and let B be the detail one.

B.MasterSource is set to dsA value and B.MasterFields value refer to a field that does not exists in cdsA (but exists in the query). When I start the application I open cdsA first and then I open cdsB. Something goes wrong. The DBGrid that link to dsA datasource shows data, the DBGrid that link to dsB does not show anything. The SQLMonitor logfile shows that the query implemented in B is executed (a simple select a, b, c from tableB ). The stuff works fine if I change the query and show the field 'X' (select a, b, c, X from tableB) where 'X' is the field referred by B.IndexFieldNames property.

Why the DBGrid that link to dsB does not show the B's record related to cdsA's current record? Does it works only if I specify the IndexFieldNames in the query columns? What did I miss? TIA.

Upvotes: 3

Views: 3316

Answers (2)

DavidG
DavidG

Reputation: 82

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.

Upvotes: 0

LightBulb
LightBulb

Reputation: 964

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.

Upvotes: 2

Related Questions