Solved

Recordset paging in vb asp with mysql as a backend

Posted on 2007-11-17
5
1,012 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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

810 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