Solved

Recordset paging in vb asp with mysql as a backend

Posted on 2007-11-17
5
1,016 Views
Last Modified: 2012-08-13
Hi,

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
	Else
	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">
        <tr>
          <td height="20" colspan="5" class="centreHeaderText">CustomerList </td>
        </tr>
        <tr>
          <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(rsVCustomerList.RecordCount)
Response.Write("  customers listed.")
%>		  </td>
        </tr>
 
	<tr>
	      <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>
        </tr>
 
	<% 
	While ((VCustomerList1__numRows <> 0) AND (NOT rsVCustomerList.EOF)) 
	%>
 
	    
	    <tr>
          <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>
	  </tr>
 
	<% 
  	VCustomerList1__index=VCustomerList1__index+1
  	VCustomerList1__numRows=VCustomerList1__numRows-1
  	rsVCustomerList.MoveNext()
	Wend
	%>
	</table>
 
<%
'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 & " ] "
Else
Response.Write "[ <a href=""" & sPageURL &"?Page=" & i & Chr(34) & ">Page " & i & "</a> ] "
End If
 
Next
%>        
 
 
          <br />
          <%
Response.Write(rsVCustomerList.RecordCount)
Response.Write("  customers listed.")
%>
	<%
	rsVCustomerList.Close()
	Set rsVCustomerList = Nothing
	%>
	
	
	</td>
    <td width="1" rowspan="2" valign="top"></td>
  </tr>
  <tr>
    <td valign="baseline"></td>
  </tr>
</table>
 
</body>
</html>
 
<%
OBJdbConn.Close
Set OBJdbConn = Nothing
%>

Open in new window

0
Comment
Question by:BenthamLtd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
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:
http://www.4guysfromrolla.com/webtech/070500-1.shtml
0
 

Author Comment

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

Expert Comment

by:kevp75
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.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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