Solved

Problem with paging in ASP search results correctly

Posted on 2011-09-06
5
483 Views
Last Modified: 2012-05-12
I have set this ASP script to add paging to a search results page.
It works fine when all fields are left at default. When a specific criteria is chosen, The first page works fine but the second or consequent pages is returning all records.

A user can search by giving a Puja Name, A zone , A Type or a Combination of Zone and Type
If all three are given, then the name dominates. Please help.
<% Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>
<html>
<head>
 <style>
  body { font-family : Verdana; font-size : 8pt; }
  a { font-family : Verdana; font-size : 8pt;
   text-decoration : none; }
 </style>
</head>

<body>

 <form method="POST" action="" name="frmPuja" id="frmPuja">
                      <table width="790" border="0" cellpadding="0" cellspacing="1" bordercolor="#111111" id="AutoNumber3" style="border-collapse: collapse">
                        <tr>
                          <td width="9" rowspan="3">&nbsp;</td>
                          <td width="9" rowspan="2">&nbsp;</td>
                          <td width="84" rowspan="2">
                          <p style="text-align: center">
                          <img src="images/trinayan.jpg" alt="trinayan" width="59" height="33"></td>
                          <td width="292"><span class="style1"> Search by Puja Name</span></td>
                          <td width="119"><span class="style1">or</span></td>
                          <td width="149">&nbsp;</td>
                          <td width="109">&nbsp;</td>
                          <td width="10">&nbsp;</td>
                        </tr>
                        
                        <tr>
                          <td width="292">&nbsp;<input name="txtPujaName" type="text" id="txtPujaName" size="40"></td>
                          <td width="119"><select name="lstZone" id="lstZone">
                              <option selected value="None">Select Zone</option>
                              <option value="North Kolkata">North Kolkata</option>
                              <option value="South Kolkata">South Kolkata</option>
                              <option value="Central Kolkata">Central Kolkata</option>
                              <option value="East Kolkata">East Kolkata</option>
                              <option value="West Kolkata">West Kolkata</option>
                              <option value="Salt Lake City">Salt Lake City</option>
                              <option value="Germany">Germany</option>
                            </select></td>
                          <td width="149"><select name="lstType" size="1" class="L" id="lstType">
                              <option value="None" selected>Select Puja Type</option>
                              <option value="Baroari">Baroari</option>
                              <option value="Community">Community</option>
                              <option value="Bonedi Bari">Bonedi Bari</option>
                              <option value="Apartment Complex">Apartment Complex</option>
                          </select></td>
                          <td width="109"><input type="hidden" name="hdIsPageSub" value="1"><input name="btnPujaSerch" type="submit" id="btnPujaSerch" value="Search"></td>
                          <td width="10">&nbsp;</td>
                        </tr>
                        <tr>
                          <td width="9"><p></td>
                          <td width="84">&nbsp;</td>
                          <td width="292">&nbsp;</td>
                          <td width="119">&nbsp;</td>
                          <td width="149">&nbsp;</td>
                          <td width="109">&nbsp;</td>
                          <td width="10">&nbsp;</td>
                        </tr>
                      </table>
                  </form>



<%

Dim strPujaName
Dim strPujaZone
Dim strPujaType

 Dim connStr
  connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
   Server.MapPath("access_db/sharodinfo.mdb")

 Dim rs
  Set rs = Server.CreateObject("ADODB.Recordset")
  Dim strSQLStmt
  
  if Request("hdIsPageSub")=1 then
  
  			strPujaName=Request.Form("txtPujaName")
			strPujaZone=Request.Form("lstZone")
			strPujaType=Request.Form("lstType")	
			Response.Write(" <BR> Through FORM " + Request("lstType") + "<br>")	
			
else			
			'strPujaName=Request("txtPujaName")
			'strPujaZone=Request("lstZone")
			'strPujaType=Request("lstType")
			Response.Write(" <BR> Through URL " + Request("lstType") + "<br>")	
			
			'strPujaName=Request.QueryString("txtPujaName")
			'strPujaZone=Request.QueryString("lstZone")
			'strPujaType=Request.QueryString("lstType")
			'Response.Write(" <BR> Through URL <br>")	
			
end if
			
			Response.Write("<BR>" + strPujaName + "<BR>")
			Response.Write("<BR>" + strPujaZone + "<BR>")
			Response.Write("<BR>" + strPujaType + "<BR>")
    			
	
		if strPujaName<>"" then
		strSQLStmt = "Select * from sharod where pujaName like '%" & strPujaName & "%' OR akaName like '%" & strPujaName & "%' order by pujaName"
		end if
		
		if strPujaName="" and strPujaZone<>"None" and strPujaType="None" then
		strSQLStmt = "Select * from sharod where pujaZone = '" & strPujaZone & "' order by sInfoId"
		end if
		
		if strPujaName="" and strPujaZone="None" and strPujaType<>"None" then
		strSQLStmt = "Select * from sharod where pujaType = '" & strPujaType & "' order by sInfoId"	
		end if
		
		if strPujaName="" and strPujaZone<>"None" and strPujaType<>"None" then
		strSQLStmt = "Select * from sharod where pujaZone='" & strPujaZone & "' and pujaType='" & strPujaType & "' order by sInfoId"
		end if
		
		if strPujaName="" and strPujaZone="" and strPujaType="" then
		strSQLStmt = "Select * from sharod order by pujaName"
		end if
		
		'else
		'strSQLStmt = "Select * from sharod order by pujaName"
		'end if	
		
		'strSQLStmt = "Select * from sharod order by pujaName"	

  rs.PageSize = 5
  rs.CacheSize = 5
  rs.CursorLocation = adUseClient

 rs.Open strSQLStmt, 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
  Response.Write "<br><br>" & vbcrlf

  Dim fldF, intRec

  Response.Write "<table border=1 align=center cellpadding=3 "
  Response.Write "cellspacing=0><thead><tr>"
  For Each fldF in rs.Fields
   Response.Write "<td>" & fldF.Name & "</td>"
  Next
  Response.Write "</tr></thead><tbody>"

  For intRec=1 To rs.PageSize
   If Not rs.EOF Then
    Response.Write "<tr>"
    For Each fldF in rs.Fields
     Response.Write "<td>" & fldF.Value & "</td>"
    Next
    Response.Write "<tr>"
    rs.MoveNext
   End If
  Next
  Response.Write "</tbody></table><p>"
  
  
  ' Now showing first, next, back, last buttons.

Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href="""
Response.Write Request.ServerVariables("SCRIPT_NAME")
Response.Write "?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")
	Response.Write "?pagenum=" & abspage - 1 & """><b>Previous Page</b></a>"
End If

Response.Write "	|	"
	If abspage < pagecnt Then
		Response.Write "<a href=""" & _
			Request.ServerVariables("SCRIPT_NAME")
		Response.Write "?pagenum=" & abspage + 1 & """>Next Page</a>"
	Else
		Response.Write "<span style=""color:silver;""" & _
			"><b>Next Page</b></span>"
End If

Response.Write "	|	"
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME")
	Response.Write "?pagenum=" & pagecnt & """><b>Last Page</b></a>"
Response.Write "</div>" & vbcrlf

Else
	Response.Write "No records found!"
End If

rs.Close
Set rs = Nothing

%>
</body>
</html>

Open in new window


Also, the page Number is not refreshing for a new search.  It displays the new records but of the last viewed page number.

The site where it will be incorporated is http://www.sharodinfo.com/sharodSearch.asp 
You could see the problem there.

I have added the MDB file in the following comment.
0
Comment
Question by:braindrops
  • 3
  • 2
5 Comments
 

Author Comment

by:braindrops
ID: 36487580
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36487961
Give an example of a search result, that gives multiple search pages.
This way we can see how it acts.
0
 
LVL 30

Accepted Solution

by:
Wayne Barron earned 250 total points
ID: 36487993
Without going through your code.
This is your issue.

This is your search Query:

http://www.sharodinfo.com/sharodSearch.asp?txtPujaName=PRASANNAMOYEE+GHAT&lstZone=None&lstType=Baroari&hdIsPageSub=1&btnPujaSerch=Search

This is the linked pages URL:

http://www.sharodinfo.com/sharodSearch.asp?searchType=all&searchBy=all&PageIndex=2

You might have to debug this to make it work.

<% Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>
<html>
<head>
 <style>
  body { font-family : Verdana; font-size : 8pt; }
  a { font-family : Verdana; font-size : 8pt;
   text-decoration : none; }
 </style>
</head>

<body>

 <form method="POST" action="" name="frmPuja" id="frmPuja">
                      <table width="790" border="0" cellpadding="0" cellspacing="1" bordercolor="#111111" id="AutoNumber3" style="border-collapse: collapse">
                        <tr>
                          <td width="9" rowspan="3">&nbsp;</td>
                          <td width="9" rowspan="2">&nbsp;</td>
                          <td width="84" rowspan="2">
                          <p style="text-align: center">
                          <img src="images/trinayan.jpg" alt="trinayan" width="59" height="33"></td>
                          <td width="292"><span class="style1"> Search by Puja Name</span></td>
                          <td width="119"><span class="style1">or</span></td>
                          <td width="149">&nbsp;</td>
                          <td width="109">&nbsp;</td>
                          <td width="10">&nbsp;</td>
                        </tr>
                        
                        <tr>
                          <td width="292">&nbsp;<input name="txtPujaName" type="text" id="txtPujaName" size="40"></td>
                          <td width="119"><select name="lstZone" id="lstZone">
                              <option selected value="None">Select Zone</option>
                              <option value="North Kolkata">North Kolkata</option>
                              <option value="South Kolkata">South Kolkata</option>
                              <option value="Central Kolkata">Central Kolkata</option>
                              <option value="East Kolkata">East Kolkata</option>
                              <option value="West Kolkata">West Kolkata</option>
                              <option value="Salt Lake City">Salt Lake City</option>
                              <option value="Germany">Germany</option>
                            </select></td>
                          <td width="149"><select name="lstType" size="1" class="L" id="lstType">
                              <option value="None" selected>Select Puja Type</option>
                              <option value="Baroari">Baroari</option>
                              <option value="Community">Community</option>
                              <option value="Bonedi Bari">Bonedi Bari</option>
                              <option value="Apartment Complex">Apartment Complex</option>
                          </select></td>
                          <td width="109"><input type="hidden" name="hdIsPageSub" value="1"><input name="btnPujaSerch" type="submit" id="btnPujaSerch" value="Search"></td>
                          <td width="10">&nbsp;</td>
                        </tr>
                        <tr>
                          <td width="9"><p></td>
                          <td width="84">&nbsp;</td>
                          <td width="292">&nbsp;</td>
                          <td width="119">&nbsp;</td>
                          <td width="149">&nbsp;</td>
                          <td width="109">&nbsp;</td>
                          <td width="10">&nbsp;</td>
                        </tr>
                      </table>
                  </form>



<%

Dim strPujaName
Dim strPujaZone
Dim strPujaType

 Dim connStr
  connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
   Server.MapPath("access_db/sharodinfo.mdb")

 Dim rs
  Set rs = Server.CreateObject("ADODB.Recordset")
  Dim strSQLStmt
  
  if Request("hdIsPageSub")=1 then
  
  			strPujaName=Request.Form("txtPujaName")
			strPujaZone=Request.Form("lstZone")
			strPujaType=Request.Form("lstType")	
			Response.Write(" <BR> Through FORM " + Request("lstType") + "<br>")	
			
else			
			'strPujaName=Request("txtPujaName")
			'strPujaZone=Request("lstZone")
			'strPujaType=Request("lstType")
			Response.Write(" <BR> Through URL " + Request("lstType") + "<br>")	
			
			'strPujaName=Request.QueryString("txtPujaName")
			'strPujaZone=Request.QueryString("lstZone")
			'strPujaType=Request.QueryString("lstType")
			'Response.Write(" <BR> Through URL <br>")	
			
end if
			
			Response.Write("<BR>" + strPujaName + "<BR>")
			Response.Write("<BR>" + strPujaZone + "<BR>")
			Response.Write("<BR>" + strPujaType + "<BR>")
    			
	
		if strPujaName<>"" then
		strSQLStmt = "Select * from sharod where pujaName like '%" & strPujaName & "%' OR akaName like '%" & strPujaName & "%' order by pujaName"
		end if
		
		if strPujaName="" and strPujaZone<>"None" and strPujaType="None" then
		strSQLStmt = "Select * from sharod where pujaZone = '" & strPujaZone & "' order by sInfoId"
		end if
		
		if strPujaName="" and strPujaZone="None" and strPujaType<>"None" then
		strSQLStmt = "Select * from sharod where pujaType = '" & strPujaType & "' order by sInfoId"	
		end if
		
		if strPujaName="" and strPujaZone<>"None" and strPujaType<>"None" then
		strSQLStmt = "Select * from sharod where pujaZone='" & strPujaZone & "' and pujaType='" & strPujaType & "' order by sInfoId"
		end if
		
		if strPujaName="" and strPujaZone="" and strPujaType="" then
		strSQLStmt = "Select * from sharod order by pujaName"
		end if
		
		'else
		'strSQLStmt = "Select * from sharod order by pujaName"
		'end if	
		
		'strSQLStmt = "Select * from sharod order by pujaName"	

  rs.PageSize = 5
  rs.CacheSize = 5
  rs.CursorLocation = adUseClient

 rs.Open strSQLStmt, 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
  Response.Write "<br><br>" & vbcrlf

  Dim fldF, intRec

  Response.Write "<table border=1 align=center cellpadding=3 "
  Response.Write "cellspacing=0><thead><tr>"
  For Each fldF in rs.Fields
   Response.Write "<td>" & fldF.Name & "</td>"
  Next
  Response.Write "</tr></thead><tbody>"

  For intRec=1 To rs.PageSize
   If Not rs.EOF Then
    Response.Write "<tr>"
    For Each fldF in rs.Fields
     Response.Write "<td>" & fldF.Value & "</td>"
    Next
    Response.Write "<tr>"
    rs.MoveNext
   End If
  Next
  Response.Write "</tbody></table><p>"
  
  
  ' Now showing first, next, back, last buttons.

Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href="""
Response.Write Request.ServerVariables("SCRIPT_NAME")
Response.Write "?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")
	Response.Write "?pagenum=" & abspage - 1 & "&amp;txtPujaName="&request.QueryString("txtPujaName")&"&amp;lstZone=None&amp;lstType="&request.QueryString("1stType")&"&amp;hdIsPageSub=1&amp;btnPujaSerch=Search""><b>Previous Page</b></a>"
End If

Response.Write "	|	"
	If abspage < pagecnt Then
		Response.Write "<a href=""" & _
			Request.ServerVariables("SCRIPT_NAME")
		Response.Write "?pagenum=" & abspage + 1 & "&amp;txtPujaName="&request.QueryString("txtPujaName")&"&amp;lstZone=None&amp;lstType="&request.QueryString("1stType")&"&amp;hdIsPageSub=1&amp;btnPujaSerch=Search"">Next Page</a>"
	Else
		Response.Write "<span style=""color:silver;""" & _
			"><b>Next Page</b></span>"
End If

Response.Write "	|	"
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME")
	Response.Write "?pagenum=" & pagecnt & "&amp;txtPujaName="&request.QueryString("txtPujaName")&"&amp;lstZone=None&amp;lstType="&request.QueryString("1stType")&"&amp;hdIsPageSub=1&amp;btnPujaSerch=Search""><b>Last Page</b></a>"
Response.Write "</div>" & vbcrlf

Else
	Response.Write "No records found!"
End If

rs.Close
Set rs = Nothing

'?txtPujaName=PRASANNAMOYEE+GHAT&lstZone=None&lstType=Baroari&hdIsPageSub=1&btnPujaSerch=Search
%>
</body>
</html>

Open in new window

0
 

Author Comment

by:braindrops
ID: 36504859
Thanks. I finally got the solution. The problem was that the code could not differentiate whether the values were coming from the FORM or the Query.String. So, when I was going to the next page, it picked up value from the Form and not the querystring.
Solved by checking with a hidden text-field.
Thanks again.
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 36506760
Glad you got it working.
Happy Coding.

Carrzkiss
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developer tools in browsers have been around for a while, yet they are still heavily underused by developers. Developers still fix html or CSS then refresh page to see effect, or they put alert or debugger in JavaScript and then try again and again …
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
This video teaches users how to migrate an existing Wordpress website to a new domain.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question