Specifies the data source component from which to extract current field values to use with same-name parameters in the query's SQL statement.
Syntax:
property DataSource: TDataSource;
Description:
Set DataSource to automatically fill parameters in a query with fields values from another dataset. Parameters that have the same name as fields in the other dataset are filled with the field values. Parameters with names that are not the same as fields in the other dataset do not automatically get values, and must be programmatically set.
For example, if the SQL property of the TPSQLQuery contains the SQL statement below and the dataset referenced through DataSource has a Custno field, the value from the current record in that other dataset is used in the CustNo parameter.
SELECT * FROM Orders O WHERE (O.CustNo = :CustNo)
DataSource must point to a TDataSource component linked to another dataset component; it cannot point to this query's data source component.
The dataset specified in DataSource must be created, populated, and opened before attempting to bind parameters. Parameters are bound by calling the query's Prepare method prior to executing the query.
DataSource is especially of use when creating a master-detail relationship between tables using a linked query. It is also of use to guarantee binding for parameters that are not already set in the Params property or through a call to the ParamByName method.
If the SQL statement used by a query does not contain parameters, or all parameters are bound by the application using the Params property or the ParamByName method, DataSource need not be assigned. The example below shows setting the DataSource property of Query2 to the data source for Query1, preparing Query2, and activating Query2.
with PSQLQuery2 do
begin
DataSource := DataSource1;
Prepare;
Open;
end;
If the SQL statement in the TPSQLQuery is a SELECT query, the query is executed using the new field values each time the record pointer in the other dataset is changed. It is not necessary to call the Open method of the TPSQLQuery each time. This makes using the DataSource property to dynamically filter a query result set useful for establishing Master-Detail relationships. Set the DataSource property in the Detail query to the TDataSource component for the Master dataset.
If the SQL statement uses other than a SELECT query (such as INSERT or UPDATE), the parameters with the same name as fields in the other dataset still get values, but the query must be explicitly executed each time the other dataset's record pointer moves. For example, the SQL statement below uses the INSERT statement and has the parameters Custno and CompanyName.
INSERT INTO Customer (CustNo, Company)
VALUES (:CustNo, :CompanyName)
Another dataset, Query1 and DataSource1, has a CustNo field but no CompanyName field. If this dataset is used through the DataSource property, the CompanyName parameter must be programmatically assigned a value. Because Query1 has a CustNo field and Query1 is referenced through the DataSource property, the CustNo parameter automatically receives a value.
with PSQLQuery2 do
begin
DataSource := DataSource1;
ParamByName('CompanyName').AsString := Edit1.Text;
Prepare;
ExecSQL;
end;
If the SQL statement contains parameters with the same name as fields in the other dataset, do not manually set values for these parameters. Any values programmatically set, such as by using the Params property or the ParamByName method, will be overridden with automatic values. Parameters of other names must be programmatically given values. These parameters are unaffected by setting DataSource.
DataSource can be set at runtime or at design-time using the Object Inspector. At design-time, select the desired TDataSource from the drop-down list or type in the name.