How do I get a search to show me more than one result.

[technoplonker]

I am carrying out a search on an MS Access 2000 db from within a page.
I know that there is more than one result to be displayed, but I can't
get the code to loop to the second result onwards. This is the code.

I hope it's not too big, as I don't want to end up with a thick ear.

<div id="masterdiv">
<%

Dim cn
Dim rs
Dim theVariable
Dim rsSearchResult 'database Recordset Variable
Dim sarySearchWord 'Holds the keywords for the URL
Dim strSearchKeywords 'Holds the keywords to be searched
menuNum = 1
Set CN=CreateObject("ADODB.COnnection")
CN.Open strCon

Set RS=CN.Execute("SELECT nav FROM Nav")
Do While not RS.EOF
theVariable=RS.Fields("Nav")
Response.Write "<div class=""menutitle""
onclick=""SwitchMenu('sub" & menuNum & "')"">"
Response.Write theVariable & "</div>"
Response.Write "<span class=""submenu"" id=""sub" & menuNum &
""">"


Set rsSearchResult = Server.CreateObject("ADODB.Recordset")

strSearchKeywords = Trim(Request.QueryString("search"))

sarySearchWord = Split(Trim(strSearchKeywords), " ")

strSQL = "SELECT Pages.* FROM Pages "

strSQL = strSQL & "WHERE cat LIKE '%" & theVariable & "%'"


rsSearchResult.Open strSQL, strCon

Response.Write vbCrLf & vbCrLf
Response.Write vbCrLf & " <a href=""" & rsSearchResult("cat") & """>"
& rsSearchResult("title") & "</a>"
Response.Write vbCrLf & " </span>"

menuNum = menuNum+1
RS.MoveNext
Loop
Set RS=nothing
CN.Close
Set CN=nothing
Set SearchResult=nothing
%>

</div>

Thanks in advance

TP

 

[Alex Goodey]

you loop on the RS recordset but not the rsSearchResult record set, try
using a Do While like you already have on both Record Sets