?
Solved

Recordset paging in vb asp with mysql as a backend

Posted on 2007-11-17
5
Medium Priority
?
1,018 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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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