An ASP.NET Search Engine with MS Access for optional search criteria

4.65 (23 votes)

Creating a search interface for one user-supplied value is pretty straightforward: a TextBox for input, a Button and some SQL that searches one or more fields where the values are LIKE '%' + @searchterm + '%' is all that is needed. While it works, this approach is not very flexible. For example, what if you wanted to search for an Employee whose last name contains "a", and don't want those where the first name or city contains "a"? The traditional solution to this is to dynamically construct the SQL statement based on what values were passed by the user. However, building the SQL string can get repetitive, boring and messy. Here is a cleaner way to allow the user to narrow down searches across multiple criteria with the use of optional parameters, and just 3 lines of programming code.

For this example, I will use the Northwind sample database, and allow users to search for employees by first name, last name, job title and city. All I need is a set of TextBoxes, a Button, a GridView for the results, and an AccessDataSource to handle data access. First, the search form, which is styled using a little bit of CSS to make it line up nicely without using tables:

<div class="row">
  <span class="label">First name: </span>
  <asp:TextBox ID="txtFirstName" runat="server" />
<div class="row">
  <span class="label">Last name: </span>
  <asp:TextBox ID="txtLastName" runat="server" />
<div class="row">
  <span class="label">Job Title: </span>
  <asp:TextBox ID="txtTitle" runat="server" />
<div class="row">
  <span class="label">City: </span>
  <asp:TextBox ID="txtCity" runat="server" />
<div class="row">
  <span class="label"> </span>
  <asp:Button ID="Button1" runat="server" Text="Search" />

And the CSS:

<style type="text/css">
body, input{font-family: Arial, Helvetica, sans-serif;}
body {font-size:76%;}
.label{float:left; width:80px;text-align:right;padding-right:5px;}

Now the GridView. For our purposes, a very basic GridView will do, with Paging enabled and AutoGenerateColumns set to true. Its DataSourceID is set to an AccessDataSource control that will be added afterwards:

  PageSize="5" />

The key to the AccessDataSource control is the SelectCommand. We have potentially any number of 4 inputs that will pass values to be searched on. It is highly likely that some of these values will be NULL, in that nothing was entered into the relevant TextBox in the search form, and this needs to be taken care of in the SQL. This is done as follows:

SELECT FirstName, LastName, Title, City, HomePhone 
FROM Employees 
(@FirstName IS NULL OR FirstName LIKE '%' +  @FirstName + '%') AND 
(@LastName IS NULL OR LastName LIKE '%' + @LastName + '%') AND  
(@Title IS NULL OR Title LIKE '%' + @Title + '%') AND  
(@City IS NULL OR City LIKE '%' + @City + '%');

The trick to this is that if a parameter value is NULL, such as @FirstName (ie the value that was entered into the txtFirstName TextBox), the SQL will return all rows. If a value was entered, the OR part of the SQL will kick in instead, and use the supplied value as a filter in the LIKE clause. The same is true of the other 3 fields, and the use of AND between them will ensure that as more criteria are added, the results are narrowed down as a consequence.

There are just two more things to do. The first is to add the parameters to the AccessDataSource's parameters collection - in the order in which they are used in the SQL - essential with OleDb parameters:

  <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" Type="String" />
  <asp:ControlParameter ControlID="txtLastName" Name=""LastName" Type="String" />
  <asp:ControlParameter ControlID="txtTitle" Name="Title" Type="String" />
  <asp:ControlParameter ControlID="txtCity" Name="City" Type="String" />

And then we need to change the CancelSelectOnNullParameter property of the AccessDataSource to false. This is true by default, and cancels the DataSource control's Selecting event during Page_Load if NULL parameters are passed, which is what you would normally want. But in this instance, you are allowing users to pass NULL values to parameters, so it needs to be changed. This can be done simply is the Properties panel for the DataSource control.

Did I say just two more things to do? Now that we have made the change to CancelSelectOnNullParameters, there is still a final step required. As I said earlier, if a parameter is NULL, then all rows are returned, unless other parameters have values to force the OR part of the SQL to come into play. Therefore if all parameters have a NULL value, all rows from the table will be returned. This is what will happen on first page load, as of course, users have not yet been presented with the search form. this will result in the GridView being populated with the entire table. Therefore, the AccessDataSource's Selecting event needs to be cancelled, unless the form is being posted back.

To do this, select the AccessDataSource control in Design View, and hit F4 to bring up the Properties panel. Then locate the lightning bolt icon.

Click it once and then locate the Selecting event in the list. Double click that and an event handler will automatically be created in your code-behind or script block. Within that, add code to check that the page hasn't been posted back, and cancel the Select operation:

if (!IsPostBack)
  e.Cancel = true;
If Not IsPostBack Then
  e.Cancel = True
End If

So the full code for the C# version of the AccessDataSource appears as follows. The VB version differs only in that it does not contain an OnSelecting attribute:

  SelectCommand="SELECT FirstName, LastName, Title, City, HomePhone 
    FROM Employees WHERE  
    (@FirstName IS NULL OR FirstName LIKE '%' +  @FirstName + '%') AND 
    (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%') AND  
    (@Title IS NULL OR Title LIKE '%' + @Title + '%') AND  
    (@City IS NULL OR City LIKE '%' + @City + '%');" 
	  Type="String" />
	  Type="String" />
	  Type="String" />
	  Type="String" />

And there you have it. While this isn't quite up to the standards of a Google or MSN Live, this approach will provide a pretty powerful search engine for your site. It uses parameters to protect against Sql Injection, and only requires 3 lines of programming code in total.

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 28750


- annie

Greate article. It clarifies my thoughts on how to create a search result page against an Oracle DB. But I have a problem querying data:
SelectCommand="SELECT * FROM "ROLODEX" WHERE (("ROLODEX#" IS NULL OR "ROLODEX#" = :column1) AND ("LNAME" IS NULL OR "LNAME" LIKE '%' || :LNAME || '%') AND ("FNAME" IS NULL OR "FNAME" LIKE '%' || :FNAME || '%'));"
That is the SQL I wrote for query data from an Oracle DB. But visual studio does not like it. I modified the format of your code to better fit Oracle sytax. Not sure what I should do with it. Seems like if it is something Oracle likes, then visual stuido does not. Any idea how to make to work?

Thank you very much,


- rajendra

its very nice sug

- Dumisile


- Durgesh

What if you only define First and Last Name and leave rest of the field blank. what it will return?

Recent Comments

Satyabrata Mohapatra 23/07/2017 16:43
In response to Razor Pages - The Elevator Pitch
@Dale Severin You can continue to build apps using web pages....

Satyabrata Mohapatra 23/07/2017 16:40
In response to Sending Email in Razor Pages
Thanks for sharing...learned a lot...

Gfw 22/07/2017 11:53
In response to Sending Email in Razor Pages
Question... Does System.Net.Mail support SSL?...

Dale Severin 20/07/2017 03:38
In response to Razor Pages - The Elevator Pitch
I work with razor web pages extensively. I appreciate the rapid development it permits me to I am as...

Obinna Okafor 14/07/2017 01:19
In response to Routing in Razor Pages
Thank you, Mike. Good post....

Satyabrata Mohapatra 11/07/2017 16:02
In response to Routing in Razor Pages
Very powerful routing system!!...

Cyrus 05/07/2017 03:41
In response to Razor Pages - Getting Started With The Preview
How can I trim packages and services as much as possible to use just razor pages? I don’t want to to...

Harris Boyce 04/07/2017 04:17
In response to Razor Pages - The Elevator Pitch
As a developer of a couple "trivial" web pages applications used by non-profits that wouldn't have I...

Cyrus 28/06/2017 20:25
In response to Razor Pages - Getting Started With The Preview
.net core 2.0 preview 2: <a...

ojorma 17/06/2017 09:24
In response to Razor Pages - The Elevator Pitch
Finally I can say goodbye to webforms...