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

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?

Recent Comments

Justin Kusuma 7/24/2015 3:38 AM
In response to Posting Data With jQuery AJAX In ASP.NET Razor Web Pages
Hi Mike, thanks much for sharing such an article :) Really help me a lot... further, I'd like to...

Michael Easterbrook 7/22/2015 5:35 PM
In response to Inline Razor Syntax Overview
I removed the @ symbols and I am still getting the same error. It only occurs when I have an "if" a...

Sujay 7/22/2015 1:36 PM
In response to ASP.NET MVC, Entity Framework, One-to-Many and Many-to-Many INSERTS
can you explain how to link two tables so that it forms many to many relationship?(Article and...

Max G 7/21/2015 9:29 PM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi, I've opted for this solution in one of my applications but i've found that the apppool is and I...

Michael Easterbrook 7/20/2015 4:31 PM
In response to Inline Razor Syntax Overview
When I have the following code: @foreach (var procRow in procRowDecade) { if (@procRow[3] +...

Shanice 7/18/2015 10:58 PM
In response to A Better Way To Export Gridviews To Excel
Hi. I'm working with mvc. I need to add the above code in the business logic layer, however the...

Matt 7/18/2015 6:29 PM
In response to Nested Layout Pages with Razor
Cheers sir, nice explanation :)...

Keshavan 7/17/2015 9:06 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, I have followed exactly as illustrated in blog, I get error "StdSchedulerFactory.cs" not...

Paul Thiel 7/16/2015 5:17 PM
In response to ASP.NET 5 By Numbers
Comments Below: "The new version of ASP.NET is called ASP.NET 5. It is a framework for developing...

saket singh 7/16/2015 8:42 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
hi Mike, great tutorial on Quartz.net , but i have One Problem , Everything is working fine as as...