The following is the coding that was used by the person who worked here before
me. I was told that he could never get the search to work very well. The user
gets results for xref files, but not for anything else (ie site 3, site 28,
site 29). I've been trying to figure this out but have been unable to. I
would appreciate any input. Thanks
<%
Dim varKeywords
Dim strSQLKeywords
Dim objConn
Dim objRecKeywords
Dim intCount
Dim varUnitID
Dim varSiteID
Dim varSubjectID
Dim strSQLUnit
Dim objRecUnit
Dim tempArray


tempArray = Split(Request.Form("SiteID"),"|")



' set the values for the SiteID
varSiteID = tempArray(0)
varPageID = tempArray(1)

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=hrib;uid=test;pw=test;"

' SQL Statement to find the unit
strSQLUnit = "SELECT UnitID FROM Sites WHERE SiteID = " & varSiteID & ";"

' create and open the Unit recordset
Set objRecUnit=Server.CreateObject("ADODB.Recordset")
objRecUnit.Open strSQLUnit, objConn, adOpenStatic, adLockReadOnly, adCmdText

' set the value for the UnitID
varUnitID = objRecUnit("UnitID")

' convert keywords to lowercase
varLCKeywords = lCase(Request.Form("Keywords"))
' strip out apostrophies
varLCKeywords = Replace(varLCKeywords, "'", "'")

' convert keywords to lowercase
varUCKeywords = Request.Form("Keywords")
' strip out apostrophies
varUCKeywords = Replace(varUCKeywords, "'", "'")


' sql statements

' generate SQL statement based on Site ID
If varSiteID = 8 Then

strSQLKeywords= "SELECT XrefMemos.MemoNumber, XrefMemos.MemoTitle,
XrefMemos.MemoDate, " & _
"XrefMemos.IndexNumber " & _
"FROM XrefMemos INNER JOIN Subjects ON " & _
"XrefMemos.IndexNumber = Subjects.IndexNumber " & _
"WHERE XrefMemos.MemoKeywords LIKE '% " & varLCKeywords & "%'
" & _
"OR XrefMemos.MemoTitle LIKE '%" & Trim(varUCKeywords) & "%' "
& _
"OR Subjects.SubjectName LIKE '%" & Trim(varUCKeywords) & "%'
" & _
"GROUP BY XrefMemos.MemoNumber, XrefMemos.MemoTitle,
XrefMemos.MemoDate, " & _
"XrefMemos.IndexNumber " & _
"ORDER BY XrefMemos.MemoDate DESC;"

ElseIf varSiteID = 1 OR varSiteID = 3 OR varSiteID = 28 OR varSiteID = 29 Then

strSQLKeywords= "SELECT Subjects.SubjectID, Subjects.SubjectName, " & _
"Content.ContentDate " & _
"FROM Subjects, Content " & _
"WHERE Subjects.PageID = " & varPageID & " " & _
"AND Content.SubjectID = Subjects.SubjectID " & _
"AND Content.Keywords LIKE '% " & varLCKeywords & "%' " & _
"ORDER BY Content.ContentDate DESC;"

End If

' objRecKeywords.CursorLocation = 3 adUseClient
Set objRecKeywords=Server.CreateObject("ADODB.Recordse t")
objRecKeywords.Open strSQLKeywords, objConn, adOpenStatic

' count the records found
intCount = objRecKeywords.Recordcount

%>