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

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>
<div class="row">
  <span class="label">Last name: </span>
  <asp:TextBox ID="txtLastName" runat="server" />
</div>
<div class="row">
  <span class="label">Job Title: </span>
  <asp:TextBox ID="txtTitle" runat="server" />
</div>    
<div class="row">
  <span class="label">City: </span>
  <asp:TextBox ID="txtCity" runat="server" />
</div>
<div class="row">
  <span class="label"> </span>
  <asp:Button ID="Button1" runat="server" Text="Search" />
</div>

And the CSS:

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

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:

<asp:GridView 
  ID="GridView1" 
  runat="server" 
  AutoGenerateColumns="True" 
  DataSourceID="AccessDataSource1" 
  AllowPaging="True" 
  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 
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 + '%');

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:

<SelectParameters>
  <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" />
</SelectParameters>

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:

[C#]
if (!IsPostBack)
{
  e.Cancel = true;
}
[VB]
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:

<asp:AccessDataSource 
  ID="AccessDataSource1" 
  runat="server" 
  DataFile="~/App_Data/Northwind.mdb"
  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 + '%');" 
  CancelSelectOnNullParameter="False" 
  OnSelecting="AccessDataSource1_Selecting">
  <SelectParameters>
	<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" />
  </SelectParameters>
</asp:AccessDataSource>

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.

Date Posted: Monday, November 12, 2007 3:15 PM
Last Updated: Monday, August 23, 2010 9:34 AM
Posted by: Mikesdotnetting
Total Views to date: 25559

4 Comments

Monday, January 25, 2010 11:28 PM - 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,

Annie

Wednesday, May 1, 2013 1:56 PM - rajendra

its very nice sug

Thursday, June 20, 2013 10:16 AM - Dumisile

thanx

Monday, July 22, 2013 11:33 PM - Durgesh

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

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Allen Michaels 12/17/2014 4:37 PM
In response to Cascading DropDownLists with jQuery and ASP.NET
Fantastic thank you so much!...

Emily 12/17/2014 12:36 PM
In response to Parameterized IN clauses with ADO.NET and LINQ
Thanks, very helpful!!!! Can i use this for multiple in's ????? IN (.....) and IN(...) and IN...

sss 12/16/2014 3:06 PM
In response to Solving the Operation Must Use An Updateable Query error
good...

Gjuro 12/15/2014 10:30 PM
In response to Examining the Edit Methods and Edit View
You have one fromr (and it should be from, I suppose). :-)...

Gjuro 12/15/2014 10:27 PM
In response to Adding Search
Hi, thnx for all this C#->VB translations. Yet, the following code block is (slightly) in error it a...

Scot 12/14/2014 1:39 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Thanks,Mike I found solution....

Gjuro 12/13/2014 10:52 PM
In response to Accessing Your Model's Data from a Controller
The article mentions "Creating an Entity Framework Data Model for an ASP.NET MVC Application" (at is...

Samuel 12/13/2014 8:40 AM
In response to Displaying The First n Characters Of Text
I have failed to use the extension because it throws an error that it doesn't recognise the chop be...

Ignas 12/12/2014 5:11 PM
In response to Cleaner Conditional HTML Attributes In Razor Web Pages
Any suggestions for Html Helper elements with HtmlAttributes, when you need to conditionally set it...

Gautam 12/11/2014 8:50 PM
In response to Validation In Razor Web Pages 2
Hi Mike Is this required for V3, non html helper input...