Solved

Recordset paging in vb asp with mysql as a backend

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with query 3 31
Button to go back 3 28
Load data upon clicking a button 8 34
Code Manager | Snippits 2 36
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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/…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

856 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