|
| |
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
 |
***Solved*** Paging and creating page size - 3/3/2007 0:08:27
So I am thinking when I am done with this I will just post it as whole script that someone can plug-in there own DB info as this is a real pain to get it all working together.......well for a newb like me! 
Ok so I want to set-up page size for my records...this way no more than 25 records will return on a given page. Then at the end of the rs links to any additional pages. I have added this chunk of code:
<%
loop
'Declare variables
Dim pCurrentPage
Dim pPageSize
Dim p
Dim sPageURL
sPageURL = Request.ServerVariables("SCRIPT_NAME")
pCurrentPage = Request.QueryString("Page")
If pCurrentPage = "" Or pCurrentPage = 0 Then pCurrentPage = 1
pPageSize = 25
rs.PageSize = pPageSize
rs.CacheSize = pPageSize
rs.AbsolutePage = pCurrentPage
For p = 1 to rs.PageCount
If p = CInt(pCurrentPage) Then
Response.Write "[ Page " & p & " ] "
Else
Response.Write "[ <a href=""" & sPageURL & "?Page=" & p & Chr(34) & ">Page " & p & "</a> ] "
End If
Next
Can't get it working, I get the error:
ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
< Message edited by lovduv -- 3/7/2007 10:15:49 >
|
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/3/2007 8:59:04
Ok I am still stuck with the page size, but I have removed the above code as I couldn't get it to do anything in my code. While I was waiting for hopefully a brilliant idea to come to me I moved on to the next part for this page....a search form. I have it working.....sorta (lol)
<%@ Language=VBScript %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
</head>
<body>
<%
Dim conn
'Declare constants
Const adLockReadOnly = 1 'Open a RecordSet in read-only mode
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
conn.open server.mappath("/afolder/adatabase.mdb")
Dim rs, strSearch
strSearch=Request("searchGT")
set rs = Server.CreateObject("ADODB.recordset")
if request.querystring("Ltr") <>"" Then
rs.open "Select gamertag from Results WHERE gamertag LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY gamertag", conn
elseif request("searchGT")<>"" Then
rs.Open "SELECT gamertag FROM Results ORDER BY gamertag", conn, 1, , adCmdText
rs.find ("gamertag LIKE '%" & strSearch & "%'")
else
rs.Open "SELECT gamertag FROM Results ORDER BY gamertag", conn
end if
Ltr = UCase(Request.QueryString("Ltr"))
if (Ltr < "A") or (Ltr > "Z") then Ltr = "A"
for i = 65 to 90
if i > 65 then response.write " | "
if Asc(Ltr) <> i then
response.write "<a href='alphawsearch.asp?Ltr=" & _
Chr(i) & "'>" & Chr(i) & "</a>"
else
response.write "<b>" & Chr(i) & "</b>"
end if
next
%>
<br />
<a href="alphawsearch.asp">ALL</a>
<br/>
<br />
<form method="post" action="" onsubmit="validateForm (this)" style="width: 610px">
Search for a Gamertag using the box below.<br />
<br />
<input name="searchGT" type="text" style="height: 21px" />
<input style name="submitSearch" type="submit" value="GO"/>
</form>
<br/>
<table border="1" style="width: 95%" cellspacing="1" cellpadding="2">
<tr>
<td Gamertag</td>
<td>Listing</td>
</tr>
<tr>
<%do until rs.EOF%>
<%for each gamertag in rs.Fields%><%
Dim x, bgcolor
if x = 1 then
bgcolor="#E1FAB8"
x=2
Else
bgcolor="#AED174"
x=1
End if %>
<td style="background-color:<%=bgcolor%>;"><%Response.Write(gamertag.value)%>
</td>
<td style="background-color:<%=bgcolor%>;"><a href="gamerlisting.aspx?gamertag=<%Response.Write(gamertag.value)%>">
View Gamer Listing</a></td>
<%next
rs.MoveNext%>
</tr>
<%loop%>
</table>
<%
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
</body>
</html>
So here are my current frustrations:
1. The search string - rs.find ("gamertag LIKE '%" & strSearch & "%'") seems to return the tag that is the closest and then every record after (maybe because I am using .find), how can I fix this so that it returns only the tags closest to what a user inputs?
For example say I want to search for the tag "halfhot", and I only know half the tag (no pun intended..it's late....early...whatever) so all I can remember is half and I type that in.
If you do this at the above example page it will return the tag halfhot...yea!
Then it returns every record from that tag on...boooo!
So how can I get it to only return the tag halfhot? or tags very similar to allow for not knowing the exact spelling of a tag?
2. Again using the above example page:
If you click a letter it returns all the tags starting with that letter....yea!
But the search will not work at all on the page now that the url has alphawsearch.asp?Ltr=A.......booo!
So how can I either utilize the search on a letter page.....or remove it all together (this is probably better) when a user is searching by letter?
3. What do I need to consider security wise, will I be ok since my DB is in a protected folder without browse permissions or do I need to sanitize (which is a brand new concept...ty spooky) and what should I sanitize?
Lastly, I still after being up all night searching, have no idea how to control the number of records returned...so that there will only be 25 records to a page with a next, arrow, number...etc. link to the next 25?
Ok I am gonna go get some sleep now and maybe some brilliant mind (*hint* BetheBall, Spooky, Rdouglass) will have some nifty ideas for me when I get up!
< Message edited by lovduv -- 3/6/2007 9:47:15 >
| |
|
|
|
BeTheBall
Posts: 6354
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/3/2007 10:08:45
I think I would switch things around a bit. Also, I have never used .find, but judging by the results you are getting, it looks like it acts more like a cursor than a true search. How about something like this:
strSearch=Request("searchGT")
set rs = Server.CreateObject("ADODB.recordset")
if request("searchGT")<>"" Then
rs.Open "SELECT gamertag FROM Results ORDER BY gamertag WHERE gamertag LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY gamertag", conn
elseif request.querystring("Ltr") <>"" Then
rs.open "Select gamertag from Results WHERE gamertag LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY gamertag", conn
else
rs.Open "SELECT gamertag FROM Results ORDER BY gamertag", conn
end if
See if that works. Once it does, we can look into the "gulp", paging.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/3/2007 13:49:03
Is there a paging option that doesn't use a url to poulate the pagesize?
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/4/2007 2:25:09
Ok I got it give me a few hours and I'll post it up, I am still not sure about what vulnerabilities may exist in the script and I don't want to leave myself open to attacks!
| |
|
|
|
BeTheBall
Posts: 6354
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/4/2007 10:46:48
Change:
strSearch=Request("searchGT")
to:
strSearch=Replace(Request("searchGT"),"'","''")
Also, change:
rs.open "Select gamertag from Results WHERE gamertag LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY gamertag", conn
to:
rs.open "Select gamertag from Results WHERE gamertag LIKE '"& Left(Request.QueryString("Ltr"),1) &"%' ORDER BY gamertag", conn
That should go along way toward thwarting most attacks.
As for paging, I have used this example a few times with good success:
http://www.asp101.com/articles/recordsetpaging/index.asp
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/4/2007 17:29:44
Crazy the script you suggested was the exact same one I had found to use as an example..."Great Minds Think Alike" huh? (lol)
Ok so this is what I have now which is working, I added the changes above to hopefully make it more secure. Do you see anything else?
<%@ Language=VBScript %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<%
Dim objConn
Dim objRst
Dim intPageCount
Dim intRecordCount
Dim intPage
Dim intRecord
Dim intStart
Dim intFinish
Dim strSearch
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
objConn.open server.mappath("/afolder/adatabase.mdb")
strSearch=Replace(Request("searchGT"),"'","''")
Set objRst = Server.CreateObject("ADODB.Recordset")
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
If Request.Form("searchGT") <> "" Then
strSearch = Request.Form("searchGT")
Else
strSearch = Request.QueryString("YEAR")
End If
if request("searchGT")<>"" Then
objRst.Open "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' ORDER BY gamertag", objConn
elseif request.querystring("Abc") <>"" Then
objRst.open "Select gamertag from Results WHERE gamertag LIKE '"& Left(Request.QueryString("Abc"),1) &"%' ORDER BY gamertag", objConn
else
objRst.Open "SELECT gamertag FROM Results ORDER BY gamertag", objConn
end if
objRst.PageSize = 5
objRst.CacheSize = objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount = objRst.RecordCount
If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (objRst.PageSize - 1)
End if
End If
'Builds query for paging by letter
Abc = UCase(Request.QueryString("Abc"))
if (Abc < "A") or (Abc > "Z") then Abc = "A"
for i = 65 to 90
if i > 65 then response.write " | "
if Asc(Abc) <> i then
response.write "<a href='alphawsearch.asp?Abc=" & _
Chr(i) & "'>" & Chr(i) & "</a>"
else
response.write "<b>" & Chr(i) & "</b>"
end if
next
%>
<body>
<br/>
<a href="alphawsearch.asp">ALL</a>
<br/>
<br />
<form method="post" action="" onsubmit="validateForm (this)" style="width: 610px">
Search for a Gamertag using the box below.<br />
<br />
<input name="searchGT" type="text" style="height: 21px" />
<input style="font-family:Verdana;font-variant:small-caps;font-weight:600;font-size:1em" name="submitSearch" type="submit" value="GO"/>
</form>
<br/>
<%If intRecordCount > 0 Then
%>
<table border="1" style="width: 95%" cellspacing="1" cellpadding="2">
<tr style="background-color:#90BF3F;">
<td style="height: 43px" class="style1"> Gamertag</td>
<td style="height: 43px">Listing</td>
</tr>
<tr>
<%
For intRecord = 1 to objRst.PageSize
Dim x, bgcolor
if x = 1 then
bgcolor="#E1FAB8"
x=2
Else
bgcolor="#AED174"
x=1
End if
%>
<td style="background-color:<%=bgcolor%>; width: 349px; font-family: Verdana; color: #003300; font-weight: 300; text-transform: capitalize; font-size: 1em;" class="style1">
<%Response.Write objRst("gamertag")%></td>
<td style="background-color:<%=bgcolor%>;"><a href="gamerlisting.aspx?gamertag=<%Response.Write objRst("gamertag")%>">
View Gamer Listing</a> </td>
</tr>
<%
objRst.MoveNext
If objRst.EOF Then Exit for
Next
%>
<tr><td align="right">
<h4>Gamertags returned <%=strYear%>
<%=intRecordCount%></h4>
</td></tr>
<tr><td align="right">
<%
If cInt(intPage) > 1 Then
%>
<a href="alphawsearch.asp?NAV=<%=intPage - 1%>&Abc=<%=Request.QueryString("Abc")%>">Prev</a>
<%End IF%>
<%
If cInt(intPage) < cInt(intPageCount) Then
%>
<a href="alphawsearch.asp?NAV=<%=intPage + 1%>&Abc=<%=Request.QueryString("Abc")%>">Next</a>
<%End If%>
</td></tr>
</table>
<%
End If
objRst.Close
Set objRst = Nothing
objConn.Close
Set objConn= Nothing
%>
</body>
</html>
< Message edited by lovduv -- 3/6/2007 9:48:09 >
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/5/2007 7:54:14
Ok my current problem is this:
When you execute a search for example you search for:
ac
You will get 6 records returned, you will see 5 of them as the page size is set to 5 records, so far so good.
If you click the next button, you move to record 6 , perfect.
Now I have added this text using variables at the bottom to see what's going on:
The search for ac returned 6 records. You are now viewing records 6 through 6.
If you from this page type in a new search let's say re, the url stays the same:
HOWEVER....The page returns with records based on the correct search term it is just returned on the second page of records...meaning NAV=2, instead of NAV= 1.
And the text changes to:
The search for re returned 7 records. You are now viewing records 6 through 7.
So my change to re is correct in the text, but not in the url. The recordset is correct, but it returns on the second page rather than the first? Also if you click the Prev link from here it will take you to page 1 of the correct recordset for the "re" search and the url will change to the correct url .
And the text changes to:
The search for re returned 7 records. You are now viewing records 1 through 5.
Which is correct....
My first thought was to reset the page each time a new search is performed with a javascript event (I thought onchange would work, but once you click go your search term is submitted and the text is gone, so a change doesn't occur), but I haven't had any luck with it?
Then I thought well if I could somehow set the to querystring to nav=1 when a new search is performed, then it would always start on page 1, but I am not sure how I could do that as you can't pass a querysting to asp using a javascript event, at least as far as I know? Got an idea?
Here is the current code:
<%@ Language=VBScript %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<%
Dim objConn
Dim objRst
Dim intPageCount
Dim intRecordCount
Dim intPage
Dim intRecord
Dim intStart
Dim intFinish
Dim strSearch
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
objConn.open server.mappath("/afolder/adatabase.mdb")
strSearch=Replace(Request("searchGT"),"'","''")
Set objRst = Server.CreateObject("ADODB.Recordset")
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
If Request.Form("searchGT") <> "" Then
strSearch = Request.Form("searchGT")
Else
strSearch = Request.QueryString("Search")
End If
if request("searchGT")<>"" Then
objRst.Open "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' ORDER BY gamertag", objConn
elseif request.querystring("Search")<>"" Then
objRst.Open "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' ORDER BY gamertag", objConn
elseif request.Querystring("Abc") <>"" Then
objRst.open "Select gamertag from Results WHERE gamertag LIKE '"& Left(Request.QueryString("Abc"),1) &"%' ORDER BY gamertag", objConn
else
objRst.Open "SELECT gamertag FROM Results ORDER BY gamertag", objConn
end if
objRst.PageSize = 5
objRst.CacheSize = objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount = objRst.RecordCount
If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (objRst.PageSize - 1)
End if
End If
'Builds query for paging by letter
Abc = UCase(Request.QueryString("Abc"))
if (Abc < "A") or (Abc > "Z") then Abc = "A"
for i = 65 to 90
if i > 65 then response.write " | "
if Asc(Abc) <> i then
response.write "<a href='alphawsearch.asp?Abc=" & _
Chr(i) & "'>" & Chr(i) & "</a>"
else
response.write "<b>" & Chr(i) & "</b>"
end if
next
%>
<body>
<br/>
<a href="alphawsearch.asp">ALL</a>
<br/>
<br />
<form method="post" action="" onsubmit="validateForm (this)" style="width: 610px">
Search for a Gamertag using the box below.<br />
<br />
<input name="searchGT" type="text" style="height: 21px; width: 141px;" />
<input style="font-family:Verdana;font-variant:small-caps;font-weight:600;font-size:1em" name="submitSearch" type="submit" value="GO"/>
</form>
<br/>
<%If intRecordCount > 0 Then
%>
<table border="1" style="width: 95%" cellspacing="1" cellpadding="2">
<tr style="background-color:#90BF3F;">
<td style="height: 43px" class="style1"> Gamertag</td>
<td style="height: 43px">Listing</td>
</tr>
<tr>
<%
For intRecord = 1 to objRst.PageSize
Dim x, bgcolor
if x = 1 then
bgcolor="#E1FAB8"
x=2
Else
bgcolor="#AED174"
x=1
End if
%>
<td style="background-color:<%=bgcolor%>; width: 349px; font-family: Verdana; color: #003300; font-weight: 300; text-transform: capitalize; font-size: 1em;" class="style1">
<%Response.Write objRst("gamertag")%></td>
<td style="background-color:<%=bgcolor%>;"><a href="gamerlisting.aspx?gamertag=<%Response.Write objRst("gamertag")%>">
View Gamer Listing</a> </td>
</tr>
<%
objRst.MoveNext
If objRst.EOF Then Exit for
Next
%>
<tr><td align="right">
<h4>The search for <%=strSearch%> returned
<%=intRecordCount%> records.</h4>
</td>
<td><h4>You are now viewing records
<%=intStart%> through <%=intFinish%>.</h4>
</td></tr>
<tr><td align="right">
<%
If cInt(intPage) > 1 Then
%>
<a href="alphawsearch.asp?NAV=<%=intPage - 1%>&Abc=<%=Request.QueryString("Abc")%>&Search=<%=strSearch%>">Prev</a>
<%End IF%>
<%
If cInt(intPage) < cInt(intPageCount) Then
%>
<a href="alphawsearch.asp?NAV=<%=intPage + 1%>&Abc=<%=Request.QueryString("Abc")%>&Search=<%=strSearch%>">Next</a>
<%End If%>
</td></tr>
</table>
<%
End If
objRst.Close
Set objRst = Nothing
objConn.Close
Set objConn= Nothing
%>
</body>
</html>
< Message edited by lovduv -- 3/6/2007 9:50:17 >
| |
|
|
|
BeTheBall
Posts: 6354
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/5/2007 9:31:22
Along with what Spooky said, I wonder if the blank action in the forms attributes is causing the problem. Try changing:
<form method="post" action="" onsubmit="validateForm (this)" style="width: 610px">
to
<form method="post" action="alphawsearch.asp" onsubmit="validateForm (this)" style="width: 610px">
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/5/2007 10:04:17
Beautiful BetheBall that was the problem I have been messing with the code so much I can't even see the simple stuff!! Thanks for being my second set of eyes!!!
Here's another question:
If you search for just the letter a (I know all you have to do is click "a", but somewhere along the line a user will search for it instead) the records return sorted by gamertag, which is what I told it to do, but I would rather it return sorted by the search term "strSearch" can I do that?
Do either of you see anything else that leaves me vulnerable to an attack on the DB?
And correct me if I am wrong, but I can move all of this into an include, right?
<%
Dim objConn
Dim objRst
Dim intPageCount
Dim intRecordCount
Dim intPage
Dim intRecord
Dim intStart
Dim intFinish
Dim strSearch
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
objConn.open server.mappath("/afolder/adatabase.mdb")
strSearch=Replace(Request("searchGT"),"'","''")
Set objRst = Server.CreateObject("ADODB.Recordset")
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
If Request.Form("searchGT") <> "" Then
strSearch = Request.Form("searchGT")
Else
strSearch = Request.QueryString("Search")
End If
if request("searchGT")<>"" Then
objRst.Open "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' ORDER BY gamertag", objConn
elseif request.querystring("Search")<>"" Then
objRst.Open "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' ORDER BY gamertag", objConn
elseif request.Querystring("Abc") <>"" Then
objRst.open "Select gamertag from Results WHERE gamertag LIKE '"& Left(Request.QueryString("Abc"),1) &"%' ORDER BY gamertag", objConn
else
objRst.Open "SELECT gamertag FROM Results ORDER BY gamertag", objConn
end if
objRst.PageSize = 5
objRst.CacheSize = objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount = objRst.RecordCount
If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (objRst.PageSize - 1)
End if
End If
'Builds query for paging by letter
Abc = UCase(Request.QueryString("Abc"))
if (Abc < "A") or (Abc > "Z") then Abc = "A"
for i = 65 to 90
if i > 65 then response.write " | "
if Asc(Abc) <> i then
response.write "<a href='alphawsearch.asp?Abc=" & _
Chr(i) & "'>" & Chr(i) & "</a>"
else
response.write "<b>" & Chr(i) & "</b>"
end if
next
%>
Other than that this sucker is done(lol)!
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/5/2007 20:18:00
Thanks guys everything seems to be working now!
Last thoughts,
Still wondering if there is a way that when a user searches the ORDER BY could be based on their search:
This code:
objRst.Open "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' ORDER BY gamertag", objConn
Would have to be changed to something like this, I guess:
objRst.Open "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' ORDER BY strSearch", objConn
But the above doesn't work....
Also It would be nice if I could take this chunk:
<tr><td align="right">
<h4>The search for <%=strSearch%> returned
<%=intRecordCount%> records.</h4>
</td>
<td><h4>You are now viewing records
<%=intStart%> through <%=intFinish%>.</h4>
</td></tr>
Which outputs this with no search:
The search for returned 84 records. You are now viewing records 1 through 5.
And this with a search:
The search for ac returned 6 records. You are now viewing records 1 through 5.
I would love to be able to have the no search display as:
There are 84 records. You are now viewing records 1 through 5. Would I do this with an if then statement and response.write the <td> ?
| |
|
|
|
BeTheBall
Posts: 6354
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/5/2007 20:27:54
quote:
Still wondering if there is a way that when a user searches the ORDER BY could be based on their search:
I don't see how. The ORDER BY has to be a column name. The user enters a free form search item which is NOT a column from your database table.
As for the "You are viewing" part, yes, you would need to do it with If/Then statements.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/5/2007 23:26:08
Ok everything is working beautifully now....Thank yoooouuu sooo much for all your help guys!
Here is the script, I wrote it up so that any Outfront user would be able to easily add it to an existing or new site, all you need is a DB! If this would be better as a new topic or moved....Feel Free! I also tried to add or keep the appropriate comments if you see something that should be added, again feel free.
Script for a search page with page size, and Abc paging.
The paging in this script is based on the script found here, http://www.asp101.com/articles/recordsetpaging/index.asp
the search, and ABC paging were added by me, with help from BetheBall and Spooky from Outfront.
Enjoy!
Copy and paste the entire script below into a new page save the page as alphawsearch.asp:
<%@ Language=VBScript %>
<html>
<head>
<script type="text/javascript">
<!-- Prevents a user from entering a blank search
function validateForm(form){
if (form.searchRS.value=="") {
alert ("Sorry, you must enter a search term.")
return false
}
return true
}
//-->
</script>
</head>
<!--You can put this in an include, This starts the include-->
<%
' Script for a search page with page size, and Abc paging.
' The paging in this script is based on the script found here: http://www.asp101.com/articles/recordsetpaging/index.asp
' the search, and ABC paging were added by me with help from BetheBall and Spooky from Outfront.
' Things to change (I use find and replace):
' Connection string (/afolder/adatabase)
' DBfield = the field in your database you want to search
' YourTable = the table in your database that holds your database field
' The following is optional:
' details.asp?DBfield
' If you have a page that gives a detailed view of a selected record change details.asp to that page and
' Record= to your querystring.
' If you do not have a details page delete this entire line of code:
' <td style="background-color: =bgcolor ;"><a href="details.asp?Record= Response.Write objRst("DBfield") ">View Record Details</a> </td>
'Declare variables
Dim objConn
Dim objRst
Dim intPageCount
Dim intRecordCount
Dim intPage
Dim intRecord
Dim intStart
Dim intFinish
Dim strSearch
' Check to see if there is value in the NAV querystring. If there
' is, we know that the client is using the Next and/or Prev hyperlinks
' to navigate the recordset.
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
'Create Connection
Set objConn = Server.CreateObject("ADODB.Connection")
' Set the connection string. You will, need to change /afolder/adatabase
' to the appropiate path for your computer.
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
objConn.open server.mappath("/afolder/adatabase.mdb")
'Create the Record Set
Set objRst = Server.CreateObject("ADODB.Recordset")
' The CursorLocation and the CursorType must be set as they are here
' in order for Recordset Paging to work properly.
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
'Declare Search Variables
If Request.Form("searchRS") <> "" Then
strSearch = Request.Form("searchRS")
Else
strSearch = Request.QueryString("Search")
End If
strSearch=Replace(strSearch,"'","''")
'If statements that build the Record Set
if request("searchRS")<>"" Then
objRst.Open "SELECT DBfield FROM YourTable WHERE DBfield LIKE '%" & strSearch & "%' ORDER BY DBfield", objConn
elseif request.querystring("Search")<>"" Then
objRst.Open "SELECT DBfield FROM YourTable WHERE DBfield LIKE '%" & strSearch & "%' ORDER BY DBfield", objConn
elseif request.Querystring("Abc") <>"" Then
objRst.open "Select DBfield FROM YourTable WHERE DBfield LIKE '"& Left(Request.QueryString("Abc"),1) &"%' ORDER BY DBfield", objConn
else
objRst.Open "SELECT DBfield FROM YourTable ORDER BY DBfield ", objConn
end if
'Set the number of reords returned
objRst.PageSize = 5
' The cachesize property sets the number of records that will be cached
' locally in memory.
objRst.CacheSize = objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount = objRst.RecordCount
' Now you must double check to make sure that you are not before the start
' or beyond end of the recordset. If you are beyond the end, set
' the current page equal to the last page of the recordset. If you are
' before the start, set the current page equal to the start of the recordset.
If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
' Make sure that the recordset is not empty. If it is not, then set the
' AbsolutePage property and populate the intStart and the intFinish variables.
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (objRst.PageSize - 1)
End if
End If
'Builds query for paging by letter
Abc = UCase(Request.QueryString("Abc"))
if (Abc < "A") or (Abc > "Z") then Abc = "A"
for i = 65 to 90
if i > 65 then response.write " | "
if Asc(Abc) <> i then
response.write "<a href='alphawsearch.asp?Abc=" & _
Chr(i) & "'>" & Chr(i) & "</a>"
else
response.write "<b>" & Chr(i) & "</b>"
end if
next
%>
<!--This ends the include-->
<body>
<br>
<a href="alphawsearch.asp">ALL</a>
<br>
<br>
<!--Search Form with Javascript validation to prevent blank searches-->
<form method="post" action="alphawsearch.asp" onsubmit="validateForm (this)">
Search for a record using the box below.<br >
<br>
<input name="searchRS" type="text" >
<input name="submitSearch" type="submit" value="GO">
</form>
<br>
<%If intRecordCount > 0 Then
' Display the record that you are starting on and the record
' that you are finishing on for this page by writing out the
' values in the intStart and the intFinish variables.
%>
<table border="1" style="width: 95%" cellspacing="1" cellpadding="2">
<tr>
<td style="height: 43px" >Record</td>
<td style="height: 43px">Record Details</td>
</tr>
<tr>
<%
' Iterate through the recordset until we reach the end of the page
' or the last record in the recordset.
For intRecord = 1 to objRst.PageSize
'Alternates Row Colors change bgcolor to the color you want *Code snippet from http://www.frontpagehowto.com/altcolor.htm?*
Dim x, bgcolor
if x = 1 then
bgcolor="#E1FAB8"
x=2
Else
bgcolor="#AED174"
x=1
End if
%>
<!--Displays the records in a table and sets the color of each row-->
<td style="background-color:<%=bgcolor%>; width: 349px; font-family: Verdana; color: #003300; font-weight: 300; text-transform: capitalize; font-size: 1em;" class="style1">
<%Response.Write objRst("DBfield")%></td>
<td style="background-color:<%=bgcolor%>;"><a href="details.asp?Record=<%Response.Write objRst("DBfield")%>">
View Record Details</a> </td>
</tr>
<%
objRst.MoveNext
If objRst.EOF Then Exit for
Next
If Request.Form("searchRS") <> "" OR Request.Querystring("Search") <>"" Then
Response.Write "<tr><td align='left'><h4>Your search for "%><span style="text-transform: capitalize;"><%Response.Write"" & strSearch & ""%></span><% Response.Write" returned " & intRecordCount & " Records.</h4></td>"
ElseIf request.Querystring("Abc") <>"" Then
Response.Write "<tr><td align='left'><h4>There are " & intRecordCount & " Records begining with the letter " & Abc & " in the Database.</h4></td>"
Else
Response.Write "<tr><td align='left'><h4>There are " & intRecordCount & " Records in the Database.</h4></td>"
End If
Response.Write "<td><h4>You are now viewing records " & intStart & " through " & intFinish & ".</h4></td></tr>"
Response.Write "<tr><td align='right'>"
If cInt(intPage) > 1 Then
%>
<tr><td><a href="alphawsearch.asp?NAV=<%=intPage - 1%>&Abc=<%=Request.QueryString("Abc")%>&Search=<%=strSearch%>">Prev</a></td></tr>
<%End If%>
<% ' Check to see if the current page is less than the last page
' in the recordset. If it is, then add a "Next" link.
If cInt(intPage) < cInt(intPageCount) Then
%>
<tr><td><a href="alphawsearch.asp?NAV=<%=intPage + 1%>&Abc=<%=Request.QueryString("Abc")%>&Search=<%=strSearch%>">Next</a></td></tr>
<%End If%>
</table>
<%
'Clean up and close the connection
End If
objRst.Close
Set objRst = Nothing
objConn.Close
Set objConn= Nothing
%>
</body>
</html>
< Message edited by lovduv -- 3/6/2007 9:42:42 >
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/5/2007 23:35:03
Found a minor issue:
When moving to the next page on a search i.e NAV=2
Then the if statement below doesn't carry over:
If Request.Form("searchGT") <> "" Then
Response.Write "<tr><td align='right'><h4>Your search for "%><span style="text-transform: capitalize;"><%Response.Write"" & strSearch & ""%></span><% Response.Write" returned " & intRecordCount & " Gamertags.</h4></td>"
Else
Response.Write "<tr><td align='right'><h4>There are " & intRecordCount & " Gamertags in the Gamerbase.</h4></td>"
End If
How can I get the if statement to carry over to the NAV=2 page The text is:
There are 6 Gamertags in the Gamerbase. You are now viewing records 6 through 6.
Should be:
Your search for Ac returned 6 Gamertags. You are now viewing records 6 through 6.
???
< Message edited by lovduv -- 3/6/2007 9:51:02 >
| |
|
|
|
BeTheBall
Posts: 6354
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/6/2007 0:06:41
If Request.Form("searchGT") <> "" OR Request.Querystring("Search") <>"" Then
Response.Write "<tr><td align='right'><h4>Your search for "%><span style="text-transform: capitalize;"><%Response.Write"" & strSearch & ""%></span><% Response.Write" returned " & intRecordCount & " Gamertags.</h4></td>"
Else
Response.Write "<tr><td align='right'><h4>There are " & intRecordCount & " Gamertags in the Gamerbase.</h4></td>"
End If
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: Paging and creating page size...yeesh! - 3/6/2007 9:30:16
Ok I added the changes to the script above and added an if statement for when a user is searching by letter.
If Request.Form("searchGT") <> "" OR Request.Querystring("Search") <>"" Then
Response.Write "<tr><td align='left'><h4>Your search for "%><span style="text-transform: capitalize;"><%Response.Write"" & strSearch & ""%></span><% Response.Write" returned " & intRecordCount & " Gamertags.</h4></td>"
ElseIf request.Querystring("Abc") <>"" Then
Response.Write "<tr><td align='left'><h4>There are " & intRecordCount & " Gamertags begining with the letter " & Abc & " in the Gamerbase.</h4></td>"
Else
Response.Write "<tr><td align='left'><h4>There are " & intRecordCount & " Gamertags in the Gamerbase.</h4></td>"
End If
Response.Write "<td><h4>You are now viewing records " & intStart & " through " & intFinish & ".</h4></td></tr>"
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
MS SQL CONNECTION - 3/6/2007 16:21:50
Quick question what would the MS SQL CONNECTION info for this script, for those that have it, and myself inn the future:
I assume the changes are only in here?
'Declare variables
Dim objConn
Dim objRst
Dim intPageCount
Dim intRecordCount
Dim intPage
Dim intRecord
Dim intStart
Dim intFinish
Dim strSearch
' Check to see if there is value in the NAV querystring. If there
' is, we know that the client is using the Next and/or Prev hyperlinks
' to navigate the recordset.
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
'Create Connection
Set objConn = Server.CreateObject("ADODB.Connection")
' Set the connection string. You will, need to change /afolder/adatabase
' to the appropiate path for your computer.
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
objConn.open server.mappath("/afolder/adatabase.mdb")
'Create the Record Set
Set objRst = Server.CreateObject("ADODB.Recordset")
' The CursorLocation and the CursorType must be set as they are here
' in order for Recordset Paging to work properly.
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
'Declare Search Variables
If Request.Form("searchRS") <> "" Then
strSearch = Request.Form("searchRS")
Else
strSearch = Request.QueryString("Search")
End If
strSearch=Replace(strSearch,"'","''")
'If statements that build the Record Set
if request("searchRS")<>"" Then
objRst.Open "SELECT DBfield FROM YourTable WHERE DBfield LIKE '%" & strSearch & "%' ORDER BY DBfield", objConn
elseif request.querystring("Search")<>"" Then
objRst.Open "SELECT DBfield FROM YourTable WHERE DBfield LIKE '%" & strSearch & "%' ORDER BY DBfield", objConn
elseif request.Querystring("Abc") <>"" Then
objRst.open "Select DBfield FROM YourTable WHERE DBfield LIKE '"& Left(Request.QueryString("Abc"),1) &"%' ORDER BY DBfield", objConn
else
objRst.Open "SELECT DBfield FROM YourTable ORDER BY DBfield ", objConn
end if
'Set the number of reords returned
objRst.PageSize = 5
' The cachesize property sets the number of records that will be cached
' locally in memory.
objRst.CacheSize = objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount = objRst.RecordCount
' Now you must double check to make sure that you are not before the start
' or beyond end of the recordset. If you are beyond the end, set
' the current page equal to the last page of the recordset. If you are
' before the start, set the current page equal to the start of the recordset.
If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
' Make sure that the recordset is not empty. If it is not, then set the
' AbsolutePage property and populate the intStart and the intFinish variables.
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (objRst.PageSize - 1)
End if
End If
'Builds query for paging by letter
Abc = UCase(Request.QueryString("Abc"))
if (Abc < "A") or (Abc > "Z") then Abc = "A"
for i = 65 to 90
if i > 65 then response.write " | "
if Asc(Abc) <> i then
response.write "<a href='alphawsearch.asp?Abc=" & _
Chr(i) & "'>" & Chr(i) & "</a>"
else
response.write "<b>" & Chr(i) & "</b>"
end if
next
%>
| |
|
|
|
lovduv
Posts: 152
Joined: 8/30/2005
Status: offline
|
RE: MS SQL CONNECTION - 3/7/2007 10:15:04
Got a valid MSSql connection with this, let me know if you see anything dangerous.
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Provider=SQLOLEDB;Data Source=xxxx;database=xxxx;uid=xxxx;pwd=xxxx;"
Sql = "Select gamertag From Results "
Sql2 = "Select gamertag from Results WHERE gamertag LIKE '"& Left(Request.QueryString("Abc"),1) &"%' "
Sql3 = "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' "
Sql4 = "SELECT gamertag FROM Results WHERE gamertag LIKE '%" & strSearch & "%' "
Set objRst = Server.CreateObject("ADODB.Recordset")
' Create you Recordset Object
Set objRst = Server.CreateObject("ADODB.Recordset")
' Open the recordset.
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
If Request.Form("searchGT") <> "" Then
strSearch = Request.Form("searchGT")
Else
strSearch = Request.QueryString("Search")
End If
strSearch=Replace(strSearch,"'","''")
' objRst.Open Sql
if request("searchGT")<>"" Then
objRst.Open Sql4, objConn
elseif request.querystring("Search")<>"" Then
objRst.Open Sql3, objConn
elseif request.Querystring("Abc") <>"" Then
objRst.open Sql2, objConn
else
objRst.Open Sql, objConn
end if
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k„ 