Solved

classic asp recordset

Posted on 2011-09-03
2
321 Views
Last Modified: 2012-05-12
Attached code is working fine. but I would like to improve a little on paging
How if I have 1000 record, and have pagesize=5
It will show number from 1 to 200
What I really want to do is like

<< 1 ,2,3,4,5 >>
If  ">>" Is clicked, it will should 6,7,8,9,10...and etc.

Does it make sense and possible to make it happen?
The codes is long but you just have to focus last part dealing with paging.

Thanks,
<!--#include virtual="/RideFinder/Adovbs.asp"--> 
 

 
<%
' BEGIN USER CONSTANTS
Dim CONN_STRING
Dim CONN_USER
Dim CONN_PASS
' Our SQL code - overriding values we just set
' Comment out to use Access
CONN_STRING = "PROVIDER=SQLOLEDB;DATA SOURCE=xxx;DATABASE=Qa;"
CONN_USER = "sa"
CONN_PASS = "ss"
' END USER CONSTANTS


' BEGIN RUNTIME CODE
' Declare our vars
Dim iPageSize       'How big our pages are
Dim iPageCount      'The number of pages we get back
Dim iPageCurrent    'The page we want to show
Dim strOrderBy      'A fake parameter used to illustrate passing them
Dim strSQL          'SQL command to execute
Dim objPagingConn   'The ADODB connection object
Dim objPagingRS     'The ADODB recordset object
Dim iRecordsShown   'Loop controller for displaying just iPageSize records
Dim I               'Standard looping var

' Get parameters
iPageSize = 5 ' You could easily allow users to change this

' Retrieve page to show or default to 1
If Request.QueryString("page") = "" Then
	iPageCurrent = 1
Else
	iPageCurrent = CInt(Request.QueryString("page"))
End If
 

' Make sure the input is one of our fields.
strOrderBy = LCase(Request.QueryString("order"))
Select Case strOrderBy
	Case "last_name", "first_name", "sales"
		' A little pointless, but...
		strOrderBy = strOrderBy
	Case Else
		strOrderBy = "id"
End Select

' Build our SQL String using the parameters we just got.
'strSQL = "SELECT * FROM sample ORDER BY " & strOrderBy & ";"
Destination="Irvine"
SQL =" SELECT "
SQL = SQL & " f.AddressID, f.Address AS frAddress, f.City AS frCity, f.Zip AS frZip, "
SQL = SQL & " d.Address AS toAddress, d.City AS toCity, d.Zip AS toZip, "
SQL = SQL & " g.VanpoolID, g.MilesToWork, g.PickupAddressID, "
SQL = SQL & " p.EmployerName, p.VanpoolID AS Expr1, "
SQL = SQL & " v.VanNumber "
SQL = SQL & " FROM Vanpool AS p INNER JOIN "
SQL = SQL & " Passenger AS g ON p.VanpoolID = g.VanpoolID INNER JOIN "
SQL = SQL & " Van AS v ON p.VanID = v.VanID INNER JOIN "
SQL = SQL & " Address AS d ON p.DestinationID = d.AddressID INNER JOIN "
SQL = SQL & " Address AS f ON g.PickupAddressID = f.AddressID " 
SQL = SQL & " WHERE (d.City ='" & Destination & "') "
SQL = SQL & " ORDER BY f.AddressID, v.VanNumber " 
strSQL=SQL
 
' Now we finally get to the DB work...
' Create and open our connection
Set objPagingConn = Server.CreateObject("ADODB.Connection")
objPagingConn.Open CONN_STRING, CONN_USER, CONN_PASS

' Create recordset and set the page size
Set objPagingRS = Server.CreateObject("ADODB.Recordset")
objPagingRS.PageSize = iPageSize

' You can change other settings as with any RS
'objPagingRS.CursorLocation = adUseClient
objPagingRS.CacheSize = iPageSize

' Open RS
objPagingRS.Open strSQL, objPagingConn, adOpenStatic, adLockReadOnly, adCmdText

' Get the count of the pages using the given page size
iPageCount = objPagingRS.PageCount

' If the request page falls outside the acceptable range,
' give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1

' Check page count to prevent bombing when zero results are returned!
If iPageCount = 0 Then
	Response.Write "No records found!"
Else
	' Move to the selected page
	objPagingRS.AbsolutePage = iPageCurrent

	' Start output with a page x of n line
	%>
	<p>
	<font size="+1">Page <strong><%= iPageCurrent %></strong>
	of <strong><%= iPageCount %></strong></font>
	</p>
	<%
	' Spacing
	Response.Write vbCrLf

	' Continue with a title row in our table
	Response.Write "<table border=""1"">" & vbCrLf

	' Show field names in the top row
	Response.Write vbTab & "<tr>" & vbCrLf
	For I = 0 To objPagingRS.Fields.Count - 1
	    Response.Write vbTab & vbTab & "<th>"
	    Response.Write objPagingRS.Fields(I).Name
	    Response.Write "</th>" & vbCrLf
	Next 'I
	Response.Write vbTab & "</tr>" & vbCrLf

	' Loop through our records and ouput 1 row per record
	iRecordsShown = 0
	Do While iRecordsShown < iPageSize And Not objPagingRS.EOF
		Response.Write vbTab & "<tr>" & vbCrLf
		For I = 0 To objPagingRS.Fields.Count - 1
		    Response.Write vbTab & vbTab & "<td>"
		    Response.Write objPagingRS.Fields(I)
		    Response.Write "</td>" & vbCrLf
		Next 'I
		Response.Write vbTab & "</tr>" & vbCrLf

		' Increment the number of records we've shown
		iRecordsShown = iRecordsShown + 1
		' Can't forget to move to the next record!
		objPagingRS.MoveNext
	Loop

	' All done - close table
	Response.Write "</table>" & vbCrLf
End If

' Close DB objects and free variables
objPagingRS.Close
Set objPagingRS = Nothing
objPagingConn.Close
Set objPagingConn = Nothing


' Show "previous" and "next" page links which pass the page to view
' and any parameters needed to rebuild the query.  You could just as
' easily use a form but you'll need to change the lines that read
' the info back in at the top of the script.
If iPageCurrent > 1 Then
	%>
	<a href="Testpaging2.asp?page=<%= iPageCurrent - 1 %>&order=<%= Server.URLEncode(strOrderBy) %>">[&lt;&lt; Prev]</a>
	<%
End If

' You can also show page numbers:
For I = 1 To iPageCount
	If I = iPageCurrent Then
		%>
		<%= I %>
		<%
	Else
		
         %>
        <a href="Testpaging2.asp?page=<%= I %>&order=<%= Server.URLEncode(strOrderBy) %>"><%= I %></a>
		<%
         
	End If
Next 'I

If iPageCurrent < iPageCount Then
	%>
	<a href="Testpaging2.asp?page=<%= iPageCurrent + 1 %>&order=<%= Server.URLEncode(strOrderBy) %>">[Next &gt;&gt;]</a>
	<%
End If
response.write "<br>"
response.write "Total Page: " & iPageCount & "<Br>" & cint(iPageCount / 5)


' END RUNTIME CODE
%>

Open in new window

0
Comment
Question by:Webboy2008
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
Hi.

Looks like your issue may be in the loop displaying the results. Check section three of this article on CodeProject: http://www.codeproject.com/KB/database/rspaging.aspx

If you have difficulty, please advise; however, I find the CodeProject page easy to follow. Hoepfully you do too. 4GuysFromRolla.com has good examples. They are doing it the way you are with a counter, so might be a good double check for what is wrong. As I said, I believe it is in the loop, i.e., they use objPagingRS.PageSize whereas you are using iPageSize.

Kevin
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now