Home > Data Access in Web Forms > Selecting Information From a Database > Retrieving Data From User Specified Queries

---

Here we explore simple yet powerful methods to retrieve and display one or more database records based on a value posted from user input.

This is made possible by simply providing the input variable to the SQL query statement that obtains the data set.

dynamic_query_sql.aspx Run Sample | View Source

Basically, the logic is implemented as follows:

1. A Page_Load event handler runs a simple procedure that
   1. returns a list of all the subjects in the data source, and
   2. binds the result set to the HtmlSelect control named mySelect.

   NOTE: This procedure runs only when the page’s IsPostBack property returns false, which means only the first time the page loads.

   protected void Page_Load ( object src, EventArgs e ) {
      if ( !IsPostBack ) {
         string query = "SELECT DISTINCT Subject FROM Books";
         mySelect.DataSource = fetchReader ( query );
         mySelect.DataBind ( );
      }
   }

   Note use of the DISTINCT keyword in the SQL statement, to display only unique values ( no duplicates ) from the given field.

   string query = "SELECT DISTINCT Subject FROM Books";

2. When the user selects a subject and posts the form, the submit handler getBooks receives control and runs a simple procedure that
   1. returns a list of all the books that belong to the selected subject, and
   2. binds the result set to the DataList control named myDataList.

   public void getProducts ( object src, EventArgs e ) {
      string query = "SELECT * FROM Books WHERE Subject='" + mySelect.Value + "'";
      myDataList.DataSource = fetchReader ( query );
      myDataList.DataBind ( );
   }

   Note that both the above event handlers call an external method fetchReader that does the actual query and obtains the result set from the source.

   This method is used extensively in this workshop; it is a generic reusable method that returns a DataReader based on the query statement that is passed on to it.

   Details of this is described in Calling Data Functions in Code Behind.

3. When the user selects a book and posts the form ( by clicking on the title link or on the book image ), the book details page receives control and in turn runs a simple procedure that
   1. returns the record of the selected item, and
   2. binds the result set to the DataList control named myData.

 Show me 

dynamic_query_oledb.aspx Run Sample | View Source

Basically, the logic is implemented similarly, as in the above example:

1. A Page_Load event handler runs a simple procedure that
   1. returns a list of all the product categories in the data source, and
   2. binds the result set to the HtmlSelect control named mySelect.

   protected void Page_Load ( object src, EventArgs e ) {
      if ( !IsPostBack ) {
         string query = "SELECT DISTINCT Type FROM Products";
         mySelect.DataSource = fetchReader ( query, "gear" );
         mySelect.DataBind ( );
      }
   }

2. When the user selects a category and posts the form, the submit handler getProducts receives control and runs a simple procedure that
   1. returns a list of all the products that belong to the selected category, and
   2. binds the result set to the DataList control named myDataList.

   public void getProducts ( object src, EventArgs e ) {
      string query = "SELECT * FROM Products WHERE Type='" + mySelect.Value + "’";
      myDataList.DataSource = fetchReader ( query, "gear" );
      myDataList.DataBind ( );
   }

   Likewise, both the above event handlers call an external method fetchReader that does the actual query and obtains the result set from the source.

   For OleDb data, this method returns a DataReader based on two arguments that are passed on to it: the query statement, and the database with which to issue the query.

   Details of this is described in Calling Data Functions in Code Behind.

3. When the user selects a product and posts the form ( by clicking on the product link or on the product image ), the product details page receives control and in turn runs a simple procedure that
   1. returns the record of the selected item, and
   2. binds the result set to the DataList control named myData.

 Show me 

Selecting Information From a Database   Passing Variables to the Select Query