The ASP Emporium - Simple Database Search Engine

<% @ Language = VBScript %> <% Option Explicit With Response .Buffer = True .Expires = 0 .Clear End With '#################### START CLASS DEFINITION ######################## Class DBSearchEngine '#################### START DOCUMENTATION ######################## 'purpose: ' searches the ASP Emporium "examples" table ' for various criteria to illustrate how to ' search a table in a database. ' ' 'version information: ' 2.0 4/25/2001 ' - new class interface ' - searches more fields ' - updated to reflect the newest "examples" ' table in use at the ASP Emporium ' ' 1.5 8/2000 ' no longer uses a global.asa ' ' 1.0 4/2000 ' initial release ' ' 'syntax: ' Set variable = New DBSearchEngine ' ' 'properties: ' DBConnectionString ' String. Required. DB Connection String. ' ' DBConnectionAccount ' String. Optional. DB Account Name. ' ' DBConnectionAcctPassword ' String. Optional. DB Account Password. ' ' bNoFieldsToSearch ' Boolean. Optional. Returns boolean indicating ' whether or not fields were selected to search. ' ' SearchType ' String. Required. Sets/returns the type of the ' search... Acceptable values are "AND" or "OR" ' ' 'methods: ' SearchTitles ' Void. Calling this method will ' search any terms entered against titles. ' Must be called before the search method. ' ' SearchDescriptions ' Void. Calling this method will ' search any terms entered against descriptions. ' Must be called before the search method. ' ' Search(keywords) ' String. Required. Calling this method searches ' the string entered in the keywords argument ' against the ASP Emporium examples table. ' ' Count ' String. Returns the count of records ' found after the search method is called. ' Must be called after the search method. ' ' Version ' String. Returns the version of the class that ' you are running. ' ' 'installation: ' 1.) edit this file ' - change db connection info at the bottom of this page ' - remove all SSI #include references ' ' 2.) get the asp emporium test database or the scripts for sql server ' http://www.aspemporium.com/aspEmporium/downloads/myData_db.asp ' ' 3.) place this example and the db on your server... ' ' 4.) execute the asp page with a browser ' ' ' ' '#################### END DOCUMENTATION ######################## 'private variables Private hTmpCount, bSearchTitles, bSearchDescriptions 'public variables become properties of the class Public DBConnectionString Public DBConnectionAccount Public DBConnectionAcctPassword Public bNoFieldsToSearch Public SearchType Public Property Get Version Version = "2.0" End Property 'public methods Public Sub SearchTitles bSearchTitles = true End Sub Public Sub SearchDescriptions bSearchDescriptions = true End Sub Public Function Search(ByVal sKeyword) ' ado constant declaration. do not modify next line. Const adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001 Dim strSQL, objConn, objRs, strTemp strTemp = "" 'this is a nested call to 2 functions... if you start in the 'middle, first the MakeKeywordArray function is called 'and then the sqlString function is called using the returned 'values from MakeKeywordArray... strSQL = sqlString( MakeKeywordArray( sKeyword ) ) 'open db Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open DBConnectionString, DBConnectionAccount, DBConnectionAcctPassword Set objRs = Server.CreateObject("ADODB.Recordset") objRs.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText 'retrieve recordcount hTmpCount = objRs.RecordCount if objRs.BOF then strTemp = "

No Matching Records

" else 'start at first record... objRs.MoveFirst 'loop through all records Do While NOT objRs.BOF AND NOT objRs.EOF ' call the function HTMLdisplay to properly ' format a valid entry for the results 'build return string, with nicely 'formatted html (not) strTemp = strTemp & HTMLdisplay(objRs) objRs.MoveNext Loop end if 'close em up... objRs.Close objConn.Close Set objRs = Nothing Set objConn = Nothing 'return the built string of results... Search = strTemp End Function Public Function Count Count = hTmpCount End Function 'private class routines Private Sub Class_Initialize() 'class_initialize event is best used to set 'up default values for class scoped variables bSearchTitles = false bSearchDescriptions = false End Sub Private Function EscapeApostrophe(ByVal toEscape) 'make "my dog's fleas" become "my dog''s fleas", 'escaping the apostrophe character because 'it's meaning is special in TSQL... EscapeApostrophe = replace(toEscape, "'", "''") End Function Private Function sqlString(ByVal keywords) ' this function creates an sql string based on various criteria Dim i 'the base string sqlString = "SELECT * FROM examples " 'check for any keywords entered... if IsArray(keywords) then if NOT bSearchTitles AND NOT bSearchDescriptions then ' repair the sql string if a client forgets to ' select a search field sqlString = TRIM(sqlString) & ";" bNoFieldsToSearch = True 'leave Exit Function end if ' don't forget the where if we're still here sqlString = sqlString & "WHERE " ' make the criteria part of the statement based on the ' array of keywords and the chosen search fields for i = 0 to UBOUND(keywords) if bSearchTitles then sqlString = sqlString & _ "exampleName LIKE '%" & EscapeApostrophe(keywords(i)) & _ "%' " & SearchType & " " next if bSearchTitles AND bSearchDescriptions then 'need to clean the sql string and remove 'the last AND or OR to avoid an error... sqlString = Left(sqlString, Len(sqlString)-4) sqlString = TRIM(sqlString) sqlString = sqlString & " OR (" end if for i = 0 to UBOUND(keywords) if bSearchDescriptions then sqlString = sqlString & _ "exampleDesc LIKE '%" & EscapeApostrophe(keywords(i)) & _ "%' " & SearchType & " " next sqlString = Left(sqlString, Len(sqlString)-4) if SearchType = "OR" then sqlString = sqlString & " " if bSearchTitles AND bSearchDescriptions then sqlString = sqlString & ") " end if ' let's make the order by : most recent examples on top sqlString = sqlString & "ORDER BY IsNew DESC, IsUpdated DESC, exampleName DESC;" End Function Private Function HTMLdisplay(ByRef objRs) 'the byreference argument above, passes a good-to-go record object 'so in here is where you would display field values from the db. 'for example: objRs.Fields(0).value ' creates the interface that displays linked results ' for examples that fit criteria 'i'm not big on html display, instead my focus is strictly 'functionality so the html leaves much to be desired but 'you can spruse it up.... HTMLdisplay = "

" if objRs("IsNew") then HTMLdisplay = HTMLdisplay & "NEW " elseif objRs("IsUpdated") then HTMLdisplay = HTMLdisplay & "UPDATED " end if if LCase(objRs("ExampleLanguage")) = "vbscript" then HTMLdisplay = HTMLdisplay & "" HTMLdisplay = HTMLdisplay & objRs("exampleName") & "" HTMLdisplay = HTMLdisplay & "
" & objRs("exampleDesc") & "

" End Function Private Function MakeKeywordArray(ByVal sWordVarTmp) 'makes an array of any entered keywords Dim sDelim, x sDelim = "," if TRIM(sWordVarTmp) = "" then Exit Function if not instr(sWordVarTmp, sDelim) then sDelim = " " x = Split(sWordVarTmp, sDelim) MakeKeywordArray = x End Function End Class '#################### END CLASS DEFINITION ######################## %> The ASP Emporium - Simple Database Search Engine

Everybody wants a search engine so here you go. If you enter no criteria, all results are displayed. You can enter keywords separated by spaces or by commas. You can also choose the fields to be searched and the type of search. Updated on May 2, 2001 and is now a vbscript class (big surprise there) and uses a new table structure.

<% 'variable declaration Dim oDB, ct, results, noCriteria 'create instance of the class Set oDB = New DBSearchEngine With oDB 'db connection string .DBConnectionString = Application("dbConn") 'db account name .DBConnectionAccount = Application("dbUsr") 'db account's password .DBConnectionAcctPassword = Application("dbPass") 'search type .SearchType = Request("type") 'determine what to search if request("exampleName") = "on" then .SearchTitles if request("exampleDesc") = "on" then .SearchDescriptions 'gather results into a string results = .Search( request("keyword") ) 'get the count of records returned ct = .Count 'find out whether or not the minimum search criteria 'was entered... noCriteria = .bNoFieldsToSearch End With 'free class instance (failure to explicitly free objects in 'ASP is generally considered a bad idea) Set oDB = Nothing 'write the search results to the browser... With Response if noCriteria then .Write("

NO FIELDS SELECTED, SEARCH NOT REFINED!

") .Write(ct & " records found.
") .Write(results) End With %>