Selecting records based on user input

Using Parameterized Queries

1. We will begin with a new asp file that has been added to the project. The file is named Customers.asp and will be used to implement a parameterized query.
   The Recordset Design-Time Control provides the capability to use paramaterized queries. You already know how to add a Recordset DTC to a page, so we've already added one to Customers.asp. But, you will need to set the properties to add the parameterized query. Right-click the control to display the menu.
2. Select Properties from the menu.
3. Here is the Recordset Properties dialog. There are two things you need to do in the General tab. First, give the control a name. Enter CustRecords in the Name box. Then, in the Source of Data section, select SQL Statement. This will let you define your own SQL statement rather than using the controls default.
4. After selecting SQL Statement, the text box below is enabled. Now you can enter the SQL statement you want the recordset control to use. Enter a SQL statement that will return customer data on customers who live in a state entered by the user. Use the following statement in the text box:

SELECT CustNo, LName, FName, ADDR1, ADDR2, 
City, ST, Zip FROM CustTable WHERE ST = ?

5. The Implementation tab is now displayed. By default, the recordset is automatically opened. You don't want this to happen when you're using a parameterized query because the complete query isn't known until the user enters a value. Instead, you want the recordset opened only after the parameter is set. Notice in the bottom half of the Implementation tab that "Automatically open the Recordset" is checked. Click the checkbox to turn this off.
6. After turning that off, you can then associate the user-entered value, or parameter, with the placeholder in the SQL statement. You do this in the Parameters tab. Click the Parameters tab.
7. The Parameters tab is where the user-entered values are associated with placeholders. The parameters are entered in the Value column. They can be literals, variables, or expressions. A variable will be used in this example, and its name is entered in the Value column. We will use txtState as our variable name. Enter txtState in the Value column. Then click Close.
8. The Recordset DTC is set up now for the parameterized query. You might be wondering how the user sends the parameter to the query. Typically, the user enters the parameter into an HTML form and then clicks a “SUBMIT” or similar button. The value is then sent via HTTP to the server. The receiving asp file on the server, Customers.asp in our case, must have code to accept the value. Enter the following code into the Customers.asp in the space above the recordset DTC.

<%
dim txtState
text entry txtState = Request("state")
CustRecords.open()
%>

9. Everything is set up. So, now you can take it for a test drive. There is one more thing, though. This simulation uses an HTML page for the user to enter the value for the state and then clicks search to run the query. The value is sent to Customers.asp and the database is searched. After the search is complete, Customers.asp displays the results. During development and testing you would most likely have the HTML page open in your browser. Then you can enter the state and click the search button just as your users will. For the purpose of this simulation we have done that for you. So, just click Next to continue.
10. Here is the HTML page. Enter AZ for the state and click search.
11. Here are the results. Two customers live in Arizona! This is the end of the simulation.