HOWTO: Call a Parameterized Query to SQL Server with Recordset DTC

• Microsoft Visual InterDev, version 6.0

SUMMARY

This article describes how to call a parameterized query against Microsoft SQL Server using the Visual Interdev 6.0 recordset design time control (DTC).

MORE INFORMATION

This sample uses a textbox DTC to allow users to enter a parameter, which is then passed to the parameterized query defined in a recordset DTC. The matching result is then displayed in a grid.

Before You Begin

Step 1: Adding a Data Connection

1. Create a new Visual Interdev project or use an existing project.



2. Right-click the Global.asa file and select Add Data Connection.



3. Specify the Data Source Name (DSN) for your SQL Server Pubs database. Follow the steps in the dialog box and enter values for userid, password, and so forth.



4. When presented with the Data Connection Properties page, name the data connection Pubs.

Step 2: Adding Controls to the Page

1. Add a new Active Server Pages (ASP) page to the project.



2. Drag a textbox DTC onto the body of the page.



3. Drag a recordset DTC onto the body of the page (leave the name of the textbox DTC as Textbox1).



1. Enable the Scripting Object Model when prompted.



2. Select Pubs as the Connection for the Recordset DTC.



3. Right-click the recordset DTC and select Properties.



4. On the General tab, select SQL Statement and add the following SQL statement:

   
      SELECT * FROM authors WHERE au_lname = ? 




5. Select the Parameters tab. You will see an entry for the parameter in your SQL Statement. In the Value field, enter Request.Form("Textbox1"). (When navigating through the results using the grid DTC, the Textbox1.value is populated after the Recordset parameters are set, and therefore we will use Request.Form("Textbox1") to get the parameter value).



6. Click OK.



4. Drag a button DTC onto the body of the page.



• Right-click the button DTC and select Properties.



• Set the Caption to Show Authors.



• Click OK.



5. Drag a grid DTC onto the body of the page.



• Right-click the grid DTC and select Properties.



• On the Data tab, set the Recordset property to Recordset1.



• In the Available Fields section, select au_id, au_lname, and au_fname.



• Click OK.

Step 3: Adding Code to the ASP Page

1. Switch to the Script Outline Window (From the View menu, select Other Windows, then select Script Outline and expand the Button1 node under Server Objects and Events.



2. Double-click the onclick node.



3. Add the following code to the Button1_onclick() event handler in your ASP page:




Sub Button1_onclick()
 
  If Recordset1.isOpen() Then Recordset1.Close
 
  Recordset1.Open 

End Sub  

4. Save the ASP page.

Step 4: Running the Sample

1. Right-click on the ASP page and select View in Browser.



2. In the textbox, type Greene (or any other valid author's last name), then click Show Authors.



3. The page will display the author_id of all authors with a last name of Greene (or whatever author name you entered).

REFERENCES

The procedure for calling a parameterized query against Microsoft Access with recordset DTC is different and uses a data command to specify parameters.

See the following Microsoft Knowledgebase article for a step-by-step procedure for calling parameterized query against Access:

Q197326 HOWTO: Call a Parameterized Query to Access with Recordset DTC

Additional query words:

Keywords : kbVisID600 kbDSupport
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbhowto