<%
' Declare variables
Dim strURL      ' The URL of this page
Dim cnnSearch   ' ADO connection
Dim rstSearch   ' ADO recordset
Dim strSQL      ' The SQL Query we build on the fly
Dim strSearch   ' The text being searched for
Dim arrTerms    ' An array of the terms in the search query
Dim strOperator ' Are we using AND or OR
Dim I           ' Looping var
' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")
' Retreive the list of terms being searched for.
strSearch = Request.QueryString("search")
' FIgure out if the user requested an "AND" which only returns
' results which match all terms or an "OR" which returns results
' which match any term.
If LCase(Request.QueryString("operator")) = "and" Then
	strOperator = "AND"
Else
	strOperator = "OR"
End If
%>
<p>Search our sample db by first or last name.  (% returns all)</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<select name="operator">
	<option value="or" <% If strOperator="OR"  Then Response.Write(" selected=""selected""")%>>Any Term</option>
	<option value="and"<% If strOperator="AND" Then Response.Write(" selected=""selected""")%>>All Terms</option>
</select>
<input type="submit" />
</form>
<p>[Try 'er in' for an example]</p>
<%
If strSearch <> "" Then
	' Create an ADO Connection to connect to the sample database.
	Set cnnSearch = Server.CreateObject("ADODB.Connection")
	' This line is for the Access sample database:
	'cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database.mdb") & ";"
	' We're actually using SQL Server so we use this line instead:
	cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.2.133;" _
		& "Initial Catalog=samples;User Id=samples;Password=password;" _
		& "Connect Timeout=15;Network Library=dbmssocn;"
	' Double up single quotes
	strSearch = Replace(strSearch, "'", "''")
	' Split search phrase into individual terms
	arrTerms = Split(strSearch, " ")
	' Build our query based on the input.
	strSQL = "SELECT last_name, first_name, sales FROM [sample] WHERE "
	' Loop over terms including each one.
	For I = LBound(arrTerms) to UBound(arrTerms)
		' I'm searching both first and last name fields so
		' I need to do 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 < UBound(arrTerms) Then 
			strSQL = strSQL & " " & strOperator & " "
		End If
	Next 'I
	' Order by last_name
	strSQL = strSQL & " ORDER BY last_name;"
	'Response.Write strSQL
	' Execute the query
	Set rstSearch = cnnSearch.Execute(strSQL)
	' Display the results
	%>
	<table border="1">
	<tr>
	<th>Name</th>
	<th>Sales</th>
	</tr>
	<%
	Do While Not rstSearch.EOF
		%>
		<tr>
		<td><%= rstSearch.Fields("first_name").Value %> <%= rstSearch.Fields("last_name").Value %></td>
		<td><%= rstSearch.Fields("sales").Value %></td>
		</tr>
		<%
		rstSearch.MoveNext
	Loop
	%>
	</table>
	<%
	' Close our recordset and connection and dispose of the objects
	rstSearch.Close
	Set rstSearch = Nothing
	cnnSearch.Close
	Set cnnSearch = Nothing
End If
%>