Paging with recordsets/sql
Shannon_512 | Posted 8:21pm 29. August 2001 Server Time |
Please help me see what is wrong with my coding or sql statements? I have been unable to do the paging part allowing it to only display select partitions although the sql statement should be alright. Instead, all the partitions in the access database were listed out.Is there something wrong with my sql statements?
Here is the codes:
<% Option Explicit
' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>
<html>
<head>
<Title> Display OS PAge</title>
<style>
body { font-family : Verdana; font-size : 8pt; }
a { font-family : Verdana; font-size : 8pt; text-decoration : none; }
</style>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body background="image/wallPaper2-1.jpg">
<%
Dim connStr
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("pc.mdb")
Set connStr=Server.CreateObject ("ADODB.Connection")
connStr.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("pc.mdb")
Dim adoConnection, adoRecordset
Dim osys, SQL
osys = Request.Form("OS")
Set adoConnection = Server.CreateObject("ADODB.Connection")
adoConnection.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("pc.mdb")
SQL = "SELECT * FROM Partitions WHERE OS='& osys &' Order By PartID;"
Set rs = adoConnection.Execute(SQL)
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.PageSize = 12
rs.CacheSize = 12
rs.CursorLocation = adUseClient
rs.Open "Partitions", connStr, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
If Len(Request("pagenum")) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum")) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum")
Else
rs.AbsolutePage = 1
End If
End If
Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount
If Not rs.EOF Then
Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf
Response.Write "Total number of records : " & rs.RecordCount & "<br><br>" & vbcrlf
Dim fldF, intRec
Response.Write "<table border=1 align=center cellpadding=5 cellspacing=0><thead><tr>"
Response.Write "<tr>"
Response.Write "<td bgcolor=Blue width=120 height=27><b><FONT COLOR=FFFFFF size=-2> OS </b></td>"
Response.Write "<td bgcolor=Blue width=60 height=27><b><FONT COLOR=FFFFFF size=-2> PC No. </b></td>"
Response.Write "<td bgcolor=Blue width=120 height=27><b><FONT COLOR=FFFFFF size=-2> Partition </b></td>"
Response.Write "<td bgcolor=Blue width=120 height=27><b><FONT COLOR=FFFFFF size=-2> Browser1 </b></td>"
Response.Write "<td bgcolor=Blue width=120 height=27><b><FONT COLOR=FFFFFF size=-2> Browser2 </b></td>"
Response.Write "<td bgcolor=Blue width=120 height=27><b><FONT COLOR=FFFFFF size=-2> Browser3 </b></td>"
Response.Write "</tr>"
For intRec=1 To rs.PageSize
If Not rs.EOF Then
Response.Write "<tr><td width=120 height=27>"
Response.Write "<div align=Left><font size=-2><b>" & RS("OS") & "</b></font></div></td>"
Response.Write "<td width=60 height=27>"
Response.Write "<div align=Left><font size=-2><b>" & RS("PCid") & "</b></font></div></td>"
Response.Write "<td width=120 height=27>"
Response.Write "<div align=Left><font size=-2><b>" & RS("PartId") & "</b></font></div></td>"
Response.Write "<td width=120 height=27>"
Response.Write "<div align=center><font size=-2><b>" & RS("Browser1") & "</b></font></div></td>"
Response.Write "<td width=120 height=27>"
Response.Write "<div align=center><font size=-2><b>" & RS("Browser2") & "</b></font></div></td>"
Response.Write "<td width=120 height=27>"
Response.Write "<div align=center><font size=-2><b>" & RS("Browser3") & "</b></font></div></td>"
Response.Write "</tr>"
RS.MoveNext
end if
next
'Now showing first, next, back, last buttons.
Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=1"">First Page</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 & """>Previous Page</a>"
End If
Response.Write " | "
If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage + 1 & """>Next Page</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 & """>Last Page</a>"
Response.Write "</div>" & vbcrlf
Else
Response.Write "No records found!"
End If
rs.Close
Set rs = Nothing
Response.Write "<input type=button name=butBack value='Back To Previous' onClick=MM_checkBrowser(4.0,1,2,4.0,1,2,2,'search_os.asp','search_os.asp');return document.MM_returnValue>"
%>
</body>
</html>
|
Holly | Posted 7:01am 31. August 2001 Server Time |
There are some restrictions on the use of the AbsolutePage property, depending on your data provider. I'm not an Access expert, but I know that you can't use it when your resultset is opened with the adOpenForwardOnly cursor type. You should look at whatever Help or docmentation you have on Access and see under what circumstances you can or cannot use AbsolutePage.
hotwu | Posted 3:07am 28. February 2002 Server Time |
lol, ok, i know this post was from august of 2001, but just to clear things up, your sql statement should be:
SQL = "SELECT * FROM Partitions WHERE OS='" & osys & "' Order By PartID"
Blackrayn | Posted 0:16am 12. April 2002 Server Time |
Const adUseClient = 3
Why did you set this to 3?
mp3cdman | Posted 3:45am 12. April 2002 Server Time |
Hey hotwu you must be really getting bored if your solving problems from this far back....lol
mp3cdman
Lakshmikanth | Posted 2:21am 29. July 2010 Server Time |
Hi.
This below SQL works fine if # of records are less than 3000. If # of records exceeds 3000 i get "internal server error". Is there a limit for RecordSet Object ? If so, what is the alternative. I need to displaya all records on web page by reading Access table.
' Below code works fine all other conditions.
strSQL1 = "SELECT ccode, cname, castinfo_additionalinfo, FROM CInfo;"
FilePath = Server.MapPath("ad\cd.mdb")
connectstr = "Provider=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & FilePath
Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
rstDBEdit.Open strSQL1, connectstr, adOpenKeyset, adLockOptimistic, adCmdText
Reply to Post Paging with recordsets/sql
|
|
|
