search page

[aspbeginner]

I am trying to create the search page to search for the records. I have a combo box -- the value -- 1, 10, 25, 40, etc -- and a text box. Suppose, I type ball for searching and want to select 1 from the combo box for showing only 1 record at a time the total of records for that "ball" product is 3. My below code is almost working fine, but the problem is when i click on the next page after submitting, the "next page" page shows 1 of 3 Next Page for showing all records. What am I doing wrong? Please help me. Thank you

<%
Dim strProduct, strCriteria, intmaxnumber
Dim iPageSize, strPageCount, strPageCurrent, x

strProduct = Request.Form("sproduct")

'If Request.Form("maximum") <> "" Then session("maximum") = Request.Form("maximum")


If Request("maximum") = "" Then
strPageCurrent = 1
Else
strPageCurrent = CInt(Request.Form("maximum"))
End If

iPageSize = strPageCurrent

Dim objrs, productcount

Set objrs = server.CreateObject("Adodb.Recordset")

'objrs.CursorLocation = adUseClient
objrs.PageSize = iPageSize

strCriteria = "Select * From Products Where Product Like '%" & strProduct & "%';"
objrs.Open strCriteria, oConn, adOpenStatic, adLockReadOnly, adcmdtext

strPageCount = objrs.PageCount

If 1 > strPageCurrent Then strPageCurrent = 1
If strPageCurrent > strPageCount Then strPageCurrent = strPageCount

productcount = 0
If Not objrs.EOF Then
objrs.AbsolutePage = strPageCurrent
Response.Write "Your search for " & "<B>" & strProduct & "</B>" & " returned " & "<B>" & objrs.RecordCount & "</B>" & " products." & "<BR><BR>"
'do while not objrs.EOF
Do While objrs.AbsolutePage = strPageCurrent And Not objrs.EOF
productcount = productcount + 1

Response.Write productcount & "." & " " %>
<A HREF="product.asp?prodid=<%=objrs("ProductID")%>"><%=objrs("Product")%></A><BR><BR>
<%
Response.Write objrs("Description") & "<BR><BR>"
objrs.moveNext
Loop

Response.Write "Page"
Response.Write strPageCurrent
Response.Write " of "
Response.Write strPageCount & "&nbsp;"

dim previouspage, nextpage

previouspage = (strPageCurrent - 1)
nextpage = (strPageCurrent + 1)

If strPageCurrent <> 1 Then
Response.Write "<A HREF=""./searchprod.asp?page="
Response.Write previouspage
Response.Write """>Previous Page </A>" & "<BR>"
Response.Write "&nbsp;&nbsp;" & "<BR>"
End If

If strPageCurrent < strPageCount Then
Response.Write "<A HREF=""./searchprod.asp?page="
Response.Write nextpage
Response.Write """>Next Page</A>" & "<BR>"
End If

Else
Response.Write "Matching product not found"
End If

objrs.Close
Set objrs = Nothing

%>

[Clunietp]

Use a client side cursor with rs.Maxrecords set to your value rather than using server side cursors with pagesizes

If you are querying a SQL 7/2000 database or an Access 2000 database you can specify TOP in your query as an alternative
ex: Select TOP 2 from MyTable where......

[efrat]

And dont forget that client cursor=3 not 1

[Clunietp]

EXACTLY

Thanks Efrat!

[efrat]

Thank for the compliment Mr Tom :)

[aspbeginner]

Clunietp & efrat:

I did change from objrs.pagesize to objrs.maxrecords. But, when I selected 1 from my combobox showing one record only, it showed all records after submitting. Am I missing something?

[aspbeginner]

Anybody helps with my problem?? Please help me.
Thank you.