Hi Lists,
I hava an application i am trying to do paging through recordset on an access database through ASP page.
The Page displays the correct records and resize to the records i have specified, counts the number of pages and when the mouse hovers on next it gives the right page number.
However, whenever, i click on the next page, it displays the total number of records in the database and paging takes place from there. Which i am not interested. I want to page on the displayed records and not total recordset.
My code is below.
<%
Response.Buffer = true
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
Const adOpenStatic = 3
Const adCmdText = &H0001
Const SCRIPT_NAME = "GISmetadata.asp"
'Const CursorLocation = 2
'................................................. .............
const adOpenKeyset = 1
const adOpenDynamic = 2
const adLockPessimistic = 2
const adLockOptimistic = 3
const adLockBatchOptimistic = 4%>
<html>
<head>
<title>Spatial Data Repository</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<%
'#################### START CLASS DEFINITION ########################
Class DBSearchEngine
'private variables
Private hTmpCount, bSearchTitles, bSearchDescriptions
'public variables become properties of the class
Public DBConnectionString
Public DBConnectionAccount
Public DBConnectionAcctPassword
Public bNoFieldsToSearch
Public SearchType
Public GetAll
Public DataType
Public abspage, pagecnt
Public Property Get Version
Version = "2.0"
End Property
'public methods
Public Sub SearchTitles
bSearchTitles = true
End Sub
Public Sub SearchDescriptions
bSearchDescriptions = true
End Sub
'................................................. .................................................. .....
Public Function Search(ByVal sKeyword)
' ADO constants used in this page
'................................................. .............
Dim strSQL, objConn, objRs, strTemp, i, AbsolutePage
strTemp = ""
sKeyWord = EscapeApostrophe(sKeyword)
strSQL = sqlString( sKeyword )
If strSQL = "" then
strTemp = "<p>Enter keywords to search</p>"
search = strTemp
exit function
End if
'open db
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
objRs.PageSize = 15
objRs.CursorType = 0
objRs.CacheSize = 15
objRs.CursorLocation = adUseClient
objConn.Open DBConnectionString, DBConnectionAccount, DBConnectionAcctPassword
objRs.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
'................................................. ..
If Len(Request.QueryString("pagenum")) = 0 Then
objRs.AbsolutePage = 1
Else
If CInt(Request.QueryString("pagenum")) <= objRs.PageCount Then
objRs.AbsolutePage = Request.QueryString("pagenum")
Else
objRS.AbsolutePage = 1
End If
End If
Dim abspage, pagecnt
abspage = objRS.AbsolutePage
pagecnt = objRS.PageCount
'................................................. .................................................. ....................................
'retrieve recordcount
hTmpCount = objRs.RecordCount
if objRs.BOF then
strTemp = "<h3>No Matching Records</h3>"
else
End if
If Not objRs.EOF Then
Response.Write "Page : " & objRS.AbsolutePage & "of " & objRS.PageCount & "<br>" & vbcrlf
Response.Write "<br><br>" & vbcrlf
End if
'start at first record...
'objRs.MoveFirst
strTemp = Chr(10) & "<table>" & Chr(10)
'loop through all records
i = 0
'Do While NOT objRs.BOF AND NOT objRs.EOF
i = i + 1
For i = 1 to objRs.Pagesize
' call the function HTMLdisplay to properly format a valid entry for the results build return string, with nicely
'formatted html (not)
strTemp = strTemp & HTMLdisplay(objRs)
'strTemp(i) = HTMLdisplay(objRs)
objRs.MoveNext
If ObjRs.EOF then exit for
'Loop
Next
'end if
strTemp = strTemp & Chr(10) & "</table>" & Chr(10)
objRs.Close
'objConn.Close
Set objRs = Nothing
Set objConn = Nothing
'................................................. .................................................. .....
'return the built string of results...
Search = strTemp
Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=1""><b>First Page</b></a>"
Response.Write " | "
If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage - 1 & """><b>Previous Page</b></a>"
End If
Response.Write " | "
If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage + 1 & """><b>Next Page</b></a>"
Else
Response.Write "<span style=""color:silver;""" & ">Next Page</span>"
End If
Response.Write " | "
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & pagecnt & """><b>Last Page</b></a>"
Response.Write "</div>" & vbcrlf
End Function
'................................................. ................................
Private Function sqlString(ByVal keywords)
Dim i
'the base string
sqlString = "SELECT * FROM qryMetaData "
sqlString = sqlString & "WHERE "
sqlString = sqlString & "((Abstract LIKE '%" & Keywords & "%') OR (Title LIKE '% " & Keywords & "%')) "
If DataType = "2" then
sqlString = sqlString & " AND (DATAFORM LIKE '%MapInfo%') "
Elseif DataType = "3" then
sqlString = sqlString & " AND ((DATAFORM = 'PDF') OR (DATAFORM = 'IMAGE')) "
End if
sqlString = sqlString & "ORDER BY [TITLE] ASC;"
End Function
'................................................. .................................................. ...........
Public Function HTMLdisplay(ByRef objRs)
Dim MyFile
HTMLdisplay = Tabs(2) &"<tr>" & Chr(10)
HTMLdisplay = HTMLdisplay & Tabs(3) & "<td width = " & Chr(34) & "85%" & Chr(34) & ">" & Chr(10)
MyFile = ExtractFileName(objRs("Title"))
If Session("iSecurityLevel") = 3 then
HTMLdisplay = HTMLdisplay & Tabs(4) & "<b><a href="& Chr(34) & "http://testmap/Data/MetaTest/Detail no downloading.asp?lngDataID=" & objRs("NGDFDISCOVERYMETADATA_ID") & Chr(34) & " class=" & Chr(34) & "boldedlink"&Chr(34)&">"
else
HTMLdisplay = HTMLdisplay & Tabs(4) & "<b><a href="& Chr(34) & "http://testmap/Data/MetaTest/Detail.asp?lngDataID=" & objRs("NGDFDISCOVERYMETADATA_ID") & Chr(34) & " class=" & Chr(34) & "boldedlink"&Chr(34)&">"
End if
HTMLdisplay = HTMLdisplay & objRs("TITLE")
End Function
'................................................. .................................................. .....
Public Function Count
count = hTmpCount
End Function
'private class routines
Private Sub Class_Initialize()
'class_initialize event is best used to set
'up default values for class scoped variables
bSearchTitles = false
bSearchDescriptions = false
End Sub
Private Function EscapeApostrophe(ByVal toEscape)
'make "my dog's fleas" become "my dog''s fleas",
'escaping the apostrophe character because
'it's meaning is special in TSQL...
EscapeApostrophe = replace(toEscape, "'", "''")
End Function
Function engdate(d)
darr=split(d,"/")
if darr(2) > 20 then century="19" else century="20"
engdate=twochar(darr(1)) & "/"& twochar(darr(0))
engdate=engdate & "/"& century & twochar(darr(2))
End function
Function twochar(x)
twochar=right(100+x,2)
End function
Function ChangeFileExt(Byval Afile,Byval Ext )
Dim i
i = Len(Afile)
i = abs(i - 4)
Afile = Left(Afile,i)
Afile = Afile + Ext
ChangeFileExt = Afile
End Function
Function ExtractFileName(Afile)
Dim strTemp
Dim i
Dim OutStr
If IsNull(Afile) then
ExtractFileName = ""
Exit function
End if
For i = 1 to Len(Afile)
strTemp = Right(Afile,i)
If Left(strTemp,1) <> "\" then
OutStr=StrTemp
Else
Exit for
End if
Next
ExtractFileName = OutStr
End Function
Private Function Tabs(Num)
Dim i
Dim TempStr
For i = 1 to Num
TempStr = TempStr & Chr(9)
Next
Tabs = TempStr
End Function
End Class
'#################### END CLASS DEFINITION ########################
%>
</body>
</html>
Any help would be deeply appreciated.
Thank you.
Tuffour
Paging Through Recordsets
