Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Master Detail Datasets

pascal

  • Please log in to reply
2 replies to this topic

#1 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 21 March 2011 - 04:56 AM

Previous tutorials in this series:
 



Overview

To optimize our database, we will structure our data into several tables based on our logical approach to "entities" described by the data. To get the information back from the tables we will depend on the relationships between the tables. And the most basic relationship is known as master detail relationship. In master detail, a table holds the "master" data and another table contains the "details".


In order to establish master-detail relationship, both tables must linked by one or more fields with the same values. The linking field(s) in master table usually must be unique (can be the primary key or simply the field(s) is set as unique index), while ones in the details table don't have to be unique. If we are using unique field(s) in the details table, then we are establishing one-to-one relationship.


What we will get by establishing master detail relationship between two datasets in Delphi is that the details dataset will be automatically filled with records corresponding with the currently active record in the master dataset. There is no need for extra work to adjusting the query in the details dataset, close, and reopening it. Oh yes, we need to use query based datasets for the detail. We can't use table based datasets for the detail datasets.


Sample Tables

In this tutorial we are going to use two tables to illustrate the procedure establising master detail tables in Delphi application.


Our master table is named Customers containing information of customers who had purchased something from our imaginary store. The structure is:

Field Name          Type
ID                  Integer (Autonumber) - Primary key
Name                Text(40)
Note                Text(255)


Our detail table is named Orders to hold order information from our customers. The structure is:

Field Name          Type
ID                  Integer (Autonumber)
Customer            Integer
Date                Date/Time
Amount              Currency
Note                Text(255)



Customers table is linked to Orders by its ID field, which is the primary key of the table. While Orders table is linked to Customers through the Customer field. Customer field in Orders table is not unique.

By establishing master detail relationship in Delphi, we want to have a list of customers in a dataset, and whenever we select a specific customer another dataset will automatically be filled list of orders that correspond with the selected customer.
 
  • Create new Delphi project

    Create a new Delphi project. Save it to a folder of your choice.

    Drop a TDBGrid to the main form, rename it to grdCustomers. Adjust its location to be on top area of the form.

    Drop another TDBGrid to the main form and rename it into grdOrders. Adjust its location to be below grdCustomers.
  • Setup database connection

    Drop a TADOConnection to the main form. Rename it into cnnMain. Setup its connection string to point to our database. Visit this tutorial: Very Basic Database Application in Delphi to see some explanation on how to setup the connection string.
  • Setup the master dataset

    Drop a TADOTable to the main form. Rename it into tblCustomers, set its Connection property to point to cnnMain. Set its TableName property to 'Customers'. Activate it by setting its Active property to True.

    Drop a TDatasource to the main form. We need it to link tblCustomers to data controls and to the details dataset. Rename this TDataSource into dsCustomers. Set its Dataset property to point to tblCustomers. Link grdCustomers to this TDataSource by setting its
  • Setup the details dataset

    Drop a TADOQuery to the main form. Rename it to qryOrders. Point its Connection to cnnMain.

    Now the key steps in establishing master detail between tblCustomers and qryOrders are:
    • Adjust the SQL Select of the details dataset to indicate the relationship with the master. We do this by adding a WHERE clause that filtering the records of the details dataset against current active record of the master dataset. The WHERE clause use the name of the linking field of the details table and pair it with a parameter with the same name with the linking field of the master table.

      A parameter in Delphi query is created by adding a field with colon prefix. For example, if you want to add a parameter named 'Condition', then in the SQL query you add ':Condition' in appropriate location (e.g. "SELECT * FROM table WHERE Field1=:Condition").

      For our demo, we will use this query:

      SELECT Customer, [Date], Amount, Note FROM Orders WHERE Customer=:ID



      Detail-SQL_0001.png


      Note that:
       
    • Customer is the name of linking field of the detail table
    • ID is the name of the linking field of master table
  • Set the DataSource property of the qryOrders to point to dsCustomers. This link will make sure that the ID parameter will be replaced with correct value whenever record in tblCustomers is changed/moved.


    AdjustTheDataSource_001.png

Drop another TDataSource to the main form. Rename it to dsOrders. Point its Dataset property to qryOrders. Link this TDataSource to grdOrders by pointing grdOrders's DataSource to this new TDataSource.

After the above step, we are done with the design part. We now need to add some codes to accomodate when we insert new record in Orders table. In this case we need to set the Customer field of the new record with the currently selected customer's ID in tblCustomers. So add this code for AfterInsert event handler of qryOrders.

procedure TForm1.qryOrdersAfterInsert(DataSet: TDataSet);
begin
  qryOrders.FieldByName('Date').Value := Now;
  qryOrders.FieldByName('Customer').Value := tblCustomers.FieldByName('ID').Value;
end;




We are done! Just activate qryOrders and we are set to go. Now we are ready to run our demo project. Press F9 to run the demo and play around with adding new customers and orders.

Edited by LuthfiHakim, 10 February 2013 - 08:53 AM.

  • 0

#2 delphiuser

delphiuser

    CC Newcomer

  • Member
  • PipPip
  • 23 posts
  • Programming Language:Delphi/Object Pascal, Pascal
  • Learning:Delphi/Object Pascal

Posted 20 December 2012 - 12:03 PM

another great tuitiorial , i do have one problem though. when i try to activate qryorders i recieve an error. "Parameter object is improperly defined. Inconsistent or in complete information was provided" . how can this be solved.
  • 1

#3 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 21 December 2012 - 10:12 AM

Looks like you forgot to activate tblCustomers (the master dataset) before activating qryOrders.

Looks like the source code of the demo project was deleted when CodeCall moved to new forum software. I will find my copy and upload it soon.
  • 0





Also tagged with one or more of these keywords: pascal

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download