<% '******************************************************* '* ASP 101 Sample Code - http://www.asp101.com/ * '* * '* This code is made available as a service to our * '* visitors and is provided strictly for the * '* purpose of illustration. * '* * '* http://www.asp101.com/samples/license.asp * '* * '* Please direct all inquiries to webmaster@asp101.com * '******************************************************* %> <% ' 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 %>

Search our sample db by first or last name. (% returns all)

[Try 'er in' for an example]

<% 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 %> <% Do While Not rstSearch.EOF %> <% rstSearch.MoveNext Loop %>
Name Sales
<%= rstSearch.Fields("first_name").Value %> <%= rstSearch.Fields("last_name").Value %> <%= rstSearch.Fields("sales").Value %>
<% ' Close our recordset and connection and dispose of the objects rstSearch.Close Set rstSearch = Nothing cnnSearch.Close Set cnnSearch = Nothing End If %>