Solved

Recordset paging in vb asp with mysql as a backend

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Optimize the query 5 52
Html value of radio 14 44
MS SQL 2008 and stored prodcures and dates 5 26
Why is my $_POST not going to results page 10 38
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

749 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