Executing Parameterized Queries

See Also

In many applications, you want to work with data sets created using conditions supplied in the application. For example, your application might display a report of all the employees in a department. You can set up a form to prompt users for the name of the department, and then execute a query based on the value they enter. This type of query is called a parameterized query.

For parameterized queries, you use a Recordset design-time control as you would for a table or other database object. The difference is that the Recordset control is bound to a stored procedure or an SQL statement instead of a table.

You can bind the control directly to the procedure or statement, or you can bind it to a data command that points to one of these types of object. For details about using data commands, see Getting Records.

To create a parameterized query

1. Drag a Recordset design-time control onto the page. For details, see Viewing Data.
   
   
2. Right-click the control and choose Properties. In the General tab, set the control's name and connection.
   
   
3. Under Source of data, specify the binding:
   • If you are binding to a data command, choose Database object, and then select DE Commands. From the Object Name list, choose the name of the data command to use.
     
     
   • If you are binding directly to a stored procedure, choose Database object, and then select Stored Procedures. From the Object Name list, choose the name of the stored procedure to use.
     
     
   • For SQL queries, choose SQL statement, and then enter the SQL text. Choose SQL Builder to launch the Query Designer. In the SQL statement, use a question mark (?) to indicate parameters in your query. For example, the following SQL statement creates a query in which the department name is a parameter:

     SELECT * FROM employee WHERE department = ?
     

     Note   Do not use named parameters. For more information about creating parameterized queries in the Query Designer, see Creating a Query with Unnamed Parameters.

4. Choose the Parameters tab. Under Values for Parameters you will see a list of the parameters specified for your query. For each parameter where the value of Type is In (a parameter that is being passed into the stored procedure or query), specify a value to be passed to the stored procedure. Values can be:
   • Literals   Enter character values in single quotation marks and numeric values without quotation marks.
     
     
   • Variables   Enter the name of a variable defined in server code that will contain the value you want to pass.
     
     
   • Object references   Enter an object reference and property value, such as Textbox1.value. The object must be one that is available in server script. Be sure to use correct capitalization, because the expression will be evaluated as a JavaScript expression.
     
     
   • Expressions   Enter any combination of literals, variables, object references, and function calls. The expression is evaluated as a JavaScript expression, so use JavaScript conventions, including single quotation marks for character literals and a plus sign (+) for concatenation.

You must make sure that the value for the parameter can be evaluated successfully when the query is run. By default, a Recordset design-time control will execute the query when the page is first loaded. In that case, the parameter value cannot be a value that is gathered or evaluated only after the page has been displayed. If you are passing the value of a variable as a query parameter, you can use two events that are processed before the recordset is opened:

• The onenter event for the page.
  
  
• The onbeforeopen event for the recordset.

Alternatively, you can also specify that the Recordset control does not automatically open the recordset when the page opens. This strategy is useful if you are using the same page for the data-entry form and the results.

To prevent the Recordset control from automatically opening a recordset

• Right-click the Recordset control and choose Properties. In the Advanced tab, clear the Automatically open the Recordset option.

You can initially display the page without the recordset and prompt for a value using a form. When the user fills in the form and clicks a button, you can open the recordset and pass it the value of a variable or control.

To open the recordset

• Call the recordset script object's open method.

For example, you might create a form with a textbox design-time control and a button design-time control. The user can fill in the text box with the value to search for, and then click the button.

In the Parameters tab of the Recordset control's Property Pages window, specify the textbox script object's value as the parameter using this expression, substituting the name of the textbox for textbox1:

textbox1.value

The onclick event handler for the button then simply calls the recordset script object's open method, as in this example:

Function Button1_onclick
   Recordset1.open()
End Function