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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Creating and Managing Databases with phpMyAdmin in cPanel.
Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

744 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

12 Experts available now in Live!

Get 1:1 Help Now