PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • Using a query with parameters in a window or in a page
  • Principle
  • Example
  • Using a query with parameters in a Table, Looper, List Box or Combo Box control
  • Principle
  • Using a query with parameters in a report
  • Principle
  • Example
  • Managing the parameters coming from edit controls
  • Example
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Using a query with parameters
Overview
A query with parameters can be used:
  • In a window or in a page.
  • In a Table control, List Box control, Combo Box control or Looper control.
  • In a report.
In any case, the entered parameters can be managed in an edit control.
Using a query with parameters in a window or in a page

Principle

When creating a window (or a page), this window (or this page) can be based on a query with parameters. The query parameters can be entered in a previous window (or page) for example.
To use a window (or a page) based on a query with parameters, the query must be run (HExecuteQuery):
  • before opening the window or the page.
  • in the declaration code of the global variables of the window or page.
The necessary parameters must be passed when running the query. These parameters can be passed:

Example

To display the list of orders placed in year XXXX, the user performs the following operations:
  • enter the requested year in the WIN_EditParam window.
  • click the "Show the result" button. The WIN_OrderList window is displayed. This window is based on the QRY_ParamOrder query. This query is used to select the orders placed on the date specified by the user.
The following code is used to:
  1. open the WIN_EditParam window.
  2. retrieve the value entered by the user.
  3. run the QRY_ParamCustomer query.
1. Opening the window for entering parameters in the initialization process of the WIN_OrderList window:
// -- Initialization process of WIN_OrderList window
ParamQuery is string
// Open the WIN_EditParam window and
// retrieve the input performed by the user
ParamQuery = Open(WIN_EditParam)
2. Running the query in the initialization process of the WIN_OrderList window:
// Run the query
HExecuteQuery(QRY_ParamOrder, hQueryDefault, ParamQuery)
// Similar code:
// QRY_ParamOrder.Year = ParamQuery
// HExecuteQuery(QRY_ParamOrder, hQueryDefault)
Using a query with parameters in a Table, Looper, List Box or Combo Box control

Principle

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

    In this case, the query is automatically run when initializing the Table control.

    Note: you can also use one of the following notations:
    • the MySource keyword:
      MySource.<Name of Parameter1> = xxx
      MySource.<Name of Parameter2> = xxx
      MySource.<Name of Parameter3> = xxx
    • the indirection:
      {"<Query Name>.<Name of Parameter 1>"} = xxx
      or
      {"<Query Name>"}.<Name of Parameter 1> = xxx
  • HExecuteQuery.
One of these two methods can be used in the declaration code of the global variables of the window or in the initialization code of the Table control.
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)
Managing the parameters coming from edit controls
In most cases, the parameters passed to a query with parameters are entered by the user in edit controls.
In order for your query to be run even if no value was typed by the user, check "NULL if empty" for the edit controls ("Details" tab of control description).
When this option is checked, if the edit control is empty, the value of the parameter passed to the query will correspond to the NULL constant. Therefore, no error occurs when running the query: the conditions that depend on this parameter will be ignored.

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" optionSQL code run
No valueOption checked SELECT ItemNames
FROM NameOfFiles
No valueOption unchecked SELECT ItemNames
FROM NameOfFiles
WHERE Item = ' '
A value is enteredOption checked or unchecked SELECT ItemNames
FROM NameOfFiles
WHERE Item = 'ValueEntered'
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment