How To Call a Parameterized Query to SQL Server with Recordset DTC (232208)

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

This sample uses the Authors table of the Pubs database in SQL Server. Before you begin, open the ODBC Administrator from Control Panel and create a System DSN to access the Pubs database on your SQL Server.

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 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. Select the Implementation tab. Uncheck the option Automatically Open the Recordset.
   7. Click Close.
3. Drag a textbox DTC onto the body of the page.
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).