Recordset paging in vb asp with mysql as a backend

Posted on 2007-11-17
Last Modified: 2012-08-13

I'm in the process of migrating a website that has a ms sql server as a backend to a mysql 5.0 database.

I have created all the views in mysql but am getting stuck with the recordset paging.

Below is an example of the code that works well on the current site, but doesnt work on the new site with a mysql back end.

	'Declare variables

	'Page the most recent match reports with pictures

	Dim iCurrentCustomerListPage

	Dim iCustomerListPageSize

	Dim rsVCustomerList

	Dim rsVCustomerList_numRows

	Dim sPageURL

	'Declare constants

	Const adOpenStaticCustomerList = 3 'Open a RecordSet using a static cursor

	Const adLockReadOnlyCustomerList = 1 'Open a RecordSet in read-only mode

	'Retrieve the name of the current ASP document

	sPageURL = Request.ServerVariables("SCRIPT_NAME")


	'Retrieve the current page number from the QueryString

	iCurrentCustomerListPage = Request.QueryString("Page")

	If iCurrentCustomerListPage = "" Or iCurrentCustomerListPage = 0 Then iCurrentCustomerListPage = 1

	'Set the number of records to be displayed on each page

	iCustomerListPageSize = 50

	'An SQL statement

	sSQLStatementCustomerList = "SELECT * From vLookup1"

	'Create an ADO RecordSet object

	OBJdbConn.CommandTimeout = 0

	Set rsVCustomerList = Server.CreateObject("ADODB.Recordset")

	'Set the RecordSet PageSize property

	rsVCustomerList.PageSize = iCustomerListPageSize

	'Set the RecordSet CacheSize property to the

	'number of records that are returned on each page of results

	rsVCustomerList.CacheSize = iCustomerListPageSize

	'Open the RecordSet

	rsVCustomerList.Open sSQLStatementCustomerList, OBJdbConn, adOpenStaticCustomerList, adLockReadOnlyCustomerList

	If NOT rsVCustomerList.eof then

	'Move to the selected page in the record set

	rsVCustomerList.AbsolutePage = iCurrentCustomerListPage


	End If



	Dim VCustomerList1__numRows

	Dim VCustomerList1__index

	VCustomerList1__numRows = iCustomerListPageSize

	VCustomerList1__index = 0

	rsVCustomerList_numRows = rsVCustomerList_numRows + VCustomerList1__numRows


	<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">


          <td height="20" colspan="5" class="centreHeaderText">CustomerList </td>



          <td height="20" colspan="5">



'If the current page number is less than the

'total number of pages then display a link

'to the next page of results

If CInt(iCurrentCustomerListPage) < rsVCustomerList.PageCount Then

Response.Write "<a href=""" & sPageURL &"?team="&iTeam& "&Page=" & (iCurrentCustomerListPage + 1 ) & """>Next Page</a> "

End If

'If the number of the current page is greater than

'the first page then display a link to the previous

'page of results

If CInt(iCurrentCustomerListPage) > 1 Then

Response.Write "<a href=""" & sPageURL &"?team="&iTeam& "&view=page&Page=" & (iCurrentCustomerListPage - 1 ) & """> Previous Page</a> "

End If


          <br />



Response.Write("  customers listed.")

%>		  </td>



	      <td width="10%" height="20" align="left" class="leftTextwhite">Title</td>

	      <td width="24%" align="left" class="leftTextwhite">Forename</td>

	      <td width="26%" align="left" class="leftTextwhite">Surname</td>

	      <td width="27%" align="left" class="leftTextwhite">Last Ordered </td>

	      <td width="13%" class="leftTextwhite">&nbsp;</td>



	While ((VCustomerList1__numRows <> 0) AND (NOT rsVCustomerList.EOF)) 




          <td height="20" align="left" class="leftTextwhite"><%=rsVCustomerList("Customer_Title")%></td>

          <td height="20" align="left" class="leftTextwhite"><%=rsVCustomerList("Customer_Forename")%></td>

          <td height="20" align="left" class="leftTextwhite"><%=rsVCustomerList("Customer_Surname")%></td>

          <td height="20" align="left" class="leftTextwhite"><%=rsVCustomerList("OrderDate")%></td>

	      <td height="20" class="leftTextwhite"><a href="customerdetails.asp?c_urn=<%=rsVCustomerList("Customer_URN")%>">View</a></td>










'Display a list of links to all of the other pages of results

For i = 1 to rsVCustomerList.PageCount

If i = CInt(iCurrentCustomerListPage) Then

Response.Write "[ Page " & i & " ] "


Response.Write "[ <a href=""" & sPageURL &"?Page=" & i & Chr(34) & ">Page " & i & "</a> ] "

End If



          <br />



Response.Write("  customers listed.")




	Set rsVCustomerList = Nothing





    <td width="1" rowspan="2" valign="top"></td>



    <td valign="baseline"></td>







Set OBJdbConn = Nothing


Open in new window

Question by:BenthamLtd
  • 2
LVL 25

Accepted Solution

kevp75 earned 500 total points
ID: 20304843
personally I would go a totally different route with your paging methods on both sides...

use .GetRows and then page the 2d array:

Author Comment

ID: 20304883
Does this method work on both mySQL and MS SQL databases?
LVL 25

Expert Comment

ID: 20307643
It should.  The code doesn't have much to do with the database, more like the provider.  So if you use the ADO provider for mySQL in your connection string you should be all set.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Introduction This article is intended for those who are new to PHP error handling (  It addresses one of the most common problems that plague beginning PHP develop…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

23 Experts available now in Live!

Get 1:1 Help Now