| Page Item | Value |
| Title | Easy ADO Recordset Paging |
| Description | This article demonstrates the use of ADO Recordset paging. Paging is invaluable for splitting up the results of database queries into manageable screens of data. |
| Keywords | tutorial, example, vbscript, asp, active server page, ado, paging, recordset, record set, paging, next, previous, access, sql server |
| Robots Meta Tag | |
| Page Content | HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | TOOLS | GALLERY | CONTACT Search: Go DEVELOPER TOOLS ASP Doc Tool ASP.NET Doc Tool SQL Doc Tool Index Server Companion The Website Utility TECHNICAL ARTICLES ASP ASP.NET JavaScript Transact SQL PHOTO GALLERIES Canon EOS 300D Samples Red Arrows 2004 Living Coasts Web Page Backgrounds More Galleries... NEW STUFF SQL Color Coder Canon EOS 300D Samples The Website Utility Search Engine Optimisation Build an ASP Search Engine My Tropical Fishtank Savings Other New Stuff... POPULAR STUFF Regular Expressions ASP Documentation Tool Index Server ASP JavaScript Ad Rotator LINKS Business Website ASPAlliance Articles
Home Articles Easy ADO Recordset Paging This article demonstrates the use of ADO Recordset paging. Paging is invaluable for splitting up the results of database queries into manageable screens of data. Introduction The following code will retrieve a Recordset from a data source, then format it in a table. The code should work on your system with only minor modifications. The only things you should need to be changing are the lines that define oConnection (the ADO connection) and the sSQLStatement SQL query. The oConnection variable should be a connection string. The sSQLStatement should be a SQL query. The code has been tested with SQL Server 7.0 and Access 2000 databases, although it should work with other databases supported by ADO. % 'Declare variables Dim iCurrentPage Dim iPageSize Dim i Dim oConnection Dim oRecordSet Dim oTableField Dim sPageURL 'Declare constants Const adOpenStatic = 3 'Open a RecordSet using a static cursor Const adLockReadOnly = 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 iCurrentPage = Request.QueryString( Page ) If iCurrentPage = Or iCurrentPage = 0 Then iCurrentPage = 1 'Set the number of records to be displayed on each page iPageSize = 3 'An ADO connection string oConnection = Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=PUBS_DATABASE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID= DATABASE_SERVER; User Id=PubsDBUser;PASSWORD=gt6Te4Ja; 'An SQL statement sSQLStatement = SELECT * FROM Publishers 'Create an ADO RecordSet object Set oRecordSet = Server.CreateObject( ADODB.Recordset ) 'Set the RecordSet PageSize property oRecordSet.PageSize = iPageSize 'Set the RecordSet CacheSize property to the 'number of records that are returned on each page of results oRecordSet.CacheSize = iPageSize 'Open the RecordSet oRecordSet.Open sSQLStatement, oConnection, adOpenStatic, adLockReadOnly 'Move to the selected page in the record set oRecordSet.AbsolutePage = iCurrentPage 'Display the opening HTML of a table Response.Write table border= 0 width= 50% cellpadding= 2 cellspacing= 0 Response.Write tr 'Loop through the fields in the RecordSet and 'display a column heading for each field For Each oTableField In oRecordSet.Fields Response.Write th width= 50% bgcolor= #008080 align= left font color= #FFFFFF b oTableField.Name /b /font /th Next Response.Write /tr Response.Write tr td width= 50% bgcolor= #C0C0C0 'Use the GetString method to display the database rows 'The GetString method has the following parameters: 'StringFormat = This should be set to 2 (or the adClipString ADO constant) 'NumRows = Number of RecordSet rows to be used 'ColumnDelimiter = Delimiter to be used between columns 'RowDelimiter = Delimiter to be used between rows 'NullExpr = Expression to use for null values Response.write oRecordSet.GetString(2, iPageSize, /td td width= 50% bgcolor= #C0C0C0 , /td /tr tr td width= 50% bgcolor= #C0C0C0 , ) Response.Write /td /tr /table 'Release database connectivity objects oRecordSet.Close set oRecordSet = nothing set oConnection = nothing % How the code sample works Obtaining field names A little known feature of Recordsets is the ability to easily obtain a list of field names contained within that Recordset. This saves a lot of time, and means that one results page could potentially be used for the results from different tables. Field names are obtained from the Fields collection of the Recordset object. Each Field in this collection has a corresponding Name property which contains the field name. The following code is used to retrieve a list of field names: For Each oTableField In oRecordSet.Fields Response.Write th width= 50% bgcolor= #008080 align= left font color= #FFFFFF b oTableField.Name /b /font /th Next Using the ADO Recordset GetString method The conventional method of rendering a Recordset in HTML is to loop through the records, writing a new table row for each record. An alternative method is to use the ADO Recordset s GetString method. This method also offers improved performance. The GetString method has the following parameters: StringFormat NumRows ColumnDelimiter RowDelimiter NullExpr StringFormat specifies how the Recordset should be converted to a string. The parameter should be set to 2 (corresponding to the adClipString ADO constant). NumRows is the number of records that should be converted into the returned Recordset. In the sample code above, the number of rows are contained in the iPageSize variable. ColumnDelimiter is the string that should be appended to each column. In this particular example this is a closing table cell tag ( /td ), followed by an opening table cell tag ( td ). RowDelimiter is the string that is appended to each row. In this example, it is a closing table cell tag, followed by a closing table row tag ( /tr ), then an opening table row tag ( tr ) followed by an opening table cell tag. Finally, NullExpr specifies what should be displayed if the particular Recordset field has a null value. Setting this parameter to nbsp; will add a HTML non-breaking space, and in Netscape browsers will prevent empty table cells from appearing blank. Navigating around the Recordset ADO allows Recordsets to be broken down into sections, called pages. Each of these pages of results can contain a user-specified number of records. The number of records contained within each page is controlled by the PageSize property of the Recordset. When returning a Recordset from the database, the records can be returned from a specific page by setting the AbsolutePage property. In the sample code above, the AbsolutePage property is set from the iCurrentPage variable, which is itself obtained from the Page parameter from the QueryString. In this way it is possible to introduce page navigation (the code for this is below). Adding links to other pages of results If you want to add links to all of the other pages of results, then use something like the following VBScript: % 'Display a list of links to all of the other pages of results For i = 1 to oRecordSet.PageCount If i = CInt(iCurrentPage) Then Response.Write [ Page i ] Else Response.Write [ a href= sPageURL ?Page= i Chr(34) Page i /a ] End If Next % An alternative method of navigation is to use links to the next and previous pages of results. This is achieved using the following: % '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(iCurrentPage) oRecordSet.PageCount Then Response.Write a href= sPageURL ?Page= (iCurrentPage + 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(iCurrentPage) 1 Then Response.Write a href= sPageURL ?Page= (iCurrentPage - 1 ) Previous Page /a End If % Displaying the number of records in the Recordset Obtaining the number of records in a Recordset is easily achieved since it is contained within the RecordCount property of the Recordset object. You can then show the user the number of records returned, e.g. % Response.Write( Your search has returned ) Response.Write(oRecordSet.RecordCount) Response.Write( records. ) % The disadvantage of using the RecordCount property is that it is not supported by all Recordsets. Further reading Recordset paging is also covered in the following articles: Recordset Paging with ADO 2.0 . Sams Teach Yourself Web Development With ASP in 24 Hours . Start learning ASP with this introductory guide. ASP Documentation Tool : Automate the generation of ASP (VBScript and JScript) technical documentation for your web applications! Useful Development Tools ASP Documentation Tool Automatically creates developer documentation for ASP 2.0 and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats. View Sample Output (HTML Help format). View Sample Output (HTML Format). Download Trial Version (5.2Mb ZIP file). ASP.NET Documentation Tool Automatically creates developer documentation for ASP.NET web applications written in C# or VB.NET. Documentation for SQL Server 7/2000 databases and C#/VB.NET components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats. View Sample Output (HTML Help format). View Sample Output (HTML Format). Download Trial Version (2.9Mb ZIP file). SQL Documentation Tool The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0 and 2000 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers and Table Relationships. View Sample Output (HTML Help format). View Sample Output (HTML Format). Download Trial Version (10.3Mb ZIP file). Index Server Companion The Index Server Companion is a Windows application that extends the functionality of Microsoft Index Server so that it is able to index content from remote websites and also from ODBC databases. As such it can be used as a low cost alternative to Site Server 3.0 Search. View Product Documentation (119K ZIP file). Try Sample Search Facility . Download Trial Version (1.7Mb ZIP file). The Website Utility The Website Utility examines websites for errors and areas that need to be optimised for search engines by using a built in web crawling engine. Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags. It also generates HTML for use in creating website site maps (table of contents pages - like this one ), and is able to create both client-side JavaScript Search Engines and server-side ASP Search Engines for a website. View Sample Output (HTML Help format). View Sample Output (HTML Format). Download Trial Version (3Mb ZIP file). Site Map All content is © 1995 - 2006 Brett Burridge |
| Image Alt Tags | Brettb.Com Microsoft Certified Professional View Sample Output (HTML Help format) View Sample Output (HTML Format) Download Trial Version View Sample Output (HTML Help format) View Sample Output (HTML Format) Download Trial Version View Sample Output (HTML Help format) View Sample Output (HTML Format) Download Trial Version View Product Documentation Try Sample Search Facility Download Trial Version View Sample Output (HTML Help format) View Sample Output (HTML Format) Download Trial Version ASP Documentation Tool - Free Trial Available! 1000MB and 40GB for $7.95 a month! |
| Internal Links | http://www.brettb.com/redirector.asp (12 links in this page) [ Robot View of this URL ] http://www.brettb.com/ASPDocumentationTool.asp (3 links in this page) [ Robot View of this URL ] http://www.brettb.com/Default.asp (3 links in this page) [ Robot View of this URL ] http://www.brettb.com/technicalwriting.asp (2 links in this page) [ Robot View of this URL ] http://www.brettb.com/DeveloperTools.asp (2 links in this page) [ Robot View of this URL ] http://www.brettb.com/TheWebsiteUtility.asp (2 links in this page) [ Robot View of this URL ] http://www.brettb.com/JavaScriptArticles.asp [ Robot View of this URL ] http://www.brettb.com/Website_Search_Engine_Optimisation.asp [ Robot View of this URL ] http://www.brettb.com/SearchResults.asp [ Robot View of this URL ] http://www.brettb.com/js_banner_ad_rotator.asp [ Robot View of this URL ] http://www.brettb.com/web.asp [ Robot View of this URL ] http://www.brettb.com/CanonEOS300D_Gallery1.asp [ Robot View of this URL ] http://www.brettb.com/BuildingAnASPSearchEngine.asp [ Robot View of this URL ] http://www.brettb.com/backgrounds.asp [ Robot View of this URL ] http://www.brettb.com/VBScriptRegularExpressions.asp [ Robot View of this URL ] http://www.brettb.com/toc.asp [ Robot View of this URL ] http://www.brettb.com/ASP.NETArticles.asp [ Robot View of this URL ] http://www.brettb.com/Investments_ISAs.asp [ Robot View of this URL ] http://www.brettb.com/TransactSQLColorCoder.asp [ Robot View of this URL ] http://www.brettb.com/MyTropicalFishtank.asp [ Robot View of this URL ] http://www.brettb.com/CanonEOS300D_Gallery3.asp [ Robot View of this URL ] http://www.brettb.com/ASPWatchArticles.asp [ Robot View of this URL ] http://www.brettb.com/Red_Arrows_2004.asp [ Robot View of this URL ] http://www.brettb.com/Living_Coasts_Photos.asp [ Robot View of this URL ] http://www.brettb.com/SQL_Help.asp [ Robot View of this URL ] http://www.brettb.com/contact.asp [ Robot View of this URL ] http://www.brettb.com/Biotechnology.asp [ Robot View of this URL ] http://www.brettb.com/Gallery.asp [ Robot View of this URL ] http://www.brettb.com/ASPNetDocumentationTool.asp [ Robot View of this URL ] http://www.brettb.com/gallery.asp [ Robot View of this URL ] http://www.brettb.com/what's_new.asp [ Robot View of this URL ] http://www.brettb.com/IndexServerCompanion.asp [ Robot View of this URL ] http://www.brettb.com/SearchingIndexServerWithASP.asp [ Robot View of this URL ] |
Report generated by The Website Utility 2.8