|
|
|
|
|
|
|
|
|
|
- Overview
- Using a query with parameters in a Table, Looper, List Box or Combo Box control
- Using a query with parameters in a report
- Principle
- Example
- Using a query with parameters through programming
- Principle
- Example
- Tip
- Example
Using a query with parameters
A query with parameters can be used: - As data source of control (Table, List Box, Combo Box, Looper, etc.).
- As data source of report.
- Programmatically.
Using a query with parameters in a Table, Looper, List Box or Combo Box control When a browsing Table control is bound to a query with parameters (independent or embedded), the parameters must be passed to the query before it is executed. 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 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: 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 through programming Principle When running the query with parameters through programming, the parameters can be passed: Example To display the list of orders placed in 2018: // Execute query
HExecuteQuery(QRY_ParamOrder, hQueryDefault, 2018)
// Similar code:
// QRY_ParamOrder.Year = 2018
// HExecuteQuery(QRY_ParamOrder, hQueryDefault) 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'
|
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|