***SOLVED*** Paging records by letter

***SOLVED*** Paging records by letter - Thanks BetheBall (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

lovduv -> ***SOLVED*** Paging records by letter - Thanks BetheBall (3/1/2007 0:05:14)

Ok I have this code which gives me the abc links and I can get the records to list so the connection is good.

<%@ Language=VBScript %> <%

Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & _ 

Server.MapPath("\afolder\adatabase.mdb"))
Dim strSQL, rs

strSQL = "Select nickname from Results"

set rs = Server.CreateObject("ADODB.Recordset")

rs.Open strSQL, conn

    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='Alpha.asp?Ltr=" & _ 
                Chr(i) & "'>" & Chr(i) & "</a>" 
        else 
            response.write "<b>" & Chr(i) & "</b>" 
        end if 
    next 
    SQL = "SELECT nickname FROM Results WHERE nickname LIKE '" & Ltr & "%'" 
     
do while not rs.EOF

Response.Write "<BR>" 

Response.Write rs("nickname") 'This was just to make sure the connection is good

Response.Write "<BR>"

rs.MoveNext

loop

  
rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing

%>

Problem is I have no idea how to get the records for each letter to populate?

i.e:
This url - http://www.somesite.com/Alpha.asp?Ltr=A
Does not populate all the records that start with A, I have no idea how to do this.

BeTheBall -> RE: Paging records by letter - may need the Big Baaa :) (3/1/2007 9:28:07)
You need to modify your SQL to add a criteris. Something like: strSQL = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%'"

lovduv -> RE: Paging records by letter - may need the Big Baaa :) (3/1/2007 11:53:15)

Ok I change dthe above to this;

    strSQL = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%'" 
    
do while not rs.EOF

Response.Write "<BR>" 

Request.QueryString("strSQL")

Response.Write "<BR>"

rs.MoveNext

loop

  
rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing
%>

Then I added this to the page and I am pulling nothing what newb thing am I forgetting here?:
<%=Request.QueryString("strSQL")%>

BeTheBall -> RE: Paging records by letter - may need the Big Baaa :) (3/1/2007 12:09:05)
What URL do you see in the status bar if you hover over say the letter A?

lovduv -> RE: Paging records by letter - may need the Big Baaa :) (3/1/2007 18:20:11)
http://www.somesitel.com/Alpha.asp?Ltr=A

Spooky -> RE: Paging records by letter - may need the Big Baaa :) (3/1/2007 19:01:46)
I would try the same thing, but with an OLEDB connection - not odbc

lovduv -> RE: Paging records by letter - may need the Big Baaa :) (3/1/2007 21:59:38)

Ok I cleaned it up, changed the connection and put it in a table (for my own sake). I can't get it to show only the names starting with the letter clicked, and I can't get them to sort for some reason? Here's the new code.

set conn=Server.CreateObject("ADODB.Connection") 
conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
conn.open server.mappath("/afolder/adatabase.mdb")

Dim strSQL, rs

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT nickname FROM Results", conn

    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='Alpha.asp?Ltr=" & _ 
                Chr(i) & "'>" & Chr(i) & "</a>" 
        else 
            response.write "<b>" & Chr(i) & "</b>" 
        end if 
    next 
    strSQL = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname" 
%> 
<br>
<br>
<table border="1" width="50%">
<%do until rs.EOF%>
   <tr>
   <%for each nickname in rs.Fields%>
      <td><%Response.Write(nickname.value)%></td>
   <%next
   rs.MoveNext%>
   </tr>
<%loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
</table>

Here is a live example www.360gl.com/alpha.asp

BeTheBall -> RE: Paging records by letter - may need the Big Baaa :) (3/1/2007 23:30:13)

Give this a go:

set conn=Server.CreateObject("ADODB.Connection") 
conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
conn.open server.mappath("/afolder/adatabase.mdb")

Dim strSQL, rs

set rs = Server.CreateObject("ADODB.recordset")
if request.querystring("Ltr") <>"" Then
strSQL = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname" 
else
rs.Open "SELECT nickname FROM Results", 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='Alpha.asp?Ltr=" & _ 
                Chr(i) & "'>" & Chr(i) & "</a>" 
        else 
            response.write "<b>" & Chr(i) & "</b>" 
        end if 
    next 
%> 
<br>
<br>
<table border="1" width="50%">
<%do until rs.EOF%>
   <tr>
   <%for each nickname in rs.Fields%>
      <td><%Response.Write(nickname.value)%></td>
   <%next
   rs.MoveNext%>
   </tr>
<%loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
</table>

lovduv -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 4:10:10)
Ok the sort is working I had to add: ORDER by nickname to this: rs.Open "SELECT nickname FROM Results", conn So now it's this set rs = Server.CreateObject("ADODB.recordset") if request.querystring("Ltr") <>"" Then strSQL = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname" else rs.Open "SELECT nickname FROM Results ORDER BY nickname", conn end if I am getting this error: ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed. /Alpha.asp, line 28 The link above will show this error. Maybe this is the problem? else rs.Open "SELECT nickname FROM Results ORDER BY nickname", conn it needs to somehow update the "strSQL =" right?

lovduv -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 4:14:39)

No thats not the problem because I can get that record set, and sorted correctly....

So... somewhere in here?

set rs = Server.CreateObject("ADODB.recordset")
if request.querystring("Ltr") <>"" Then
strSQL = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname"
else

BeTheBall -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 9:28:23)
Bad cut and paste on my part. Change this: rs.Open = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname", conn else rs.Open "SELECT nickname FROM Results", conn end if to: strSQL = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname" else rs.Open "SELECT nickname FROM Results", conn end if

lovduv -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 10:04:24)

Nope your not crazy...lol
That's what you posted the first time, which is giving me the error:

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

/Alpha.asp, line 28

So currently my code is this:


<%

Dim conn

set conn=Server.CreateObject("ADODB.Connection") 
conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
conn.open server.mappath("/afolder/adatabase.mdb")

Dim strSQL, rs

set rs = Server.CreateObject("ADODB.recordset")
if request.querystring("Ltr") <>"" Then
strSQL = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname " 
else 
rs.Open "SELECT nickname FROM Results ORDER BY nickname ", 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='Alpha.asp?Ltr=" & _ 
                Chr(i) & "'>" & Chr(i) & "</a>" 
        else 
            response.write "<b>" & Chr(i) & "</b>" 
        end if 
    next 
%> 
<br>
<br>
<table border="1" width="50%">
<%do until rs.EOF%>
   <tr>
   <%for each gamertag in rs.Fields%>
      <td><%Response.Write(nickname .value)%></td>
   <%next
   rs.MoveNext%>
     </tr>
<%loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
</table>

You can see the above code with error here - www.360gl.com/alpha.asp

BeTheBall -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 10:46:16)
OK, I'm losing my mind. I swear what posted is not what I typed. One more try: if request.querystring("Ltr") <>"" Then rs.Open = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname" , conn else rs.Open "SELECT nickname FROM Results ORDER BY nickname", conn end if

Spooky -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 15:43:18)
Whats line 28? It doesnt match the code youve given?

lovduv -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 17:52:44)
Dont worry Duane I have much worse moments! Ok so now I get this error: Expected end of statement /Alpha.asp, line 13 rs.Open = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname" , conn Spooky Re: Line 28 in my code it's the end of the code block on line 28 %>

lovduv -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 18:04:00)
Guess what it works!!!!!!!! I started looking at this: rs.Open = "Select nickname from Results WHERE nickname LIKE '"& Request.QueryString("Ltr") &"%' ORDER BY nickname" , conn and the "=" looked out of place so i removed it and it's good to go!! Thank you, Duane, you da man!

BeTheBall -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 18:10:49)
EDIT - Yep, the = wasn't supposed to be there.

Spooky -> RE: Paging records by letter - may need the Big Baaa :) (3/2/2007 18:12:56)
Correct - the "=" shouldnt be there

lovduv -> Oops One More Thing!!! Re: Paging (3/2/2007 19:09:04)
Sorry I thought this would be as easy as adding this to the below table: <td><a href="nicklisting.aspx?nickname=<%=Request.Querystring("nickname")%>">Visit Nick Listing</a></td> <table border="1" width="50%"> <%do until rs.EOF%> <tr> <%for each nickname in rs.Fields%> <td style="width: 349px"><%Response.Write(nickname.value)%></td> <%next rs.MoveNext%> <td><a href="nicklisting.aspx?nickname=<%=Request.Querystring("nickname")%>">Visit Nick Listing</a></td> </tr> <%loop rs.Close Set rs = Nothing conn.Close Set conn = Nothing %> </table> I thought that the "nickname" querystring was already declared and would change with the rs.move, however the url is empty at the nicklisting= part. If this is involved just point me in the right direction and I will go play!

lovduv -> RE: Oops One More Thing!!! Re: Paging (3/2/2007 19:39:10)

I should trust myself more!!!!!!!!!!
Just needed to move this around and change Request.Querystring("nickname") to Response.Write(nickname.value) :

<table border="1" width="50%">
<%do until rs.EOF%>
   <tr>
   <%for each nickname in rs.Fields%>
      <td style="width: 349px"><%Response.Write(nickname.value)%></td>
      <td><a href="nicklisting.aspx?nickname=<%=Response.Write(nickname.value)%>">Visit Nick Listing</a></td>
   <%next
   rs.MoveNext%>
   	  

     </tr>
<%loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
</table>

Spooky -> RE: Oops One More Thing!!! Re: Paging (3/2/2007 19:43:29)
Looking at it quickly - shouldnt it be inside the for / next loop and use nickname.value ? Also - youll want to sanitize the data being passed to the SQL string. At a minimum just use left(input,1) to get the one character only

lovduv -> RE: Oops One More Thing!!! Re: Paging (3/2/2007 20:49:46)
quote: sanitize the data Hmmm gonna have to go look that up, don't know what it means, but it sounds important....I don't want dirty data! (lol) I'll be back...

lovduv -> RE: Oops One More Thing!!! Re: Paging (3/2/2007 21:00:53)
Is the DB at risk or is it on the client side? Could you elaborate on sanitize the data, I kind of understand it....should I add something like this? <%=server.htmlencode(Response.Write(nickname.value))%> edit Ok I did some more reading, the DB I am using is in a fpdb folder with browsing turned off. Also the recordset I am returning is not on a proctected page i.e there is no login to view the nickname list or the listing details, this is available to everyone so I am not passing any private info like pwds. The DB litterly only has the one field, nickname, and I don't use that for any cookies or access to anything protected in my own site. Does that mean I am ok or do I still need to be concerned about some a**hole uploading something that could erase my data which is all I care about (I don't want to lose all my nicknames).

Page: [1]

0.1855469