search problem 1

[laxfan]

I am having a problem with my search. It only seems to search through XREF (site 8). It won't search through Site3, Site 28 or Site 29. Does anyone know why?

' 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;"

 

[mrmovie]

there is nothing wrong with your conditional statements
this works fine on my machine for all combinations you want

varSiteID = 29
if varSiteID = 8 Then
msgbox "8..."

ElseIf varSiteID = 3 OR varSiteID = 28 OR varSiteID = 29 Then
msgbox "other ones!"

End If

so, i would say there is something wrong with how varSiteID is being created/assigned/modified/declared etc

 

[laxfan]

Here is the coding used. Can you help? ps 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=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, "'", "''")

 

[laxfan]

I am confused. It actually does search the appropriate units, pages, contents etc, however, it only 'finds' items in XREF (unit 8). Even though the keywords have all been entered into the data base, it doesn't come up with any keyword results for any other units. Can anyone help?

 

[PHV]

Are you sure that varPageID contains the proper value when you build strSQLKeywords ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[laxfan]

No, I am not sure (the problem is that I didn't set up this site). When I add the keywords to the data base, I can only do so in "contents".

 

[PHV]

the problem is that I didn't set up this site
Are you saying you can't modify the ASP code nor query the database ?

 

[laxfan]

No, I'm not saying that. I can modify the asp and query the database. The guy who set it up is gone but I know that he was having problems trying to figure out why the search function did not work as intended.

 

[PHV]

So, you may try a Response.Write of strSQLKeywords when varSiteID<>8 and play with queries until you find the correct SQL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244