Visual InterDev

Customized Query Sample

See Also      Tasks

This sample shows how to get user input to create a custom query. The user selects a field from the table and text to search for.

Behind the Scenes

This sample uses one file: Query.asp. After the user enters the search criteria the results are displayed using a data-bound grid.

To create the dynamic query a function must be called to set the SQL statement for the recordset. The function first determines if the user selected CONTAINS or BEGINS WITH from the first drop-down and then builds the SQL statement.

The new SQL statement is sent to the recordset and the new recordset is opened. The function below shows the code that is executed once the Search button is clicked:

   function Button1_onclick() {
      if (Listbox2.getValue(Listbox2.selectedIndex) == "Contains") {
         newSQL = "Select * FROM Customers WHERE (" + Listbox1.getValue(Listbox1.selectedIndex) + " LIKE '%" + Textbox1.value + "%')";
      }   
      else {
         newSQL = "Select * FROM Customers WHERE (" + Listbox1.getValue(Listbox1.selectedIndex) + " LIKE '" + Textbox1.value + "%')";
      }      
      DTCRecordset1.setSQL(newSQL);
      DTCRecordset1.open();
   }

After the new recordset is opened the results are displayed using a data-bound grid. The grid displays the data in pages of ten records each.