HOWTO: Call a Parameterized Query to Access with Recordset DTC
ID: Q197326
|
The information in this article applies to:
-
Microsoft Visual InterDev, version 6.0
SUMMARY
This article shows how to use the Visual InterDev 6.0 Recordset Design-time
control (DTC) to create a parameterized query.
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
- 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).
- Add a new Active Server Page to this project.
Step 2
- Right-click the Project and select Add Data Command. The Data Command
Properties window will appear.
- Set the Command Name to ProductQuery and the Connection to your Data
Connection to the Adventure Works database.
- Select SQL Statement as your Source of Data. Enter the following SQL
Statement:
SELECT * FROM Products WHERE ProductType = ? AND UnitPrice >= ?
- 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.
- Click OK to clear the dialog box and continue.
Step 3
- Select the Parameters tab. Two parameters (Param1, Param2) will appear
in the Parameters list.
- 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.
- Select Param2. In the Parameter Properties section, set the Name to
UnitPrice. Leave the remaining values as their defaults. Click OK.
Step 4
- Add a Recordset DTC to the Active Server Page.
- Set the Connection to your connection to Adventure Works, the Database
Object to DE Commands and the Object Name to ProductQuery.
Step 5
- Add a Grid DTC to the Active Server Page.
- Right-click the Grid DTC and select Properties.
- In the General area, change the width from Pixels to Percentage.
- Click the Navigation tab and select Enable row navigation.
- Pick a row color. Click the Data tab and set the Recordset to
Recordset1.
- In the Available fields section select ProductID, ProductCode,
ProductType, ProductName and UnitPrice. Click OK.
Step 6
- In the Toolbox, select the Script Outline.
- Expand Server Objects & Events. Expand Recordset1 and double-click
onbeforeopen.
- 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
- Save the page and view it in the browser.
Keywords : kbVisID600 kbGrpASP
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbhowto
|