how to limit number of records in page

minhas4all · 09-08-2005, 08:28 PM

hello
its me once again with new problem as i wrote earlier that i am new in asp and even in programming i make a guest book for site now i want to limit the number of records to display in a page so please help me in this matter
thanks in advance
the code is as follows

Code:

<%
Dim conn 
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = medicalrecords; User Id = sa; Password=mohammed"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select  * FROM gbcomm order by date desc ", conn
%>
          <%do until rs.EOF%>
          <%for each x in rs.Fields%>
<%   Response.Write("<b>" & x.name & "</b>")
    Response.Write(" : ")
	if lcase(x.name)="email" then
    response.write ("<a href='mailto:" & x.value & "'>" &  x.value  & "</a><br />") %>   	
          <% else
    Response.Write(x.value )  %>
        </td>
	<tr><td>
	<% end if %>
    <% next
   rs.MoveNext%>  <hr>
<%loop
rs.close
conn.close%>

TheShaner · 09-08-2005, 09:54 PM

I'm not sure if this is the easiest and cleanest approach, but I would make that all part of a form with no action. So basically, it's a form that submits to itself. You would then set up a hidden input field that would by default have the value of 1 (to be used as your pointer to what record to start on). Put a counter in your code that only 10 records (or however many) display. Something like this:

Code:

<%
  'Set up connection to database and recordset here
%>

<html>
<head><title>Guestbook</title>
<script language="JavaScript">
<!--
function nextRec()
{
  // Add 10 to startRec value and then submit form
  document.myform.startRec.value = document.myform.startRec.value + 10
  document.myform.submit()">
}
-->
</script>
</head>
<body>
<form name="myform" method="post">
<% 
  If CInt(Request.Form("startRec")) > 1 Then
    Response.Write "<input type=""hidden"" name=""startRec"" value=""" &
                          Request.Form("startRec") & """>"
  Else
    Response.Write "<input type=""hidden"" name=""startRec"" value=""1"">"
  End If
%>
<%
  'Loop from document.myform.startRec.value to
  'document.myform.startRec.value + 10
  'and print the records here
%>
<!-- The link below adds 10 to the startRec value 
       for the next page to use for the start record
       and then submits the page
-->
<input type="button" onClick="nextRec()" value="Next Page"> 
</form>
<% 'Close database and recordset connection here %>
</body>
</html>

I haven't tested this, so not sure how it'd work, but it seems plausible. With some modifying, you could also get it to go back to previous records too. Hope this gives you some direction.

-Shane

Bullschmidt · 09-09-2005, 03:21 AM

And here are some related links:

Database Paging
http://www.asp101.com/samples/db_paging.asp
Uses PageSize method of recordset.

Paging Records with GetRows by Mukul Sabharwal - 7/5/2000
http://www.4guysfromrolla.com/webtech/070500-1.shtml
Uses GetRows.

Displaying a certain # of records on each webpage Paging
http://www.aspfree.com/asp/startpage.asp?id=11
Uses PageSize method of recordset.
Shorter.

Recordset Paging with ADO 2.0 by Michael Qualls
http://www.asp101.com/articles/recor...ging/index.asp
Uses PageSize method of recordset.
Longer with more comments.

Paging Demo provides Count and Navigation at the top of the Page!
http://www.aspfree.com/asp/startpage.asp?id=65

minhas4all · 09-09-2005, 08:44 AM

Thanks both of you
but as i told earlier that i am new in asp
so i tried with your code but didnot completed
please any one there that can write the code in details
thanks

TheShaner · 09-09-2005, 03:53 PM

Well, I had pretty much wrote most of it out for you. I'm not going to test this because I really don't have the time, so you'll have to mess with it if it doesn't work or find someone who has more time. But here's what I came up with what seems to work (in my head):

Code:

<%
  Dim conn 
  Set conn = Server.CreateObject("ADODB.Connection")
  conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = medicalrecords; User Id = sa; Password=mohammed"
  set rs = Server.CreateObject("ADODB.recordset")
  rs.Open "Select  * FROM gbcomm order by date desc ", conn
%>

<html>
<head><title>Guestbook</title>
<script language="JavaScript">
<!--
function nextRec()
{
  // Add 10 to startRec value and then submit form
  document.myform.startRec.value = document.myform.startRec.value + 10
  document.myform.submit()">
}
-->
</script>
</head>
<body>
<form name="myform" method="post">
<% 
  If CInt(Request.Form("startRec")) > 1 Then
    Response.Write "<input type=""hidden"" name=""startRec"" value=""" &
                          Request.Form("startRec") & """>"
  Else
    Response.Write "<input type=""hidden"" name=""startRec"" value=""1"">"
  End If
%>
<%
  Dim count
  count = 0
  rs.Move CInt(document.myform.startRec.value) 
  Do Until rs.EOF
    Response.Write "<b>" & rs("name") & "</b>"
    Response.Write " : "
    If lcase(rs("name"))="email" Then
      Response.Write "<a href='mailto:" & rs("value") & "'>" &  rs("value") & "</a>"	
    Else
      Response.Write rs("value")
    End If
    Response.Write "<br><hr><br>
    count = count + 1
    If count = 10 Then Exit Do
    rs.MoveNext
  Loop
  ' Used to disable Next Page button
  Dim noMoreRecs
  noMoreRecs = False
  If rs.EOF Then noMoreRecs = True
%>
<br>
<%
  If noMoreRecs = False Then
    Response.Write "<input type=""button"" onClick=""nextRec()"" value=""Next Page"">"
  End If
%>
</form>
<% 
  rs.close
  conn.close
%>
</body>
</html>

I never looked into those links from BullSchmidt, so those may offer better solutions.

-Shane

minhas4all · 09-10-2005, 07:17 AM

Quote:

Originally Posted by TheShaner

Well, I had pretty much wrote most of it out for you. I'm not going to test this because I really don't have the time, so you'll have to mess with it if it doesn't work or find someone who has more time. But here's what I came up with what seems to work (in my head):

Code:

<%
  Dim conn 
  Set conn = Server.CreateObject("ADODB.Connection")
  conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = medicalrecords; User Id = sa; Password=mohammed"
  set rs = Server.CreateObject("ADODB.recordset")
  rs.Open "Select  * FROM gbcomm order by date desc ", conn
%>

<html>
<head><title>Guestbook</title>
<script language="JavaScript">
<!--
function nextRec()
{
  // Add 10 to startRec value and then submit form
  document.myform.startRec.value = document.myform.startRec.value + 10
  document.myform.submit()">
}
-->
</script>
</head>
<body>
<form name="myform" method="post">
<% 
  If CInt(Request.Form("startRec")) > 1 Then
    Response.Write "<input type=""hidden"" name=""startRec"" value=""" &
                          Request.Form("startRec") & """>"
  Else
    Response.Write "<input type=""hidden"" name=""startRec"" value=""1"">"
  End If
%>
<%
  Dim count
  count = 0
  rs.Move CInt(document.myform.startRec.value) 
  Do Until rs.EOF
    Response.Write "<b>" & rs("name") & "</b>"
    Response.Write " : "
    If lcase(rs("name"))="email" Then
      Response.Write "<a href='mailto:" & rs("value") & "'>" &  rs("value") & "</a>"	
    Else
      Response.Write rs("value")
    End If
    Response.Write "<br><hr><br>
    count = count + 1
    If count = 10 Then Exit Do
    rs.MoveNext
  Loop
  ' Used to disable Next Page button
  Dim noMoreRecs
  noMoreRecs = False
  If rs.EOF Then noMoreRecs = True
%>
<br>
<%
  If noMoreRecs = False Then
    Response.Write "<input type=""button"" onClick=""nextRec()"" value=""Next Page"">"
  End If
%>
</form>
<% 
  rs.close
  conn.close
%>
</body>
</html>

I never looked into those links from BullSchmidt, so those may offer better solutions.

-Shane

thanks for your assistance
but i go through following code and its working fine

Code:

<!--#include file="connection.asp"-->

<%
DIM mySQL, objRS
mySQL = "SELECT * FROM gbcomm order by date desc" 
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorType = 1
objRS.Open mySQL, objConn

DIM intPageRecords, intRecords, intRecordCount, intCurrentPage
DIM intNumberOfPages, intDisplayPage
intPageRecords = Request.Querystring("page")
IF intPageRecords = "" THEN intPageRecords = 1 : intRecords = 1
intRecords = intPageRecords
intPageRecords = ((intPageRecords - 1) * 5) +1
intRecordCount = 0

IF NOT objRS.EOF THEN
objRS.Move (intPageRecords - 1)
DO WHILE intRecordCount < 5 and NOT objRS.EOF
%>
<tr><td><%for each x in objRS.Fields%>
<%   Response.Write("<b>" & x.name & "</b>")
    Response.Write(" : ")
	if lcase(x.name)="email" then
    response.write ("<a href='mailto:" & x.value & "'>" &  x.value  & "</a><br />") %>   	
          <% else
    Response.Write(x.value )  %>
        </td>
	<tr><td>
	<% end if %>
    <% next
   objRS.MoveNext%>  <hr></td></tr>
<%
'objRS.MoveNext
intRecordCount = intRecordCount +1
Loop
END IF
%>
</table>

<%=intPageRecords%> - <%=intPageRecords+(intRecordCount-1)%> of <%=(objRS.RecordCount)%> customers
<p>Scroll Through More Customers
<%
intCurrentPage = Request.Querystring("page")
IF intCurrentPage = "" THEN intCurrentPage = 1
intNumberOfPages = int(objRS.RecordCount \ 5)
IF objRS.RecordCount MOD 5<> 0 THEN intNumberOfPages = intNumberOfPages + 1
Response.Write("Pages: [")
FOR intDisplayPage = 1 TO intNumberOfPages
IF Cint(intDisplayPage) = Cint(intCurrentPage) THEN
Response.Write " <b>" & intDisplayPage & "</b> "
ELSE
Response.Write " <a href=""inser.asp?page=" & intDisplayPage & """>" & intDisplayPage &_
"</a> "
END IF
NEXT
Response.Write ("]")
%>

thanks once again all of you

TheShaner · 09-10-2005, 03:11 PM

Good to see you got it working!

-Shane

minhas4all · 09-12-2005, 08:36 AM

Quote:

Originally Posted by TheShaner

Good to see you got it working!

-Shane

thanks