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: 25320

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

Manas 11/27/2014 5:30 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, Thank you for awesome article. My concern is it might impact website if we use or is...

priya 11/26/2014 6:50 PM
In response to Create PDFs in ASP.NET - getting started with iTextSharp
very nice.....its save my time...

ransems 11/24/2014 12:29 AM
In response to Adding A Controller
Love the article. I dislike that the world thinks c# articles are the way to go. Love the VB, keep...

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...