navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
FrontPage Alternative
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Free FrontPage Templates

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

 

***SOLVED*** Paging records by letter - Thanks BetheBall

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> ***SOLVED*** Paging records by letter - Thanks BetheBall 		Page: [1]
 
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		***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.

< Message edited by lovduv -- 3/2/2007 19:39:51 >
BeTheBall

 

Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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") &"%'"

_____________________________

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.

(in reply to lovduv)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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")%>


(in reply to BeTheBall)
BeTheBall

 

Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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?

_____________________________

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.

(in reply to lovduv)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

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

(in reply to BeTheBall)
Spooky

 

Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		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

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to lovduv)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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

(in reply to Spooky)
BeTheBall

 

Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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>


_____________________________

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.

(in reply to lovduv)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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?

(in reply to BeTheBall)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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

(in reply to lovduv)
BeTheBall

 

Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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

_____________________________

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.

(in reply to lovduv)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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

(in reply to BeTheBall)
BeTheBall

 

Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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

_____________________________

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.

(in reply to lovduv)
Spooky

 

Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		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?

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to BeTheBall)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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
%>


(in reply to Spooky)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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!

(in reply to lovduv)
BeTheBall

 

Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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.

_____________________________

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.

(in reply to lovduv)
Spooky

 

Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline

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

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to BeTheBall)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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!


< Message edited by lovduv -- 3/2/2007 19:23:09 >

(in reply to Spooky)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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>

(in reply to lovduv)
Spooky

 

Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		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

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to lovduv)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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...

(in reply to Spooky)
lovduv

 

Posts: 152
Joined: 8/30/2005
Status: offline

  		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).

< Message edited by lovduv -- 3/2/2007 22:04:18 >

(in reply to lovduv)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> ***SOLVED*** Paging records by letter - Thanks BetheBall 		Page: [1]
Jump to: 1





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


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.