How do You do a PREVIOUS and NEXT links?

That is, I have a db that users search to return records. However, I want them to see only 10 per page and then, a NEXT 10 link that will display the next 10 results.
I have tried using LIMIT , but it only returns only the 10 and no way for them to see the remaining results.
I am using Asp, MS SQL 7 on WINNT.


cheers

assigned

Hey.
Well we've written an article on paging with PHP and MySQL, which you can see here: http://www.devarticles.com/art/1/110. Can you "read" PHP to convert what the article talks about into ASP and MS SQL?

It should give you an idea, but because MySQL supports LIMIT and SQL Server only supports TOP, it may be a bit difficult to get your head around. If you can't understand it then let me know and I will get one of our authors to write an article on it for you

Yes, you guessed right. it's not easy to convert this php stuff to ASP. This is a FAQ on many site ASP sites and no article written seem to have addressed it in Plain English. I am looking forward to the article.
And if you are feeling lucky, how about doing another on how to do this:
Display first paragraph of an article and then .......more.[when MORE is clicked, the entire entire can yhen be read] how to do it in ASP

cheers
assigned

OK, well we've added the article to the list for this week and we will be talking with our writers tommorow. It should be on the site in a couple of days

Was the article on displaying the first paragraph and the [read more...] ever written? I don't recall seeing it....

Thanks!

Nope, they never did. I have checked everyday and i am now learning how to do it with the help of various resources.
There are some very good write up on it at some sites. Try programmersResources.com and look for paging. sorry dont remember the exact article number, left it at work.

cheers,

assigned logic

Ok when I posted this question on another forum, I got these replies from others.
I have not had time to try them 'cos my pc died and I have not finished rebuilding it. Please let me know which one works.
Also the link to an article that walks you step by step

=========================================
http://www.programmersresource.com/articles/paging.asp
================================================== =========
CODE BY ZENITH
====================

** mypage.asp

strSQL = "SELECT ID,name FROM table"
set RS = oConn.Execute(strSQL)

IF (RS.EOF) THEN
num_of_recs = 0
RS.Close
Set RS = Nothing
ELSE
results = RS.GetRows
num_of_recs = Ubound(results,2)
RS.Close
Set RS = Nothing
END IF

start = Request.Querystring("start")
end = Request.Querystring("end")

'if start & end are both "" -> we're on 1st page
'then records from 1-10 (table rows 0-9)

IF (start = "" AND end = "") THEN
FOR i = 0 TO 9
Response.Write("ID:" & results(0,i) & " Name:" & results(1,i) & "<br>")
NEXT

Response.Write("<a href=""mypage.asp?start=10&end=20"">Next 10</a>")
ELSE

'another page = not first one
FOR i = start TO end
Response.Write("ID:" & results(0,i) & " Name:" & results(1,i) & "<br>")
NEXT

Response.Write("<a href=""mypage.asp?start=" & start - 10 & "&end=" & end-10 & """>Previous 10</a>")
Response.Write("<a href=""mypage.asp?start=" & start + 10 & "&end=" & end+10 & """>Next 10</a>")

END IF

================================================== ==========================================

Code by GOOF
================================================== =================================

Dim currentPage, itemsPerPage, x, nextHTML, prevHTML
itemsPerPage = 10
currentPage = request.querystring("page")
If (currentPage = "") Then currentPage = 1

'connect to the database
'dbRS will be the recordset I use for the search results returned

dbRS.move((currentPage - 1) * itemsPerPage)

For x = 1 To itemsPerPage
If (dbRS.EOF = false) Then
'display the current item
dbRS.movenext
End If
Next

'if you aren't on page 1, construct a link for the previous page
If (currentPage > 1) Then
prevHTML = "<a href=""thisdoc.asp?page=" & (currentPage - 1) & """>Previous Page</a>"
End If

'if this isn't the end of the database, construct a link for the next page
If (dbRS = false) Then
nextHTML = "<a href=""thisdoc.asp?page=" & (currentPage + 1) & """>Next Page</a>"
End If

'close the database and clear the objects


=============================================
cheers,
assigned

Hi,
I want to display 10 records per page using JSP,SQL Server 7.0,Tomcat.
Can any one help me in this regard.

Thanks in Advance
Sagar

Can any one help me in this regard

A general SQL statement would be:
SELECT records FROM table LIMIT 10

The paging logic is all here in this thread, its just a matter of converting it from PHP or ASP to JSP

Variables you want to pass to the page is the offset and the limit... offset being the first document you want to display, and limit being the number of variables past that... the actual paging system is simply an addition/multiplication table between limit and offset

Thanks MadCowDzz
Can i get any sample code for this or can you specify any similar link so that i can modify and use it for my functionality

Actually, this is problematic in SQL Server because it doesn't support the LIMIT keyword. That was the initial problem. The logic for doing this is easy if you can limit your query. The easiest solution I can think of is to make sure you have an auto_increment (identity, in SQL Server) field that you order by and to keep track of the first and last ids used. So you'd "SELECT TOP 10 FROM table WHERE id > $stored_id" and then make sure you store the first and last ids in that batch to send to the next/prev links so that the next load of the page will know where to start.

Incidentally, the code snippets given above will work, but imagine you have a million records. All of them would have to be returned and iterated over in both bits of code given above, and that could make for a slow loading page. Consider this pseudocode alternative:



This, or something very like it (it's untested) ensures that you only select ten records at a time, easing load on your server and making for a palatable wait-time for the user.

dhouston
Thanks for your immediate replies,basically i am new to JSP as well as PHP.Can you give some code snippet in JSP to resolve this issue

Thanks
Sagar

I'm afraid not. I haven't touched jsp in a while and was flying by the seat of my pants when I did. The looping and variable assignment should be pretty easy, and it shouldn't be too big a production to look up the db connectivity and all. Good luck with it.

Here goes my code and i am getting an error


Error:
javax.servlet.ServletException: javax/sql/RowSet
at org.apache.jasper.servlet.JspServlet.service(JspSe rvlet.java:249)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invokeNext(StandardPipeline.j ava:643)
at org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)

My Code is:
----------------------------------
<%@ page import="java.util.*, java.sql.*,com.sun.rowset.*,com.sun.rowset.CachedR owSetImpl" %>

<html>
<head>
<title>Physician Details Page</title>
</head>

<body bgcolor="#ffffee" text="blue">
<form target="_top" name=viewForm action="<%= request.getContextPath()%>/protected/dbtool_frameset.jsp" method="post">
<%! int numPages = 0; %>
<%
String columnName = "";
int count = 0;
int totalCols = 0;
int increment = 1;
ResultSetMetaData resultSetMetaData=null;

String numRowsString = (String) session.getAttribute("numRowsString");
int numRows = Integer.parseInt(numRowsString);
String startIndexString = request.getParameter("startIndex");
int startIndex = Integer.parseInt(startIndexString);

try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//CachedRowSet cache = new CachedRowSet();
CachedRowSetImpl cache = new CachedRowSetImpl();

cache.setReadOnly(true);
cache.setUrl("jdbcdbc:faxDetails");
cache.setUsername("AtlantisUpdate");
cache.setPassword("Evenor");
cache.setCommand("SELECT * FROM PhysicianDetail");
cache.execute();
resultSetMetaData = cache.getMetaData();
totalCols = resultSetMetaData.getColumnCount();
%>
<table border=1 align=center width="100%">
<tr>
<%
for(int j=1; j<=totalCols; j++) {
columnName = (String) resultSetMetaData.getColumnName(j);
%><td>
<b><% out.print(columnName); %></b>
</td>
<%}%>
</tr>
<%
numPages = numRows / 10;
int remain = numRows % 10;

if(startIndex + 10 < numRows) {
increment = startIndex + 10;
}else{
increment = startIndex + remain;
}


cache.absolute(startIndex);

for(count = startIndex; count < increment; count++) {
%><tr><%
for(int i=1; i<=totalCols; i++) {
%><td><% out.println(cache.getString(i)); %></td><%
} // end for
%></tr><%
cache.next();
} // end for
%>
</table>
<br>
<br>
<%

%>
<table width = "100%">
<tr>
Displaying Records:
<% if(startIndex + 10 < numRows){%>
<%= " " + startIndex %> - <%= increment - 1 %>
<%}else{%>
<%= " " + startIndex %> - <%= numRows %>
<%}%>

<%if(startIndex != 1) {%>
<a href="<%=request.getContextPath()%>/protected/view_tableRecords.jsp?startIndex=<%=startIndex-10%>">Previous</a>
<%}%>

<%increment += 10;%>
<%if(startIndex + 10 <= numRows){%>
<a href="<%=request.getContextPath()%>/protected/view_tableRecords.jsp?startIndex=<%=startIndex+10%>">Next</a>
<%}%>
</tr>
</table>
<%
}catch(Exception exc){
out.println(exc.toString());
} // end try-catch
%>

</form>
</body>
</html>
--------------------------------------
Can any body help regardign this error

Thanks
Sagar Prathap