Visual InterDev

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.

Note   If you are writing ASP pages, you can set options that help you find errors and trace events in the scripting object model. For details, see Debugging Script Objects in ASP Pages.

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:
  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:

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:

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

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

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