How To Call a Parameterized Query to Access with Recordset DTC

MORE INFORMATION

Due to limitations with Access as a database provider, the Recordset DTC is unable to determine the parameters and their properties specified for a parameterized query to Access. This can be accomplished by creating a DE Command and defining the parameters, then specifying that DE Command as the source for the Recordset DTC.

The following is an example of how this is done. In this example, you will be executing the following parameterized query against the Adventure Works database (AdvWorks.mdb):

   SELECT * FROM Products WHERE ProductType = ? AND UnitPrice >= ?
				

Step 1

1. Open a Visual InterDev 6.0 project with a Data Connection to Adventure Works (AdvWorks.mdb). If you do not have a Data Connection to Adventure Works, right-click the project, select Add Data Connection and follow the steps to create a Data Connection to the Adventure Works Access Database (AdvWorks.mdb).
2. Add a new Active Server Page to this project.

Step 2

1. Right-click the Project and select Add Data Command. The Data Command Properties window will appear.
2. Set the Command Name to ProductQuery and the Connection to your Data Connection to the Adventure Works database.
3. Select SQL Statement as your Source of Data. Enter the following SQL Statement:
   

   SELECT * FROM Products WHERE ProductType = ? AND UnitPrice >= ?

4. Click Apply. A dialog box with the following error will appear:
   
   Unable to determine the parameter information for the parameters. Use the Parameters Tab to specify the appropriate information.
5. Click OK to clear the dialog box and continue.

Step 3

1. Select the Parameters tab. Two parameters (Param1, Param2) will appear in the Parameters list.
2. Select Param1. In the Parameter Properties section, set the Name to ProductType, the Data Type to adVarChar, and the Size to 20. Leave the remaining values as their defaults.
3. Select Param2. In the Parameter Properties section, set the Name to UnitPrice. Leave the remaining values as their defaults. Click OK.

Step 4

1. Add a Recordset DTC to the Active Server Page.
2. Set the Connection to your connection to Adventure Works, the Database Object to DE Commands and the Object Name to ProductQuery.

Step 5

1. Add a Grid DTC to the Active Server Page.
2. Right-click the Grid DTC and select Properties.
3. In the General area, change the width from Pixels to Percentage.
4. Click the Navigation tab and select Enable row navigation.
5. Pick a row color. Click the Data tab and set the Recordset to Recordset1.
6. In the Available fields section select ProductID, ProductCode, ProductType, ProductName and UnitPrice. Click OK.

Step 6

1. In the Toolbox, select the Script Outline.
2. Expand Server Objects & Events. Expand Recordset1 and double-click onbeforeopen.
3. In the onbeforeopen() event of Recordset1, place the following code (VBScript) to set the parameters.
   

         Sub Recordset1_onbeforeopen()
           ' Search for Boots $90 and over
           Recordset1.setParameter 0, "Boot"
           Recordset1.setParameter 1, 90
         End Sub
   						

4. Save the page and view it in the browser.