The paging display of database records performed on the client

Pagination | client | data | database | show | execute

Introduction

One of the most common tasks in ASP applications is to display the query results of a database in a paging format. For example, when working with a large number of records, you should display only 10 items at a time, making it easier for visitors to use this information. There are already many articles on the web that demonstrate several ways to page through the results of a database query, with better articles including:

Paging through database records using stored procedures

Database Paging Sample Code

Paging records by using GetRows

But all of the methods described in these articles are paging through the server side. For example, if there are 30 database records, we want to display 10 records at a time. When the user requests the first page of data, the ASP page extracts the top 10 records and sends them to the customer. When the user is ready to read the following 10 records, he clicks on a link, when the ASP page is reloaded, through the query string passed in a new page value, the ASP page will take out the 11th to 20 records, and send them to the user.

In this article, we will migrate this entire process to the client. When users view data online, all 30 records are sent to the client in the form of a client-side JavaScript array. Additionally, the attached client JavaScript code is responsible for displaying records 1th through 10th, and linking to the following or previous recordset. When you click on these links, the client JavaScript code is executed and new data is displayed. Since these are all occurring on the client side, it is only necessary to contact the Web server when the page is first loaded, thereby obviating the data transfer consumption between the server and the client, and undoubtedly greatly improving the performance of the application.

Required client-side scripting

What client script should the ASP page generate to implement a database record that displays pagination on the client? As mentioned earlier, we need to create an array of clients. Because we're going to display database records in an HTML table, we're going to create an HTML array that might be used to display an HTML table row ( database Value ). This array will contain all the database elements that we want to allow the user to scroll through.

We also need a client-side JavaScript function to display a subset of these array values. Using this function, we can display the top N records, and when the user clicks on a link, it can display the back (or front) n Records. In other words, this function is responsible for client paging.

Finally, we need some methods to dynamically change the display of an HTML page without updating the page. This can be done through DHTML (Dynamic HTML). To do this, we need to create an HTML div tag where all the output is stacked, and then use the client JavaScript code to dynamically modify the content in this div tag. For more information about using DHTML, read the introduction to Dynamic HTML and the cross-browser DHTML tutorial.

Create a paging class

Keep in mind that the entire application here includes only one ASP page. When this ASP page is accessed, it creates all the client JavaScript code that is required to perform the paging of records. To simplify the process, I created a VBScript class to handle this feature. When using this class, the developer simply passes in the recordset that he wants to page through the visitor's Web browser. For the use of classes, read about using classes in VBScript.

I named this class Dhtmlgetrows, which contains two properties and one method. Two properties are:

1, Recsperpage: Determine how many records each page display.

2, Thstring: Through an HTML table to show this is the result of pagination; This property allows you to specify a string for the title of the table.

A single approach is generatehtml (recordsetobject), which returns complete HTML for the paging application: Client JavaScript code and the required div tag. This method requires only one parameter recordsetobject, which should be a Recordset object that populates the database data that you want to display in a paging format.

The code for this class is quite long, and most of the code simply returns the JavaScript code for the client. The following is the code for the class:

<%
Class dhtmlgetrows

' ******* PRIVATE member VARIABLES **********
Private Irecsperpage
Private strthstring
'*******************************************

' ************ Initialize Event *************
Private Sub Class_Initialize ()
Irecsperpage = Ten ' Assign a default value
End Sub
'*******************************************

' ************ Property Let/get *************
Public Property Let Thstring (strvalue)
' Replace all apostrophes with \ '
strthstring = Replace (strvalue, "'", "\")
End Property

Public Property Get Thstring ()
thstring = strthstring
End Property

Public Property Let Recsperpage (Ivalue)
If ivalue > 0 and IsNumeric (ivalue) Then
Irecsperpage = CInt (ivalue)
End If
End Property

Public Property Get Recsperpage ()
Recsperpage = Irecsperpage
End Property
'*******************************************

' **************** METHODS ******************
Public Function generatehtml (OBJRS)

' Begin by getting an array of the ' data
Dim avalues
Avalues = Objrs.getrows ()

' Find the value of rows and columns
Dim Icols, Irows
Icols = UBound (avalues, 1)
irows = UBound (avalues, 2)

Dim Stroutput
' Display the initial script block
Stroutput = "

Dim Iloop, Icolloop, strtmp
For iloop = 0 to Irows
Stroutput = stroutput & "tablerow[" & Iloop & "] = ' '

For icolloop = 0 to Icols
' Fix apostrophes
strtmp = Replace (Avalues (Icolloop, Iloop), "'", "\")

' Remove Carraige returns
strtmp = Replace (strtmp, VbCrLf, "")

Stroutput = stroutput & "" & strtmp & ""
Next ' Icolloop

Stroutput = stroutput & ";" & VbCrLf
Next ' Iloop

' Init global varaibles and find out what browser the ' user ' using
Stroutput = stroutput & vbCrLf & vbCrLf & "var 0;" & vbCrLf & _
"var last =" & Irecsperpage & ";" & VbCrLf & _
"Var Mynav & vbCrLf &" if (Navigator.appname = "" Netscape ")" & _
VbCrLf & VbTab & "Mynav =" "NS" ";" & VbCrLf & _
' If (navigator.appname = "" Microsoft Internet Explorer "") "& _
VbCrLf & VbTab & "Mynav =" "IE" ";" & VbCrLf & _
VbCrLf & "" & VbCrLf & VbCrLf

' Now display the HTML table
Stroutput = stroutput & vbCrLf & "

" & VbCrLf & _
VbCrLf & vbCrLf & "

' Write the NAV function
Stroutput = stroutput & "function nav (ival) {" & VbCrLf & _
"//Do we want to move forward or backwards?" & VbCrLf & _
"if (ival = = 1) {" & VbCrLf & VbTab & "A/+" & _
Irecsperpage & "& VbCrLf &" last + + & Irecsperpage & _
VbCrLf & "}" & VbCrLf & "Else If" (ival = 1) {"& VbCrLf & VbTab & _
"& Irecsperpage &"; "& VbCrLf & VbTab &" Last-= "& _
Irecsperpage & ";" & VbCrLf & "}" & VbCrLf & _
VbCrLf & vbCrLf & "var txt = ';" & VbCrLf & _
"TXT + + '

"" & VbCrLf

' Do we need to add a TH string?
If Len (strthstring) > 0 Then
Stroutput = stroutput & "txt = = ' " & strthstring & " ';" & VbCrLf
End If

Stroutput = Stroutput & "for (var iloop = i; Iloop; iloop++) "& VbCrLf & _
VbTab & "If" (Iloop <= "& irows &") txt + tablerow[iloop]; "& VbCrLf & _
"txt = = ' ';" & VbCrLf & VbCrLf

' Now, show Next/prev links if applicable
Stroutput = Stroutput & "if (0)//Show Prev link" & vbCrLf & _
VbTab & "txt + = ' prev "& _
Irecsperpage & " ';" & VbCrLf & VbCrLf & _
' If (last <= ' & irows & ')//Show Next link "& VbCrLf & VbTab & _
"TXT + + ' next "& _
Irecsperpage & " ';" & VbCrLf & VbCrLf

' Write out the new HTML content to the DIV tag
Stroutput = stroutput & "//write out the" DIV tag depending on browser ... "& VbCrLf & _
"If" (Mynav = "NS" ") {" & VbCrLf & VbTab & _
"Document.layers[' grid '].document.write (TXT);" & VbCrLf & VbTab & _
"Document.close ();" & VbCrLf & "}" & VbCrLf & VbCrLf & _
"If" (Mynav = = "" IE "") "& VbCrLf & VbTab & _
"Document.all[' grid '].innerhtml = txt;" & VbCrLf & VbCrLf & _
"}" & VbCrLf & VbCrLf

Stroutput = stroutput & "NAV (0);" & VbCrLf & ""

generatehtml = Stroutput
End Function
'*******************************************
End Class

%>

Before I finish, I want to quickly explain how to use this class in an ASP page. Because this class contains only one method, it is fairly straightforward to use this class. All you have to do is create and populate a Recordset object, then create an example of a class and use the Response.Write output objclassinstance.generatehtml (objRS) value.

!--#include file= "dhtmlGetRows.class.asp"-->
<%
' Create and populate a Recordset
Dim objRS, objconn, strSQL
Set objconn = Server.CreateObject ("ADODB. Connection ")
objConn.Open "Dsn=mydsn"

strSQL = "Select top Viewcount, Description" & _
"From Tblfaq ORDER by Viewcount DESC"

Set objRS = objConn.Execute (strSQL)

' Create An instance of the ' Dhtmlgetrows class
Dim Objpagedresults
Set objpagedresults = new Dhtmlgetrows

objpagedresults.thstring = " views FAQ question "
Response.Write objpagedresults.generatehtml (objRS)

' Clean up ...
Set objpagedresults = Nothing

Objrs.close
Set objRS = Nothing

Objconn.close
Set objconn = Nothing
%>

The code fragment above assumes that the Dhtmlgetrows class can be used in a server-side include file dhtmlGetRows.class.asp.