Searching with multiple search parameters (ASP.NET)

If the search page submits more than one search parameter to the server, then you must write a SQL query and use the search parameters in SQL variables.

To search for records in a database using SQL:

1. Open the results page in Dreamweaver, and then create a new DataSet by opening the Bindings panel (Window > Bindings), clicking the Plus (+) button, and selecting DataSet from the pop-up menu.
2. Make sure the advanced DataSet dialog box appears.

   If the simple dialog box appears instead, switch to the advanced dialog box by clicking the Advanced button.

3. Enter a name for the DataSet and select a connection.

   The connection should be to a database containing data you want the user to search.

4. Enter a Select statement in the SQL text area.

   Make sure the statement includes a WHERE clause with question mark (?) placeholders for the search parameters. The following example contains two placeholders:

   SELECT EMPLOYEEID, FIRSTNAME, LASTNAME, DEPARTMENT, EXTENSION
   FROM EMPLOYEE WHERE LASTNAME LIKE ?
   AND DEPARTMENT LIKE ?
   

   For help on SQL syntax, see SQL Primer.

5. Give the placeholders the values of the search parameters by clicking the Plus (+) button in the Parameters area and entering the parameter’s name, type, and value.

   The parameters must be listed in the same order they appear in the SQL statement.

   In the Name text box, enter any valid parameter name. The name cannot contain any spaces or special characters.

   In the Type pop-up menu, select a data type. For example, if the parameter will hold text, select WChar.

   In the Value box, enter the server variable that will contain the parameter value. For example, if the name of the form control on the search page is txtCity, then a server variable called Request.Form("txtCity") will be created and a value stored in it.

   You can also enter a more complete expression that specifies a default value in case the server variable doesn’t exist. For example, if searching a Microsoft Access database, you can use % as a default value. The following expression checks to see if the server variable Request.Form("txtCity") exists. If the variable exists (that is, if it’s not equal to nothing), the expression returns the variable’s value; if it doesn’t exist, the expression returns the default value of %.

   (IIf((Request.Form("txtCity") <> Nothing),
   Request.Form("txtCity"), "")) + "%"
   

   For more information, see a Visual Basic or C# language reference.

6. If you want to, click Test to create an instance of the DataSet using the default variable values.

   The default values simulate the values that would otherwise have been returned from the search page. Click OK to close the test DataSet.

7. If you’re satisfied with the DataSet, click OK.

   Dreamweaver inserts the SQL query in your page.

The next step is to display the search results in a DataGrid. See Displaying the results in a DataGrid.