Recordset Paging using the Table Object Model

Recordset Paging Using the Table Object Model

by David Halls

Introduction

Table generated using the Table Object Model

I am interested in developing standalone programmes using VBScript and HTML pages. These should be able to run on any computer that uses Internet Explorer as a browser. In considering how to interact with a database, I looked into how to page through a database and came up with a solution using the Table Object Model which allows a table to be refreshed with new data dynamically. This article describes how to do this.

Approaches using ASP

As VBScript is widely used in retrieving database data from a server using ASP, it seemed sensible to look at how it is done with that approach. Normally, after the recordset is retrieved from the database, the first set of records are retrieved from the server to fill the first page of results. On requesting the next page, the server then supplies results for the next set of records and a new page is created [1,2]. The database needs to be kept open for these transactions.

A faster way [3 - 7] is to load all the records into a two-dimensional array using the expression,

Myarray = Recordset.GetRows([rows], [start], [fields])

The database can then be closed to free resources. However, in paging through the recordsets, you still have to get back to the server to request the next set of records from the array and produce a new page.

If, however, you are working with a database on the host computer, all the data is there in memory and you should not need to create a new page. This is where the Table Object Model comes in as you can change the data in the table without redrawing the whole page[8].

Connecting to the Database and Retrieving Data

First, we need to connect to the database and put the data in an array using GetRows.

The example shown here uses an Access database ("stock.mdb") connected through a DSN connection which was established with "Data Sources (ODBC)" found under "Administrative Tools" in the "Control Panel" of Windows. The database is information on a stock of cars and data in all the fields in a table ("Cars") are collected through the SQL query. All this is set up in a subroutine "dbconnect" for convenience. As the database is on the local computer, we use CreateObject("ADODB.Connection") instead of Server.CreateObject("ADODB.Connection") used in ASP methods.

 
'Constants used with the recordset
Const adUseClient = 3
Const adOpenStatic = 3


'Set up variables 
Dim objConn, objRS, strQuery
Dim strConnection, currentpage,totpages,numperpage, linenum
Dim fieldarray(10), fieldtitle


sub dbconnect 
Set objConn = CreateObject("ADODB.Connection")
strConnection = "DSN=Stock;Database=Stock"
objConn.Open strConnection
currentpage = 1
'SQL query to select all the data
strQuery = "Select * FROM Cars"
'Set up the recordset
Set objRS = CreateObject("ADODB.Recordset")
objRS.CursorType = adOpenStatic
objRS.CursorLocation = adUseClient
Set objRS.ActiveConnection = objConn
objRS.Open(strQuery)
'Define the pagesize and count the number of pages needed to display the database
objRS.Pagesize = numperpage
totpages = objRS.pagecount
'put all the data in an array
alldata = objRS.getrows
'Find the number of columns and rows
numcols = ubound(alldata,1)
numrows = ubound(alldata,2)
'Put all the names of the fields in an array
for i = 0 to numcols
fieldarray(i) = objRS.fields(i).name
next
end sub

Our business with the database and recordset is now finished as all the information is in arrays or variables. The objects are closed and set to nothing.

 
sub closedb
objRS.Close
objConn.close
Set objRS = nothing
Set objConn = nothing
end sub

Setting up the Table to Display the Data

Next we create the table using "document.write" statements to produce the HTML. The elements are named using "ID" so that we can work with the Table Object Model. Headings are added but the cells are left blank at this stage

 
sub setuptable(numrows,numcols)
dim blank, otable,otbody, orow , ocell, i, j, numcols, numrows, alldata
'Define a blank cell content. 
'N.B. There should be no gap between & and nbsp; in the following statement.
'Writing HTML codes in an HTML page gives some problems. 
blank ="& nbsp;"
'Set the number of records to be shown on a page
numperpage = 10

'Set up Header
With document
 .write "<table id='otable' cellpadding='10' width='500' bgcolor='#ffffce' border='0'>"
'Name header as an object
 .write "<thead id = 'oTHead'>"
end with
'Insert a row into the header
'Populate it with cells whose innertexts are the fields of the field array
set orow = document.all.oTHead.InsertRow()
for i = 0 to numcols
	set ocell = orow.insertCell(i)
	with ocell
		.style.color = "maroon"
		.style.fontweight ="bold"
		.style.textalign = "center"
		.style.width = "125"
		.style.background = "silver"
		.innertext = fieldarray(i)
	end with 
next
document.write "</thead>"

'Set up body of table which is named as the object otbody
document.write "<tbody id='otbody'>"
'set up the number of rows as defined by numperpage and populate these with blank cells
for i = 0 to numperpage -1
	set orow = document.all.otbody.insertrow()
	for j = 0 to numcols
		set ocell = orow.insertcell(j)
		ocell.style.textalign = "center"
		ocell.innerhtml = blank
	next
next
With document
 .write "</tbody>"
 .write "</table>"
 .write "<br />"

'Add links for paging
 .write "<a href = '#' onclick = 'previouspage'>Previous</a> |"
for i = 1 to totpages
	.write "<a href = '#' onclick = 'numberedpage(" & i& ")'>" & i & "</a> | "  
next
 .write "<a href = '#' onclick = 'nextpage'>Next</a>"
end with
end sub

Populating the Table with Data

We will need to populate the data initially and then in response to the links as:

A page number
A previous page
The next page

A sub routine is needed for all cases to populate the table for a given page number. First we calculate the starting number for the array from the page number and the number of columns per page. The cells are then filled with data from that starting number.

 
sub populatetable(pagenum)
Dim startnum
'calculate where to start in the array of data
startnum =(pagenum-1)*numperpage
linenum = 0
'If there is data for that number in the array, fill the cells otherwise insert blanks
for i = startnum to (startnum + numperpage-1) 
	for j = 0 to numcols
		if i <= numrows then	
		document.all.otbody.rows(linenum).cells(j).innerhtml = alldata(j,i)
		else
		document.all.otbody.rows(linenum).cells(j).innerhtml = blank
		end if
	next
	linenum = linenum + 1
next
end sub

The routines then are relatively simple. We need to ensure that with "previous and "next" we do not cause an error by going beyond the limits.

 
sub previouspage
if pagenum > 1 then 
	pagenum = pagenum - 1
	call populatetable(pagenum)
end if
end sub

sub nextpage
if pagenum < totpages then 
	pagenum = pagenum + 1
	call populatetable(pagenum)
end if
end sub

sub numberedpage(i)
pagenum = i
call populatetable(pagenum)
end sub

Setting up the whole operation in the HTML page

All the functions shown above can go into the HEAD section of an HTML page. The functions are then called in sequence with a VBScript routine in the BODY of the page.

 
'connect to database to get array
call dbconnect
'disconnect from database
call closedb
'set to first page
pagenum = 1

'set up table with blanks
call setuptable(numrows,numcols)
'fill with data for page number(pagenum) = 1
call populatetable(pagenum)

References

Download

Code and sample database package

The files will need to be loaded onto your computer and a DSN connection set up with "Data Sources (ODBC)" found under "Administrative Tools" in the "Control Panel" of Windows. Under "User DSN", add a new DSN, select a Microsoft Access Driver, name the Data Source Name "stock" and browse to the database on your computer. This works in XP; I am not sure of the procedure in Vista and Windows 7.

DJH Web, 1A Bloomfield Road, Lesmahagow, Lanark ML11 0DF
Tel: 01555 894949
e-mail:davidhalls@onetel.com