calliepeck
03-27-2006, 03:35 PM
I have a script which is meant to display the records of my database in a paged recordset AND include the ability to search on the same page. Everything is working very well except that I cannot get my pagination to work with my filters. It displays the search but when you click on a page, it reverts to displaying the unfiltered records. I suppose I could get everything going with a crapload of conditionals, but I feel like there should be a better way to do it. My code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/news.asp" -->
<%
Dim script_name
script_name= Request.ServerVariables("SCRIPT_NAME")
%>
<% 'Option Explicit %>
<%
'Sub ShowRec(qc1, qc2, qc3)
'response.Write("<p class='bold' style='margin-bottom:0px;'>"&qc1&"</p>")
'response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&qc2&"</p>")
'response.Write("<span style='font-weight:bold;font-size:12px;'>"&qc3&": </span>")
'End Sub
' Putting the values of ADO constants
Const adCmdTableDirect = &H0200
Const adLockReadOnly = 1
Const adOpenStatic = 1
%>
<div style="width:180px;margin-left:10px;">
<form action="<%=script_name%>" method="post">
<div style="float:left;font-size:11px;padding-top:6px;width:50px;">By Date:</div>
<div>
input name="mo" type="text" value="" maxlength="2" class="searchhearings" onFocus="this.value=''">
<input name="day" type="text" value="" maxlength="2" class="searchhearings" style="width:20px;" onFocus="this.value=''">
<input name="year" type="text" value="" maxlength="4" class="searchhearings" style="width:30px;" onFocus="this.value=''">
</div>
</div>
<div style="width:180px;margin-left:10px;margin-top:2px;">
<div style="float:left;font-size:11px;padding-top:6px;width:50px;">Or Title:</div>
<div>
<input name="title" type="text" class="searchhearings" style="width:120px;">
</div>
</div>
<div style="width:180px;margin-left:10px;margin-top:2px;">
<div style="float:left;font-size:11px;padding-top:6px;width:50px;">Or:</div>
<div>
<select name="committee" style="width:124px;font-size:10px;">
<option value="">Search Committee</option>
<option value="">-------------------------</option>
<option value="Full Committee">Full Committee</option>
<option value="Subcommittee on Africa, Global Human Rights, and International Operations">SC - Africa</option>
<option value="Subcommittee on Asia and the Pacific">SC - Asia</option>
<option value="Subcommittee on the Western Hemisphere">SC - West Hem.</option>
<option value="Subcommitee on Europe and Emerging Threats">SC - Europe</option>
<option value="Subcommittee on the Middle East and Central Asia">SC - Middle East</option>
<option value="Subcommittee on Oversight and Investigations">SC - Oversight</option>
<option value="Subcommittee on International Terrorism and Nonproliferation">SC - Terrorism</option>
</select>
</div>
</div>
<div style="text-align:right;margin-right:10px;margin-top:7px;margin-bottom:0px;">
<input type="image" name="submit_hearing" value="submit" src="buttons/search_hearing.gif"></form>
</div>
</div>
</div>
<div style="margin-top:10px;margin-left:20px;line-height:1.2;width:300px;">
<%
hdate=request.Form("mo")&"/"&request.Form("day")&"/"&request.Form("year")&""
htitle=request.Form("title")
hcommittee=request.Form("committee")
Dim Updater
Set Updater = Server.CreateObject("ADODB.Recordset")
Updater.CursorLocation=3 'clientside
Updater.CursorType=3 'staticrecordset
Updater.PageSize=4
Updater.Open "hearings", MM_news_STRING, adOpenStatic, adLockReadOnly, adCmdTableDirect
If Not Updater.EOF Then
Updater.Sort = "h_date DESC"
'by Date
if (len(hdate)>3) then
response.Write("Results for hearings held on "& hdate)
Updater.Filter = "h_date='"& hdate &"'"
'by Date & Title
if (len(htitle)>0) then
response.Write(" and title like '"& htitle &"'")
Updater.Filter = "h_date='"& hdate &"' AND h_title LIKE '%"& Replace(htitle, "'", "''") &"%'"
'by Date & Title & Committee
if (len(hcommittee)>0) then
response.write(" hosted by the "& hcommittee)
Updater.Filter = "h_committee='"& hcommittee &"' AND h_date='"& hdate &"' AND h_title LIKE '%"& Replace(htitle, "'", "''") &"%'"
end if
end if
'by Date & Committee
if (len(hcommittee)>0) then
response.Write(" hosted by the '"& hcommittee &"'")
Updater.Filter = "h_date='"& hdate &"' AND h_committee = '"& hcommittee &"'"
end if
'by Title
else if (len(htitle)>0) then
response.Write("Results for hearings with title like '"& Replace(htitle, "'", "''") &"'")
Updater.Filter = "h_title LIKE '%"& htitle &"%'"
'by Title & Committee
if (len(hcommittee)>0) then
response.write(" hosted by the "& hcommittee)
Updater.Filter = "h_committee='"& hcommittee &"' AND h_title LIKE '%"& Replace(htitle, "'", "''") &"%'"
end if
'by Committee
else if (len(hcommittee)>0) then
response.Write("Results for "& hcommittee &" hearings")
Updater.Filter = "h_committee='"& hcommittee &"' OR h_committee2='"& hcommittee &"'"
'Normal
else
Response.Write "<div>PAGE:"
for i=1 to Updater.PageCount
Response.Write " <a href="& script_name &"?pg=" & i & ">" & i & "</a> "
next
response.Write("</div><p>")
response.Write("Results for all hearings sorted by date")
end if
end if
end if
If (not Updater.EOF) then
if Request.QueryString("pg")="" then
Updater.AbsolutePage=1
If (Updater.AbsolutePage=Updater.Pagecount) then
lastrecord=Updater.recordcount
remainder=lastrecord Mod Updater.pagesize
For i=1 to remainder
response.Write("<p class='bold' style='margin-bottom:0px;'>"&Updater("h_date")&"</p>")
response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&Updater("h_title")&"</p>")
response.Write("<span style='font-weight:bold;font-size:12px;'>"&Updater("h_type")&": </span>")
Next
Else
For i=1 to Updater.pagesize
response.Write("<p class='bold' style='margin-bottom:0px;'>"&Updater("h_date")&"</p>")
response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&Updater("h_title")&"</p>")
response.Write("<span style='font-weight:bold;font-size:12px;'>"&Updater("h_type")&": </span>")
Updater.MoveNext
Next
end if
else
Updater.AbsolutePage=cint(Request.QueryString("pg"))
If (Updater.AbsolutePage=Updater.Pagecount) then
lastrecord=Updater.recordcount
remainder=lastrecord Mod Updater.pagesize
For i=1 to remainder
response.Write("<p class='bold' style='margin-bottom:0px;'>"&Updater("h_date")&"</p>")
response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&Updater("h_title")&"</p>")
response.Write("<span style='font-weight:bold;font-size:12px;'>"&Updater("h_type")&": </span>")
Next
Else
For i=1 to Updater.pagesize
response.Write("<p class='bold' style='margin-bottom:0px;'>"&Updater("h_date")&"</p>")
response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&Updater("h_title")&"</p>")
response.Write("<span style='font-weight:bold;font-size:12px;'>"&Updater("h_type")&": </span>")
Updater.MoveNext
Next
end if
end if
Else
Response.Write "No records Found!"
End If
End If
Updater.Close
Set Updater = Nothing
%>
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/news.asp" -->
<%
Dim script_name
script_name= Request.ServerVariables("SCRIPT_NAME")
%>
<% 'Option Explicit %>
<%
'Sub ShowRec(qc1, qc2, qc3)
'response.Write("<p class='bold' style='margin-bottom:0px;'>"&qc1&"</p>")
'response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&qc2&"</p>")
'response.Write("<span style='font-weight:bold;font-size:12px;'>"&qc3&": </span>")
'End Sub
' Putting the values of ADO constants
Const adCmdTableDirect = &H0200
Const adLockReadOnly = 1
Const adOpenStatic = 1
%>
<div style="width:180px;margin-left:10px;">
<form action="<%=script_name%>" method="post">
<div style="float:left;font-size:11px;padding-top:6px;width:50px;">By Date:</div>
<div>
input name="mo" type="text" value="" maxlength="2" class="searchhearings" onFocus="this.value=''">
<input name="day" type="text" value="" maxlength="2" class="searchhearings" style="width:20px;" onFocus="this.value=''">
<input name="year" type="text" value="" maxlength="4" class="searchhearings" style="width:30px;" onFocus="this.value=''">
</div>
</div>
<div style="width:180px;margin-left:10px;margin-top:2px;">
<div style="float:left;font-size:11px;padding-top:6px;width:50px;">Or Title:</div>
<div>
<input name="title" type="text" class="searchhearings" style="width:120px;">
</div>
</div>
<div style="width:180px;margin-left:10px;margin-top:2px;">
<div style="float:left;font-size:11px;padding-top:6px;width:50px;">Or:</div>
<div>
<select name="committee" style="width:124px;font-size:10px;">
<option value="">Search Committee</option>
<option value="">-------------------------</option>
<option value="Full Committee">Full Committee</option>
<option value="Subcommittee on Africa, Global Human Rights, and International Operations">SC - Africa</option>
<option value="Subcommittee on Asia and the Pacific">SC - Asia</option>
<option value="Subcommittee on the Western Hemisphere">SC - West Hem.</option>
<option value="Subcommitee on Europe and Emerging Threats">SC - Europe</option>
<option value="Subcommittee on the Middle East and Central Asia">SC - Middle East</option>
<option value="Subcommittee on Oversight and Investigations">SC - Oversight</option>
<option value="Subcommittee on International Terrorism and Nonproliferation">SC - Terrorism</option>
</select>
</div>
</div>
<div style="text-align:right;margin-right:10px;margin-top:7px;margin-bottom:0px;">
<input type="image" name="submit_hearing" value="submit" src="buttons/search_hearing.gif"></form>
</div>
</div>
</div>
<div style="margin-top:10px;margin-left:20px;line-height:1.2;width:300px;">
<%
hdate=request.Form("mo")&"/"&request.Form("day")&"/"&request.Form("year")&""
htitle=request.Form("title")
hcommittee=request.Form("committee")
Dim Updater
Set Updater = Server.CreateObject("ADODB.Recordset")
Updater.CursorLocation=3 'clientside
Updater.CursorType=3 'staticrecordset
Updater.PageSize=4
Updater.Open "hearings", MM_news_STRING, adOpenStatic, adLockReadOnly, adCmdTableDirect
If Not Updater.EOF Then
Updater.Sort = "h_date DESC"
'by Date
if (len(hdate)>3) then
response.Write("Results for hearings held on "& hdate)
Updater.Filter = "h_date='"& hdate &"'"
'by Date & Title
if (len(htitle)>0) then
response.Write(" and title like '"& htitle &"'")
Updater.Filter = "h_date='"& hdate &"' AND h_title LIKE '%"& Replace(htitle, "'", "''") &"%'"
'by Date & Title & Committee
if (len(hcommittee)>0) then
response.write(" hosted by the "& hcommittee)
Updater.Filter = "h_committee='"& hcommittee &"' AND h_date='"& hdate &"' AND h_title LIKE '%"& Replace(htitle, "'", "''") &"%'"
end if
end if
'by Date & Committee
if (len(hcommittee)>0) then
response.Write(" hosted by the '"& hcommittee &"'")
Updater.Filter = "h_date='"& hdate &"' AND h_committee = '"& hcommittee &"'"
end if
'by Title
else if (len(htitle)>0) then
response.Write("Results for hearings with title like '"& Replace(htitle, "'", "''") &"'")
Updater.Filter = "h_title LIKE '%"& htitle &"%'"
'by Title & Committee
if (len(hcommittee)>0) then
response.write(" hosted by the "& hcommittee)
Updater.Filter = "h_committee='"& hcommittee &"' AND h_title LIKE '%"& Replace(htitle, "'", "''") &"%'"
end if
'by Committee
else if (len(hcommittee)>0) then
response.Write("Results for "& hcommittee &" hearings")
Updater.Filter = "h_committee='"& hcommittee &"' OR h_committee2='"& hcommittee &"'"
'Normal
else
Response.Write "<div>PAGE:"
for i=1 to Updater.PageCount
Response.Write " <a href="& script_name &"?pg=" & i & ">" & i & "</a> "
next
response.Write("</div><p>")
response.Write("Results for all hearings sorted by date")
end if
end if
end if
If (not Updater.EOF) then
if Request.QueryString("pg")="" then
Updater.AbsolutePage=1
If (Updater.AbsolutePage=Updater.Pagecount) then
lastrecord=Updater.recordcount
remainder=lastrecord Mod Updater.pagesize
For i=1 to remainder
response.Write("<p class='bold' style='margin-bottom:0px;'>"&Updater("h_date")&"</p>")
response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&Updater("h_title")&"</p>")
response.Write("<span style='font-weight:bold;font-size:12px;'>"&Updater("h_type")&": </span>")
Next
Else
For i=1 to Updater.pagesize
response.Write("<p class='bold' style='margin-bottom:0px;'>"&Updater("h_date")&"</p>")
response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&Updater("h_title")&"</p>")
response.Write("<span style='font-weight:bold;font-size:12px;'>"&Updater("h_type")&": </span>")
Updater.MoveNext
Next
end if
else
Updater.AbsolutePage=cint(Request.QueryString("pg"))
If (Updater.AbsolutePage=Updater.Pagecount) then
lastrecord=Updater.recordcount
remainder=lastrecord Mod Updater.pagesize
For i=1 to remainder
response.Write("<p class='bold' style='margin-bottom:0px;'>"&Updater("h_date")&"</p>")
response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&Updater("h_title")&"</p>")
response.Write("<span style='font-weight:bold;font-size:12px;'>"&Updater("h_type")&": </span>")
Next
Else
For i=1 to Updater.pagesize
response.Write("<p class='bold' style='margin-bottom:0px;'>"&Updater("h_date")&"</p>")
response.Write("<p style='margin-top:0px;margin-bottom:0px;font-size:12px;'>"&Updater("h_title")&"</p>")
response.Write("<span style='font-weight:bold;font-size:12px;'>"&Updater("h_type")&": </span>")
Updater.MoveNext
Next
end if
end if
Else
Response.Write "No records Found!"
End If
End If
Updater.Close
Set Updater = Nothing
%>