results of query not displaying

[laxfan]

If someone does a search on this 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" column. 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=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 = 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
%>

 

[Chopstik]

Are you actually getting results and they are simply not displaying or are you not getting any results back? If you are not getting results, have you tried to response.write your SQL string(s) and then tested it in your database to ensure that it's correct?

If these do not help to resolve the problem, then it might help if you post back with more description as to the what you are encountering.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous