Paging Record Set Error

[dupati1]

Hi Guys,

i am going crazy with this error. I am trying to page the record sets retrieved from the database based on a search parameter. The first page works fine and shows the related number of pages for a particular search, but when i click on the second page and other pages i get all the records from the database. The search parameter value is lost i guess. I dont know how to hold this value. After going through related articles i came up even with solution and repassed the search variable but still i am getting error. Any help would appreciated.

The below is my code.

File 1: paginginfo.asp

<%
Set rsName = Server.CreateObject("ADODB.Recordset")
Set MdConnection = Server.CreateObject("ADODB.Connection")
MdConnection.Open strConnect

rsName.Open "SELECT [SurgeonName] FROM [SurgeonsList]", MdConnection
%>
<form method="POST" action="newnewcheck.asp" name="vijay" align= "center">

<p><select size="1" name="surName">
<option value= "%">-Select One-</option>

<% While Not rsName.EOF %>
<option value="<% =rsName.Fields("SurgeonName")%>"><% =rsName.Fields("SurgeonName")%></option>
<%
rsName.MoveNext
Wend
%>

</select><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>

****************************
File 2: newnewcheck.asp

<%

Dim surgeonName

surgeonName = "%"

If LEN(TRIM(Request.Form("surName"))) > 0 Then
surgeonName = Request.Form("surName")
End If
If LEN(TRIM(surgeonName)) > 0 Then
surgeonName = Replace(surgeonName, "'","''")
End If

Dim Con
Dim rsPage
Dim Page
Dim RowCount
Dim PageCounter
Dim strQuote
Dim strConnect
Dim lsSQL

'Get the Current page
Page = Request.QueryString("Page")

'If there is no page set it to page 1
If Page = "" then Page = 1

RowCount = 0

set con = server.CreateObject("ADODB.Connection")
Set rsPage = Server.CreateObject("ADODB.Recordset")

strConnect = "Provider=SQLOLEDB;Data Source=STJSQL\STJDB;Database=VJORnew4SQL;User ID=sa;Password=scgmlk"

lsSQL = " SELECT * FROM [ORRecord] WHERE( ([SurgeonName] LIKE '" &surgeonName& "' )"


con.Open strConnect

'Set the number of records in each page to 25
rsPage.PageSize = 25

'Open recordset
rsPage.Open lsSQL, Con,3,3

rsPage.AbsolutePage = cInt(Page)

If not rsPage.eof or rsPage.bof Then

Response.Write "<TABLE>"
Do While Not rsPage.eof and RowCount < rsPage.PageSize

Response.Write "<tr><td>" &rsPage("Procedure") & "</td></tr>"

rsPage.Movenext
RowCount = RowCount + 1
Loop

Response.Write "</TABLE><p>"
For PageCounter = 1 to rsPage.PageCount
%>
<a href ="newnewcheck.asp?Page=<%=PageCounter%>&surgeonName=<%=request("surgeonName")%>"><%=PageCounter%></a>

<%
Next

rsPage.Close
set rsPage = Nothing
End if

%>

[aspdaddy]

Have you read this reply
[webmasterworld.com...]

<added>
The way I learned asp paging was to download a guestbook script that uses paging from aspin and just go through the code till I understood it. Or just modify it and hope it dont break ;)
</added>

[dupati1]

Hi thanks for the reply.

Ya i read that article and included the below line,

<a href ="newnewcheck.asp?Page=<%=PageCounter%>&surgeonName=<%=request("surgeonName")%>"><%=PageCounter%></a>

but i am still getting error. the first page gets displayed fine but when i click on the other pages links i get all the records of the database.

Any ideas

VJ

[aspdaddy]

Cant spot anything - the code I use, looks similar to yours. Maybe you can spot something.

intPageSize = CONST_PAGESIZE
 intThisPage = request("Page")
 If intThisPage = "" then 
intThisPage = "1" 
 End If 
 
 objConn.Open strConn
 objRS.Open strSQL, objConn, 3, 1 
 intRecords = objRS.RecordCount 
 if NOT (intRecords = 0) then 
objRS.pageSize = intPageSize 
objRS.AbsolutePage = intThisPage 
 End If
 intNext = intThisPage + 1
 intPrev = intThisPage - 1
 intPages = intRecords \ intPageSize 
 if intRecords mod intPageSize then 
 intPages = intPages + 1
 End If

[dupati1]

Ya the code works fine when i directly fetch the record set. But when i try to fetch the record set based on a search parameter it does not work.

I dont the reason for this.

Thanks.

VJ

[aspdaddy]

Maybe something wrong in the SQL.

Try doing reponse.write SQL
and then paste it into the db query window, see if it runs ok.

[dupati1]

Thanks a lot. I solved the problem.

It worked when i used request.querystring instead of request.form

VJ