Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

Problem with paging in ASP search results correctly

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
braindrops
Asked:
braindrops
  • 3
  • 2
1 Solution
 
braindropsAuthor Commented:
0
 
Wayne BarronCommented:
Give an example of a search result, that gives multiple search pages.
This way we can see how it acts.
0
 
Wayne BarronCommented:
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
 
braindropsAuthor Commented:
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
 
Wayne BarronCommented:
Glad you got it working.
Happy Coding.

Carrzkiss
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now