Solved

Problem with paging in ASP search results correctly

Posted on 2011-09-06
5
482 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction (All good things must come to an end (http://en.wikipedia.org/wiki/All_Good_Things...))The original MySQL API (http://php.net/manual/en/book.mysql.php) has gone away, deprecated by PHP in Version 5.5, and removed from PHP in all current…
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now