working with ADOQuery1 & ADOQuery2

Question

i have a ADOQuery1 that is my master table and now i whant to use onother one ADOQuery2 for my detail Table but realy i don´t find the right way to work out the solucion.

ADOQuery1 nr- Autonum name local quant. ADOQuery2 Nro _ Autonum name
l1 l2
l3

can some one help thank´s

Answer 1

I have some components that automate this, but when using the raw TADOQuery, this is how I implement it:

On Master TADOQuery.AfterScroll event do this:

myDetailQuery.Active := False;
myDetailQuery.Params.ParamByName('ID').Value := DataSet.FieldsByName('ID').AsString;
myDetailQuery.Active := True;

The query for Detail TADOQuery looks like this:

SELECT * FROM DetailTable
WHERE ID = :ID;

If there's a better way, I'd love to see it too.

TADOTable has this functionality built in, so you might consider using that instead. Set TADOTable.MasterSource. There's more information on that property in Delphi help.

Answer 2

For AdoQuery, you should use the DataSource property. Here is what you should do:

1- Drop an AdoQuery on the form as the master dataset, and write the query using its SQL property.

2- Drop a DataSource component on the form, and connect it to the master AdoQuery you built in step 1.

3- Drop another AdoQuery on the form as the detail dataset.

4- Set DataSource property of your detail dataset to the datasource which is connected to the master dataset (Refer to step 2).

5- Write the SQL query for your detail dataset, using its SQL property; in the query, you should have a WHERE clause which filters your detail dataset using a paramater which has the same name as one of the fields in the master datasource. For example, suppose you have an ID field in your master dataset, and a field called MasterID in your detail dataset which is a foreign key for ID field of master dataset. Now to connect the detail dataset to the master one, using these fields, you can write a query for the detail dataset like this:

SELECT * FROM DetailTable WHERE MasterID = :ID

As you can see, we have a SQL parameter named ID, which has the same name as ID field in master datasource. Now, whenever you browse records in the master datasource, Delphi automatically retrieves the value of ID field, and uses it as the value of ID parameter in the detail dataset, and requeries the detail dataset. You do not need to set the parameter value and requery explicitly. This will be done automatically for you.

I hope this clarifies things to you about how to make a master-detail relationship using AdoQuery datasets.

Regards