Solved

Problem with paging in ASP search results correctly

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Using Quotation Marks in PHP This question (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28217211.html) seems to come up a lot for developers who are new to PHP.  And it got me thinking, "How can we explain the rule…
New Relic: Our company recently started researching several products to figure out what were the best ways for us to increase our web page speed and to quickly identify performance problems that we may be having. One of the products we evaluated wa…
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.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

747 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

13 Experts available now in Live!

Get 1:1 Help Now