If someone does a search on my site, they only get very limited results in "XREF" (or unit 8) and NO results in the other units - 3, 28 & 29. In the database all they keywords are located in "content" table. Can someone tell what is wrong? If I'm in the wrong forum, please let me know which forum to go to. Thanks!
This is part of the keywordresponse.asp file.
<%
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=paul.marcotte;pw=bT2du8Yg;"
' 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 = 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.Recordset")
objRecKeywords.Open strSQLKeywords, objConn, adOpenStatic
' count the records found
intCount = objRecKeywords.Recordcount
%>
<%
' return results to user
If intCount > 0 Then
If varSiteID = 8 Then
Response.Write "<p><strong>" & intCount & " memo(s) found matching the above keyword(s).</strong></p>"
While Not objRecKeywords.EOF
Response.Write "<p><strong>Memo: <a href='/EPB/Memo.asp?UnitID=" & varUnitID & "&SiteID=" & varSiteID & "&MemoNumber=" & objRecKeywords("MemoNumber") & "'>" & objRecKeywords("MemoNumber") & "</a></strong><br>" & vbCrLf
Response.Write "<strong>Index:</strong>" & objRecKeywords("IndexNumber").value & "<br>" & vbCrLf
Response.Write "<strong>Date:</strong>" & objRecKeywords("MemoDate") & "<br>" & vbCrLf
Response.Write "<strong>Subject:</strong> " & objRecKeywords("MemoTitle") & "</p>"
objRecKeywords.MoveNext
Wend
ElseIf varSiteID = 3 OR varSiteID = 28 OR varSiteID = 29 Then
Response.Write "<p><strong>" & intCount & " instance(s) found matching the above keyword(s).</strong></p>"
While Not objRecKeywords.EOF
Response.Write "<p><strong>Date:</strong>" & objRecKeywords("ContentDate") & "<br>" & vbCrLf
Response.Write "<strong>Subject: <a href='Content.asp?UnitID=" & varUnitID & "&SiteID=" & varSiteID & "&SubjectID=" & objRecKeywords("SubjectID").value & "&kw=" & Server.URLEncode(Trim(varUCKeywords)) & "'>" & objRecKeywords("SubjectName").value & "</a></strong></p>" & vbCrLf
objRecKeywords.MoveNext
Wend
ElseIf varSiteID = 7 Then
End If
Else
Response.Write "<p>No documents were found matching these keywords. Please revise your keywords and try again."
End If
%>
This is part of the keywordresponse.asp file.
<%
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=paul.marcotte;pw=bT2du8Yg;"
' 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 = 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.Recordset")
objRecKeywords.Open strSQLKeywords, objConn, adOpenStatic
' count the records found
intCount = objRecKeywords.Recordcount
%>
<%
' return results to user
If intCount > 0 Then
If varSiteID = 8 Then
Response.Write "<p><strong>" & intCount & " memo(s) found matching the above keyword(s).</strong></p>"
While Not objRecKeywords.EOF
Response.Write "<p><strong>Memo: <a href='/EPB/Memo.asp?UnitID=" & varUnitID & "&SiteID=" & varSiteID & "&MemoNumber=" & objRecKeywords("MemoNumber") & "'>" & objRecKeywords("MemoNumber") & "</a></strong><br>" & vbCrLf
Response.Write "<strong>Index:</strong>" & objRecKeywords("IndexNumber").value & "<br>" & vbCrLf
Response.Write "<strong>Date:</strong>" & objRecKeywords("MemoDate") & "<br>" & vbCrLf
Response.Write "<strong>Subject:</strong> " & objRecKeywords("MemoTitle") & "</p>"
objRecKeywords.MoveNext
Wend
ElseIf varSiteID = 3 OR varSiteID = 28 OR varSiteID = 29 Then
Response.Write "<p><strong>" & intCount & " instance(s) found matching the above keyword(s).</strong></p>"
While Not objRecKeywords.EOF
Response.Write "<p><strong>Date:</strong>" & objRecKeywords("ContentDate") & "<br>" & vbCrLf
Response.Write "<strong>Subject: <a href='Content.asp?UnitID=" & varUnitID & "&SiteID=" & varSiteID & "&SubjectID=" & objRecKeywords("SubjectID").value & "&kw=" & Server.URLEncode(Trim(varUCKeywords)) & "'>" & objRecKeywords("SubjectName").value & "</a></strong></p>" & vbCrLf
objRecKeywords.MoveNext
Wend
ElseIf varSiteID = 7 Then
End If
Else
Response.Write "<p>No documents were found matching these keywords. Please revise your keywords and try again."
End If
%>
