Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with paging in ASP search results correctly

Posted on 2011-09-06
5
Medium Priority
?
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:braindrops
ID: 36487580
0
 
LVL 31

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 31

Accepted Solution

by:
Wayne Barron earned 1000 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 31

Expert Comment

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

Carrzkiss
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
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.

661 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