|
 |
access_asp thread: Paging Recordsets
Message #1 by "Dale James Wright" <dwright@c...> on Fri, 17 Jan 2003 11:03:08
|
|
hi i have a very annoying problem @ the minute.... I have created a ASP
page that allows users to either user a text free search or select a
record from a list box populated by a database... Now the problem is as
follows...
There is a load of records to return and the database will get bigger and
bigger as time goes on.... So i want to use paging... I have a script that
works fine when using no search features.... But i cant for the life of me
get this to work using TWO search features....It just wont return any
records... I am fairly newish beginner to asp. So this might be the
problem....I may have missed something.... Is anyone out there who could
help me with this.....
The code is to long to show, but i can email you the page if you drop me a
line....
Thanks in advance
Regards
Dale
Message #2 by "sashidhar" <sashi@a...> on Sat, 18 Jan 2003 05:57:50
|
|
here is a sample programe page.asp
it will connect with a table 'login' which has field 'uid'
per page it will show 2 records
Hope it will solve ur problem
<%
dim rs,i,strConn,strTemp,con
set con=server.CreateObject("adodb.connection")
set rs=server.CreateObject("adodb.recordset")
on error resume next
'create dsn as hello
con.Open "hello"
rs.CursorLocation=3 'clientside
rs.CursorType=3 'staticrecordset
rs.PageSize=2
rs.Open "select * from login",con
Response.Write "<table><tr>"
for i=1 to rs.PageCount
Response.Write "<td><a href=page.asp?pg=" & i & ">" & i & "</a></td>"
next
Response.Write "</tr></table><br>"
if Request.QueryString("pg")="" then
rs.AbsolutePage=1
For i=1 to 2
Response.Write rs.Fields("uid") & "<br>"
rs.MoveNext
Next
else
rs.AbsolutePage=cint(Request.QueryString("pg"))
For i=1 to 2
Response.Write rs.Fields("uid") & "<br>"
rs.MoveNext
Next
end if
%>
Message #3 by "Dale James Wright" <dwright@c...> on Mon, 20 Jan 2003 13:09:27
|
|
Thanks for the reply, but i know how to do that...Creating paging isnt the
problem... The problem is as follows:
Kind regards
Dale
I know its something pretty easy, but i cant figure it out....
The user submits a free text search into the search box.. The results are
returned, and the records are displayed... If the results return 10
results from the seach keyword TAX...then the database shows 5 records
with the next five being activated by clicking on the next button...
This is not happening though... The user is searching for the word, the
results are returned, 5 records and 5 waiting to be accessed. You click on
the next five results and it shows ALL OF THE RESULTS... IE 750 records
instead of the other 5...
PLEASE PLEASE PLEASE HELP ME!!!! I am pulling my hair out...
regards Dale
<%@ Language=VBScript %>
<% Response.Buffer = True %>
<!--#INCLUDE FILE="ADOVBS.INC"-->
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table>
<td valign="top">
<form name="txtsearch" method="post" action="pagingexample.asp">
<table width="600" border="0" cellspacing="1" cellpadding="1"
align="center">
<tr>
<td width="270" height="24" valign="top">Enter your Free
Text
Search in the text box and click on the Show Text
Results:</td>
<td width="153" height="24">
<input type="Text" name="Keywords" style="font-family:
Verdana,Arial,Helvetica,sans-serif; font-size:12px; background-color: rgb
(240,240,240); color: rgb(0,0,0)">
</td>
<td width="167" height="24">
<input type="submit" name="submit2" value="Show Text
Results" style="font-family: Verdana,Arial,Helvetica,sans-serif; font-
size:12px; background-color: rgb(100,100,100); color: rgb(255,255,255);
border: 1px solid rgb(255,255,255)">
</td>
</tr>
</table>
</form>
</td>
</tr>
</table>
<%
Dim objRS, oconn, vardepartment, tempsearch, sqltxt
Function ChkString(string)
If string = "" Then String = " "
ChkString = Replace(String, "'", "''")
End Function
Tempsearch = CHKString(Request.Form("KeyWords"))
Set oConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.RecordSet")
oconn.Open "DSN=BTContact"
sqltxt = "SELECT * FROM FAQAnswer WHERE FAQ LIKE '%" &
Tempsearch & "%' ORDER BY DeptID"
objRS.Open sqltxt, oConn, adOpenStatic, adLockReadOnly
objRS.PageSize = 5
intPageCount = objRS.PageCount
Select Case Request("action")
Case "<<"
intPage = 1
Case "<"
intPage = CInt(Request("intPage"))-1
If intPage < 1 Then intPage = 1
Case ">"
intPage = CInt(Request("intPage"))+1
If intPage > intPageCount Then intPage =
intPageCount
Case ">>"
intPage = intPageCount
Case Else
intPage = 1
End Select
%>
<p align="left"><font face="MS Sans Serif" size="2">Below are the results
that
have been found:</font></p>
<%
objRS.AbsolutePage = intPage
For intRecord = 1 To objRS.PageSize
Response.Write "<table width=600 border=0
cellspacing=0 cellpadding=1 align=center bgcolor=#666666>"
Response.Write "<tr bordercolor=#FFFFFF>"
Response.Write "<td width=77 valign=top
bgcolor=#FFFFCC><b>Question:</b></td>"
Response.Write "<td width=416 valign=top
bgcolor=#DDDDDD>" & objrs("FAQ") & "</td>"
Response.Write "</tr>"
Response.Write "<tr bordercolor=#FFFFFF>"
Response.Write "<td width=77 valign=top
bgcolor=#FFFFCC><b>Answer:</b></td>"
Response.Write "<td width=416 valign=top
bgcolor=#CCCCFF>" & objrs("Solution") & "<br>"
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "</table>"
objRS.MoveNext
If objRS.EOF Then Exit For
Next
objRS.Close
Set objRS = Nothing
oConn.Close
Set oConn = Nothing
%>
<FORM NAME="MovePage" ACTION="pagingexample.asp" METHOD="POST">
<table width="600" border="0" cellspacing="1" cellpadding="1"
align="center">
<tr>
<td>
<div align="center">
<input type="hidden" name="intPage" value="<%=intPage%>">
<input type="submit" name="action" value="<<">
<input type="submit" name="action" value="<">
<input type="submit" name="action" value=">">
<input type="submit" name="action" value=">>">
Page: <%=intPage & " of " & intPageCount%> </div>
</td>
</tr>
</table>
</FORM>
</body>
</html>
Message #4 by "Paulo Fernandes" <paulofernandes@c...> on Mon, 20 Jan 2003 15:01:36 -0000
|
|
Hi,
I didn't hard code this, but it's working fine (watch for wrapping).
HTH
PauloF
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D begining of code
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D
<!--VB ADO Constants file. Needed for the ad... constants we use-->
<!-- #include file=3D"includes/adovbs.inc" -->
<%
response.expires =3D -1
' BEGIN USER CONSTANTS
Dim CONN_STRING
Dim CONN_USER
Dim CONN_PASS
' To use a DSN, the format is shown on the next line:
CONN_STRING =3D "DSN=3Dmydsn;"
' BEGIN RUNTIME CODE
' Declare our vars
Dim iPageSize 'How big our pages are
Dim iPageCount 'The number of pages we get back
Dim iPageCurrent 'The page we want to show
Dim strOrderBy 'A fake parameter used to illustrate passing them
Dim newstrSQL 'SQL command to execute
Dim objPagingConn 'The ADODB connection object
Dim objPagingRS 'The ADODB recordset object
Dim iRecordsShown 'Loop controller for displaying just iPageSize
records
Dim I 'Standard looping var
' Get parameters
iPageSize =3D 5
' Retrieve page to show or default to 1
If Request.QueryString("page") =3D "" Then
iPageCurrent =3D 1
Else
iPageCurrent =3D CInt(Request.QueryString("page"))
End If
If Request.QueryString("order") =3D "" Then
strOrderBy =3D "codigo"
Else
strOrderBy =3D Request.QueryString("order")
End If
strSQL =3D request.querystring("strSQL")
Set objPagingConn =3D Server.CreateObject("ADODB.Connection")
objPagingConn.Open CONN_STRING, CONN_USER, CONN_PASS
' Create recordset and set the page size
Set objPagingRS =3D Server.CreateObject("ADODB.Recordset")
objPagingRS.PageSize =3D iPageSize
' You can change other settings as with any RS
'objPagingRS.CursorLocation =3D adUseClient
objPagingRS.CacheSize =3D iPageSize
' Open RS
objPagingRS.Open strSQL, objPagingConn, adOpenStatic, adLockReadOnly,
adCmdText
' Get the count of the pages using the given page size
iPageCount =3D objPagingRS.PageCount
' If the request page falls outside the acceptable range,
' give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent =3D iPageCount
If iPageCurrent < 1 Then iPageCurrent =3D 1
' Check page count to prevent bombing when zero results are returned!
If iPageCount =3D 0 Then
Response.Write "<br>N=C3=A3o foram encontrados registos!<br>"
%>
<head>
<title></title>
</head>
<body link=3D"#49B78D" vlink=3D"#CCFFCC" alink=3D"#49B78D"
BGCOLOR=3D"#FFFFFF">
<P ALIGN=3D"center">
<input type=3D"button" name=3D"btn_pesquisar" value=3D"Efectuar nova
pesquisa !" style=3D"font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 7pt; font-weight: bold" onclick=3D"javascript:
history.back()"><br>
</P>
<%
Else
' Move to the selected page
objPagingRS.AbsolutePage =3D iPageCurrent
' Start output with a page x of n line
%>
<script language=3D"JavaScript">
function addfavorite(){
window.external.AddFavorite(location.href, document.title);
}
function startpopup(desktopURL) {
var desktop =3D
window.open(desktopURL,
"_blank","scrollbars=3Dno,resizable=3Dno,width=3D400,height=3D400,top=3D1
0,left=3D10");
}
function startpopup1(desktopURL) {
var desktop =3D
window.open(desktopURL,
"detalhe","scrollbars=3Dyes,resizable=3Dno,width=3D400,height=3D400,top=3D
10,left=3D10");
}
</script>
<table border=3D"0" width=3D"550" bordercolor=3D"#CC0000"
STYLE=3D"border-collapse: collapse" CELLPADDING=3D"0" CELLSPACING=3D"0">
<tr>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><b>
<font face=3D"Arial" size=3D"1"
color=3D"#49B78D">Refer=C3=AAncia</font></b></td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><b>
<font face=3D"Arial" size=3D"1"
color=3D"#49B78D">Finalidade</font></b></td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><b>
<font face=3D"Arial" size=3D"1"
color=3D"#49B78D">Tipo</font></b></td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><b>
<font face=3D"Arial" size=3D"1"
color=3D"#49B78D">Zona</font></b></td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><b>
<font face=3D"Arial" size=3D"1"
color=3D"#49B78D">Tipologia</font></b></td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><b>
<font face=3D"Arial" size=3D"1"
color=3D"#49B78D">Pre=C3=A7o</font></b></td>
</tr>
<%
iRecordsShown =3D 0
Do While iRecordsShown < iPageSize And Not objPagingRS.EOF
%>
<tr>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><font
color=3D"#CC0000" face=3D"Verdana, Arial, Helvetica, sans-serif"
size=3D"1">
<a
href=3D"javascript:startpopup1('detalhe_imovel.asp?ID=3D<%=3DobjPagingRS.
Fields("idx_imovel")%>')"><%=3DobjPagingRS.Fields("idx_imovel")%><font
color=3D"#49B78D">
</font>
</td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><font
color=3D"#CC0000" face=3D"Verdana, Arial, Helvetica, sans-serif"
size=3D"1">
<a
href=3D"javascript:startpopup1('detalhe_imovel.asp?ID=3D<%=3DobjPagingRS.
Fields("idx_imovel")%>')"><%=3DobjPagingRS.Fields("finalidade")%><font
color=3D"#49B78D">
</font>
</td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><font
color=3D"#CC0000" face=3D"Verdana, Arial, Helvetica, sans-serif"
size=3D"1">
<a
href=3D"javascript:startpopup1('detalhe_imovel.asp?ID=3D<%=3DobjPagingRS.
Fields("idx_imovel")%>')"><%=3DobjPagingRS.Fields("tipo")%><font
color=3D"#49B78D">
</font>
</td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><font
color=3D"#CC0000" face=3D"Verdana, Arial, Helvetica, sans-serif"
size=3D"1">
<a
href=3D"javascript:startpopup1('detalhe_imovel.asp?ID=3D<%=3DobjPagingRS.
Fields("idx_imovel")%>')"><%=3DobjPagingRS.Fields("zona")%><font
color=3D"#49B78D">
</font>
</td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><font
color=3D"#CC0000" face=3D"Verdana, Arial, Helvetica, sans-serif"
size=3D"1">
<a
href=3D"javascript:startpopup1('detalhe_imovel.asp?ID=3D<%=3DobjPagingRS.
Fields("idx_imovel")%>')"><%=3DobjPagingRS.Fields("tipologia")%><font
color=3D"#49B78D">
</font>
</td>
<td width=3D"100" align=3D"center" HEIGHT=3D"25"><font
color=3D"#CC0000" face=3D"Verdana, Arial, Helvetica, sans-serif"
size=3D"1">
<a
href=3D"javascript:startpopup1('detalhe_imovel.asp?ID=3D<%=3DobjPagingRS.
Fields("idx_imovel")%>')"><%=3DobjPagingRS.Fields("preco")%><font
color=3D"#49B78D">
</font><font color=3D"#49B78D">=E2=82=AC </font>
</td>
</tr>
<%
iRecordsShown =3D iRecordsShown + 1
objPagingRS.MoveNext
Loop
%>
<tr>
<td width=3D"550" align=3D"center" HEIGHT=3D"30"
colspan=3D"6">
<%
If iPageCurrent > 1 Then
%>
<a href=3D"resultados_pesquisa.asp?page=3D<%=3D iPageCurrent - 1 %>">
<font color=3D"#49B78D" face=3D"Verdana, Arial, Helvetica, sans-serif"
size=3D"1">[<< Anterior]</FONT></a><font color=3D"#49B78D"
face=3D"Verdana, Arial, Helvetica, sans-serif" size=3D"1">
<%
End If
If iPageCurrent < iPageCount Then
%> </font><font color=3D"#CC0000" face=3D"Verdana, Arial, Helvetica,
sans-serif" size=3D"1">
<a href=3D"resultados_pesquisa.asp?page=3D<%=3D iPageCurrent + 1 %>">
<font color=3D"#49B78D" face=3D"Verdana, Arial, Helvetica, sans-serif"
size=3D"1">[Seguinte >>]</font></a></font><font color=3D"#49B78D"
face=3D"Verdana, Arial, Helvetica, sans-serif" size=3D"1">
<%
End If
%>
</td>
</tr>
<tr>
<td width=3D"550" align=3D"center" colspan=3D"6">
<input type=3D"button" name=3D"btn_pesquisar" value=3D"Efectuar nova
pesquisa !" style=3D"font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 7pt; font-weight: bold" onclick=3D"javascript:
history.back()">
</td>
</tr>
</table>
<p align=3D"right">
<font face=3D"Verdana, Arial, Helvetica, sans-serif" size=3D"1"
color=3D"#49B78D"><b>P=C3=A1gina <%=3D iPageCurrent %> de <%=3D
iPageCount %></b></font>
</p>
</font>
<%
End If
' Close DB objects and free variables
objPagingRS.Close
Set objPagingRS =3D Nothing
objPagingConn.Close
Set objPagingConn =3D Nothing
%>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3Dend of code
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D
-----Original Message-----
From: Dale James Wright [mailto:dwright@c...]
Sent: segunda-feira, 20 de Janeiro de 2003 13:09
To: Access ASP
Subject: [access_asp] Re: Paging Recordsets
Thanks for the reply, but i know how to do that...Creating paging isnt
the
problem... The problem is as follows:
Kind regards
Dale
I know its something pretty easy, but i cant figure it out....
The user submits a free text search into the search box.. The results
are
returned, and the records are displayed... If the results return 10
results from the seach keyword TAX...then the database shows 5 records
with the next five being activated by clicking on the next button...
This is not happening though... The user is searching for the word, the
results are returned, 5 records and 5 waiting to be accessed. You click
on
the next five results and it shows ALL OF THE RESULTS... IE 750 records
instead of the other 5...
PLEASE PLEASE PLEASE HELP ME!!!! I am pulling my hair out...
regards Dale
<%@ Language=3DVBScript %>
<% Response.Buffer =3D True %>
<!--#INCLUDE FILE=3D"ADOVBS.INC"-->
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=3D"Content-Type" content=3D"text/html;
charset=3Diso-8859-1">
</head>
<body bgcolor=3D"#FFFFFF" text=3D"#000000">
<table>
<td valign=3D"top">
<form name=3D"txtsearch" method=3D"post"
action=3D"pagingexample.asp">
<table width=3D"600" border=3D"0" cellspacing=3D"1"
cellpadding=3D"1"
align=3D"center">
<tr>
<td width=3D"270" height=3D"24" valign=3D"top">Enter
your Free
Text
Search in the text box and click on the Show Text
Results:</td>
<td width=3D"153" height=3D"24">
<input type=3D"Text" name=3D"Keywords"
style=3D"font-family:
Verdana,Arial,Helvetica,sans-serif; font-size:12px; background-color:
rgb
(240,240,240); color: rgb(0,0,0)">
</td>
<td width=3D"167" height=3D"24">
<input type=3D"submit" name=3D"submit2" value=3D"Show
Text
Results" style=3D"font-family: Verdana,Arial,Helvetica,sans-serif; font-
size:12px; background-color: rgb(100,100,100); color: rgb(255,255,255);
border: 1px solid rgb(255,255,255)">
</td>
</tr>
</table>
</form>
</td>
</tr>
</table>
<%
Dim objRS, oconn, vardepartment, tempsearch, sqltxt
Function ChkString(string)
If string =3D "" Then String =3D " "
ChkString =3D Replace(String, "'", "''")
End Function
Tempsearch =3D
CHKString(Request.Form("KeyWords"))
Set oConn =3D
Server.CreateObject("ADODB.Connection")
Set objRS =3D Server.CreateObject("ADODB.RecordSet")
oconn.Open "DSN=3DBTContact"
sqltxt =3D "SELECT * FROM FAQAnswer WHERE FAQ LIKE
'%" &
Tempsearch & "%' ORDER BY DeptID"
objRS.Open sqltxt, oConn, adOpenStatic,
adLockReadOnly
objRS.PageSize =3D 5
intPageCount =3D objRS.PageCount
Select Case Request("action")
Case "<<"
intPage =3D 1
Case "<"
intPage =3D CInt(Request("intPage"))-1
If intPage < 1 Then intPage =3D 1
Case ">"
intPage =3D CInt(Request("intPage"))+1
If intPage > intPageCount Then intPage =3D
intPageCount
Case ">>"
intPage =3D intPageCount
Case Else
intPage =3D 1
End Select
%>
<p align=3D"left"><font face=3D"MS Sans Serif" size=3D"2">Below are the
results
that
have been found:</font></p>
<%
objRS.AbsolutePage =3D intPage
For intRecord =3D 1 To objRS.PageSize
Response.Write "<table width=3D600 border=3D0
cellspacing=3D0 cellpadding=3D1 align=3Dcenter bgcolor=3D#666666>"
Response.Write "<tr bordercolor=3D#FFFFFF>"
Response.Write "<td width=3D77 valign=3Dtop
bgcolor=3D#FFFFCC><b>Question:</b></td>"
Response.Write "<td width=3D416 valign=3Dtop
bgcolor=3D#DDDDDD>" & objrs("FAQ") & "</td>"
Response.Write "</tr>"
Response.Write "<tr bordercolor=3D#FFFFFF>"
Response.Write "<td width=3D77 valign=3Dtop
bgcolor=3D#FFFFCC><b>Answer:</b></td>"
Response.Write "<td width=3D416 valign=3Dtop
bgcolor=3D#CCCCFF>" & objrs("Solution") & "<br>"
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "</table>"
objRS.MoveNext
If objRS.EOF Then Exit For
Next
objRS.Close
Set objRS =3D Nothing
oConn.Close
Set oConn =3D Nothing
%>
<FORM NAME=3D"MovePage" ACTION=3D"pagingexample.asp" METHOD=3D"POST">
<table width=3D"600" border=3D"0" cellspacing=3D"1" cellpadding=3D"1"
align=3D"center">
<tr>
<td>
<div align=3D"center">
<input type=3D"hidden" name=3D"intPage"
value=3D"<%=3DintPage%>">
<input type=3D"submit" name=3D"action" value=3D"<<">
<input type=3D"submit" name=3D"action" value=3D"<">
<input type=3D"submit" name=3D"action" value=3D">">
<input type=3D"submit" name=3D"action" value=3D">>">
Page: <%=3DintPage & " of " & intPageCount%> </div>
</td>
</tr>
</table>
</FORM>
</body>
</html>
Message #5 by "Ken Schaefer" <ken@a...> on Tue, 21 Jan 2003 12:46:21 +1100
|
|
You need to pass the search criteria from page to page, so that the
recordset can be reconstructed in each subsequent page as it was on the
first page.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Dale James Wright" <dwright@c...>
Subject: [access_asp] Re: Paging Recordsets
: Thanks for the reply, but i know how to do that...Creating paging isnt the
: problem... The problem is as follows:
:
: Kind regards
:
: Dale
:
: I know its something pretty easy, but i cant figure it out....
:
: The user submits a free text search into the search box.. The results are
: returned, and the records are displayed... If the results return 10
: results from the seach keyword TAX...then the database shows 5 records
: with the next five being activated by clicking on the next button...
:
: This is not happening though... The user is searching for the word, the
: results are returned, 5 records and 5 waiting to be accessed. You click on
: the next five results and it shows ALL OF THE RESULTS... IE 750 records
: instead of the other 5...
:
: PLEASE PLEASE PLEASE HELP ME!!!! I am pulling my hair out...
:
: regards Dale
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #6 by "Dale James Wright" <dwright@c...> on Wed, 22 Jan 2003 16:49:20
|
|
Ken i understand this, but i am having big big problems.... I have changed
the code again... This time i have it working better...If you select the
Department search, it works fine....If you type the free text search, then
it shows the first page, but when you click on the next page link, nothing
is returned..... PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE
PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE
PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE
HEEEEEEEEEEEELLLLLLLLLLLLLLLLLLPPPPPPPPPPPPPPPPPPPPPPPP!!!!!!!!!!
<%
On Error Resume Next
Dim objConn
Dim objRst
Dim StrSearch
Dim Sql
Dim intPageCount
Dim intRecordCount
Dim intPage
Dim intRecord
Dim intStart
Dim intFinish
Dim PageNumbers
If Request.Form("txtYear") = "" Then
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
Set objConn = Server.CreateObject
("ADODB.Connection")
objConn.Open "DSN=BTContact"
If Request.Form("department") <> "" Then
StrSearch = Request.Form("department")
Else
StrSearch = Request.QueryString("FIND")
End If
sql = "SELECT * FROM FAQAnswer WHERE
Deptid = " & strSearch & ""
Call FindRecords()
Else
if Request.Form("txtYear") > 0 And Request.Form("Department") > 0
Then
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
Set objConn = Server.CreateObject
("ADODB.Connection")
objConn.Open "DSN=BTContact"
If Request.Form("txtYear") <> "" Then
StrSearch = Request.Form("txtYear")
Else
StrSearch = Request.QueryString("FIND")
End If
sql = "SELECT * FROM FAQAnswer WHERE FAQ
LIKE '%" & StrSearch & "%' ORDER BY DeptID"
Call FindRecords()
End if
End if
Function FindRecords()
Set objRst = Server.CreateObject
("ADODB.Recordset")
objRst.CursorLocation = 3
'adUseClient
objRst.CursorType = 3
'adOpenStatic
objRst.ActiveConnection = objConn
objRst.Open Sql
objRst.PageSize = 5
objRst.CacheSize = objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount =
objRst.RecordCount
If CInt(intPage) > CInt
(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt
(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart +
(objRst.PageSize - 1)
End if
End If
Response.Write "<BODY
bgcolor=#FFFFFF>"
Response.Write "<table width=410
border=0 cellspacing=1 cellpadding=1 align=center>"
Response.Write "<tr>"
Response.Write "<th>Your search
in " & StrSearch & " returned " & intRecordCount & " records.</th>"
Response.Write "</tr><tr>"
If intRecordCount > 0 Then
Response.Write "<td>You are now
viewing records " & intStart & " through " & intFinish & " </td>"
Response.Write "</tr>"
Response.Write "</table>"
Response.Write sql
For intRecord = 1 to objRst.PageSize
Response.Write "<table width=600
border=0 cellspacing=0 cellpadding=1 align=center bgcolor=#666666>"
Response.Write "<tr
bordercolor=#FFFFFF>"
Response.Write "<td width=77
valign=top bgcolor=#FFFFCC><b>Question:</b></td>"
Response.Write "<td width=416
valign=top bgcolor=#DDDDDD>" & objrst("FAQ") & "</td>"
Response.Write "</tr>"
Response.Write "<tr
bordercolor=#FFFFFF>"
Response.Write "<td width=77
valign=top bgcolor=#FFFFCC><b>Answer:</b></td>"
Response.Write "<td width=416
valign=top bgcolor=#CCCCFF>" & objrst("Solution") & "<br>"
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "</table>"
objRst.MoveNext
If objRst.EOF Then Exit for
Next
Response.Write "<table width=408
border=0 cellspacing=1 cellpadding=1 align=center>"
Response.Write "<tr>"
If cInt(intPage) > 1 Then
Response.Write "<td><a
href=bk_searchresult.asp?NAV=" & intPage - 1&"&FIND=" & StrSearch & "><<
Prev</a></td>"
End IF
If cInt(intPage) < cInt(intPageCount) Then
Response.Write "<td><a
href=bk_searchresult.asp?NAV=" & intPage + 1&"&FIND=" & StrSearch & ">Next
>></a></td>"
Response.Write "</tr>"
End If
End If
Response.Write "</table>"
Response.Write "<table width=300
border=0 cellspacing=1 cellpadding=1 align=center>"
Response.Write "<tr>"
Response.Write "<td><a
href=mainsearch.asp>Try a new search</a></td>"
Response.Write "</tr>"
End Function
objrst.Close
set objrst = Nothing
%>
Message #7 by "Ken Schaefer" <ken@a...> on Thu, 23 Jan 2003 13:41:40 +1100
|
|
Hi,
a) Doing Response.Write(strSQL) would probably show you what is going wrong.
b) Your code looks like this:
<%
If Request.Form("txtYear") = "" Then
sql = "SELECT * FROM FAQAnswer WHERE
Deptid = " & strSearch & ""
End If
If Request.Form("txtYear") <> "" Then
"SELECT * FROM FAQAnswer WHERE FAQ
LIKE '%" & StrSearch & "%' ORDER BY DeptID"
End If
%>
Since you are using a GET on each subsequent page request,
Request.Form("txtYear") is always equal to "", and you are always falling
into the first search conditional. That's what I think is going wrong. What
I suggest you do is put some Response.Write() statements inside your
conditionals, so you can see where the code is falling to...
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Dale James Wright" <dwright@c...>
Subject: [access_asp] Re: Paging Recordsets
: Ken i understand this, but i am having big big problems.... I have changed
: the code again... This time i have it working better...If you select the
: Department search, it works fine....If you type the free text search, then
: it shows the first page, but when you click on the next page link, nothing
: is returned..... PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE
: PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE
: PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE
: HEEEEEEEEEEEELLLLLLLLLLLLLLLLLLPPPPPPPPPPPPPPPPPPPPPPPP!!!!!!!!!!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #8 by "Dale James Wright" <dwright@c...> on Mon, 27 Jan 2003 14:23:21
|
|
Now solved....
I needed to pass extra criteria in the form pages to get this to work...
Regards
Dale
|
|
|
View Cart
