ASP 101 - Active Server Pages 101 db_search_multi.aspx
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
	Sub btnSearch_OnClick(sender as Object, e as EventArgs)
		Dim myConnection As SqlConnection
		Dim myCommand As SqlCommand
		Dim strSearch As String
		Dim arrTerms() As String
		Dim strOperator As String
		Dim strSQL As String
		Dim I As Integer
		' Get Search Text
		strSearch = txtSearch.Text.Trim
		' If our search text isn't empty build our SQL Query and execute it.
		If strSearch.Length > 0 Then
			' Clean up single quotes in our query
			strSearch = strSearch.Replace("'", "''")
			' Split query into individual terms
			arrTerms = strSearch.Split()
			' Determine multi-term search type (AND/OR)
			If ddlSearchType.SelectedValue = "All Terms" Then
				strOperator = "AND"
			Else
				strOperator = "OR"
			End If
			' Set up our SQL query text
			strSQL = "SELECT first_name + ' ' + last_name AS Name, Sales " _
			 & "FROM sample WHERE "
			' Loop over terms including each one in our SQL query text
			For I = arrTerms.GetLowerBound(0) To arrTerms.GetUpperBound(0)
				' I'm searching both first and last name fields so
				' I need to include both for each search term.
				strSQL = strSQL & "(   last_name  LIKE '%" & arrTerms(I) & "%'"
				strSQL = strSQL & " OR first_name LIKE '%" & arrTerms(I) & "%')"
				' Use the appropriate operator to combine the individual search terms
				If I < arrTerms.GetUpperBound(0) Then
					strSQL = strSQL & " " & strOperator & " "
				End If
			Next I
			' Sort by last name
			strSQL = strSQL & "ORDER BY last_name;"
			' Set up our connection to the database
			myConnection = New SqlConnection("Data Source=10.2.2.133;" _
			 & "Initial Catalog=samples;User Id=samples;Password=password;" _
			 & "Connect Timeout=15;Network Library=dbmssocn;")
			' Open our connection to the database
			myConnection.Open()
			' Create new command object and pass it our SQL query
			myCommand = myConnection.CreateCommand()
			myCommand.CommandText = strSQL
			' Set the DataGrid's data source and call DataBind()
			dgSearch.DataSource = myCommand.ExecuteReader()
			dgSearch.DataBind()
			' Close our connection to the database
			myConnection.Close()
		Else
			txtSearch.Text = "Enter Search Here"
		End If
	End Sub
</script>
<html>
<head>
  <title>ASP.NET Multi-Term Database Search Sample</title>
</head>
<body>
<form id="myForm" runat="server" defaultbutton="btnSearch">
<p>Search our sample db by first or last name.  (% returns all)</p>
<asp:TextBox id="txtSearch" runat="server" />
<asp:DropDownList id="ddlSearchType" runat="server">
	<asp:ListItem>Any Term</asp:ListItem>
	<asp:ListItem>All Terms</asp:ListItem>
</asp:DropDownList>
<asp:Button id="btnSearch" runat="server"
	Text    = "Search"
	OnClick = "btnSearch_OnClick"
/>
<p>[Try 'er in' for an example]</p>
<!-- Plain vanilla DataGrid... format it as you like. -->
<asp:DataGrid id="dgSearch" runat="server"
	HeaderStyle-Font-Bold="True"
/>
</form>
<hr />
<p>
Click <a href="http://www.asp101.com/samples/db_search_multi_aspx.asp">here</a>
to read about and download the source code.
</p>
</body>
</html>