3.3. Query parameters

There can be parameters in a query text. Let us see the following query:

select * from DVDs
where Title = @param1

This is the query to MS SQL demonstration database. The parameter with “param1” name is defined in a query. Here it should be noted: method of describing parameters in a query differs for different DBMS. For MS SQL a parameter is marked by a “@” symbol, MS Access parameters do not have names and are marked by the “?” symbol.

If your SQL query contains parameters, you have to declare them. It can be done in the third step of the “Query Wizard” which we have looked at above. To create a parameter, press the “Add parameter” button.

The following parameter’s properties should be set in the properties window:

Property Description
Name Parameter name. Here you need to indicate the same name which you use in the query text. Some DBMS (for example, MS Access) do not support named parameters. In this case do not change this property.
DataType Parameter data type.
DefaultValue Value which will be used if the “Expression” property is not specified, or if it is impossible to calculate it (for example, when operating with the query in the report design mode).
Expression Expression which returns parameter’s value. This expression will be processed when you run the report. You can indicate any expression in this property (see details in the “Expressions” chapter).
Size Parameter data size. This property should be indicated if the parameter is of “string” data type.

Passing a value to the parameter

Parameters are often used to ask a value from the user. Let us look at two ways to pass a value to the query parameter.

In the first way, you pass a value programmatically. Since there is no easy way to pass a value directly to the query parameter, you need to use the report parameter, which can be easily set via code. You should do the following:

report1.SetParameterValue("MyReportParameter", 10);

Report Parameters Top Page System Variables