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:
Last Updated:
Posted by:
Total Views to date: 26860

4 Comments

- 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

- rajendra

its very nice sug

- Dumisile

thanx

- Durgesh

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

Recent Comments

Manie Verster 5/28/2015 9:39 AM
In response to MVC 5 with EF 6 in Visual Basic - Implementing Inheritance
Sorry Mike but on my project when run it does not show the hire date and/or enrollment date. I the I...

Joseph Casey 5/27/2015 3:17 PM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Probably should have read the title. Couldn't use it with Visual Studios 2013. =[...

jean 5/27/2015 8:41 AM
In response to iTextSharp - Drawing shapes and Graphics
hey Mike, I read your arcticle about drawing on PDF with ITextSharp, it was really interesting !...

Alexandros L 5/26/2015 10:50 PM
In response to Entity Framework 6 Recipe - Hierarchical Data Management
You are a live saver.. thank you so much......

saurabh rao 5/26/2015 1:03 PM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Hey Mike ! Great article ...the code worked flawlessly for me. Didn't have any issues executing it ....

Vijay 5/25/2015 2:12 PM
In response to How To Send Email In ASP.NET MVC
Thanks...

RR 5/25/2015 8:58 AM
In response to Web Pages - Efficient Paging Without The WebGrid
@MIke.. is it possible to combine pagination and filter (change category ) in the script? Thanks...

Irfan Khan 5/25/2015 6:19 AM
In response to Highlighting Keywords Found In Search Results
This is truely helpful. Thank you for the brilliant tip....

Manoj Kulkarni 5/25/2015 4:46 AM
In response to Custom TagHelpers in ASP.NET MVC 6
Thank you for nice article. Really helpful....

Sean 5/21/2015 4:20 AM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Thank you for the tutorial. Saving to the database works, but the save FilePath is not working. It...