Using a query with parameters

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

Help

Editors

Query editor

Query with parameters

Using a query with parameters

Overview

Using a query with parameters in a Table, Looper, List Box or Combo Box control

Using a query with parameters in a report

Using a query with parameters by programming

Tip

See also

Query with parameters

Using a query with parameters

Overview

A query with parameters can be used:

As data source of control (Table, List Box, Combo Box, Looper, etc.).
As data source of report.
By programming.

Using a query with parameters in a Table, Looper, List Box or Combo Box control

When a browsing Table control is linked to a query with parameters (embedded or not), parameters must be passed to the query before it is run.

If these parameters are not specified before the first execution of the query, the query will not be run and the Table control will remain empty.

To pass parameters to the query, you can use:

the following notation:

<Query Name>.<Name of Parameter1> = xxx
<Query Name>.<Name of Parameter2> = xxx
<Query Name>.<Name of Parameter3> = xxx
HExecuteQuery(<Query Name>)

In this case, the query is automatically run when initializing the Table control.
HExecuteQuery by specifying the parameters in the function.

Special case: the query parameters can be directly initialized in the initialization code of control. In this case, there is no need to use HExecuteQuery to calculate the query result. The syntax will be:

MySource.<Name of Parameter1> = xxx
MySource.<Name of Parameter2> = xxx
MySource.<Name of Parameter3> = xxx

See Queries and browsing Table controls for more details.

Using a query with parameters in a report

Principle

To use a report based on a query with parameters, the query must be run before opening the report. The query can be run by iInitReportQuery. This function is used to run the query and to specify the parameters of this query. This function can be called:

in the initialization code of report,
before iPrintReport.

Example

The WIN_CustomerForm window is used to browse the characteristics of customers. For each customer, the user can click the BTN_PrintOrder button to print the RPT_CustomerOrdReport report. This report is associated with the QRY_CustomerOrd query that selects the orders placed by a given customer.

The following code is used to run the QRY_CustomerOrd query according to the current customer and to print the report:

// -- Click process of BTN_PrintOrder button
// Run the query according to the current customer
iInitReportQuery(RPT_CustomerOrderReport, CustomerNum)
// Print the report
iPrintReport(RPT_CustomerOrdReport)

Using a query with parameters by programming

Principle

When running the query with parameters by programming, the parameters can be passed:

in the WLanguage HExecuteQuery function.
by using the following notation <Query Name>.<Parameter Name>, then by calling HExecuteQuery.

See Two methods for passing parameters to a query for more details.

Example

To display the list of orders placed in 2018:

// Run the query
HExecuteQuery(QRY_ParamOrder, hQueryDefault, 2018)
// Similar code:
// QRY_ParamOrder.Year = 2018
// HExecuteQuery(QRY_ParamOrder, hQueryDefault)

Tip

When a query parameter is initialized with the NULL value, the condition associated with this parameter is ignored when running the query.

For information: If the parameter value comes from an edit control, the option "NULL if empty" ("Details" tab of control description) is used to directly assign the NULL value if the control is not filled.

Example

The SQL code run is:

SELECT NameOfItems

FROM NameOfFiles

WHERE Item = {Param1}

For this example, the table below describes the use of "NULL if empty":

Value entered by the user	"NULL if empty" option	SQL code run

No value	Checked option	`SELECT ItemNames FROM NameOfFiles`
No value	Option unchecked	`SELECT ItemNames FROM NameOfFiles WHERE Item = ' '`
A value is entered	Option checked or unchecked	`SELECT ItemNames FROM NameOfFiles WHERE Item = 'ValueEntered'`