Sort DB help

breeze76 · #1 (**permalink**) 04-22-04, 00:33

Let me explain what I am looking for:

1- hyperlinked alphabets... A B C D E F ..... and so on... when you clcik the alphabet you want it will produce a table with the info that statrts with the letter A only.

2- when the table that has the list that was picked from the alphabet, the names will be hyperlinked so that when you click on that name it gives you the complete info of that name.

Like in the members list area..

Any suggestions???

breeze76

rokslide · #2 (**permalink**) 04-22-04, 01:12

Plenty of suggestions... what sort exactly are you looking for though...

Basically I'd loop through the ascii character set starting at the ascii value for A and finishing with the ascii value for Z to generate the initial hyper links...

Then for the second part I would use a db query with either a like operator and a wildcard or use a substr (or left) function depending on which turned out faster...

breeze76 · #3 (**permalink**) 04-22-04, 01:17

Quote:

Originally posted by rokslide
Plenty of suggestions... what sort exactly are you looking for though...

Basically I'd loop through the ascii character set starting at the ascii value for A and finishing with the ascii value for Z to generate the initial hyper links...

Then for the second part I would use a db query with either a like operator and a wildcard or use a substr (or left) function depending on which turned out faster...

Ok... You lost me, I am kind of new to this and somewhat understand you but do not know how to do anything you said...

sorry.. BUT, I am learning.. so if you could maybe send me an example or a place to go to see an example..

thanks
breeze76

rokslide · #4 (**permalink**) 04-22-04, 01:37

okie... the loop for the links....

Code:

Dim iAlpha, letter
' ascii value for A = 65, Z=90
for iAlpha = 65 to 90
  letter = chr(iAlpha)
  response.write "<a href='mypage.asp?letter=" & letter & "'>" & letter & "</a>"
next

then for the mypage.asp I would do something like...

Code:

Dim letter, sql
letter = request.querystring("letter")
sql = "select * from mytable where name like '" & letter & "%'"
set myResults = myConnection.Execute(sql)
do while not myResults.eof
  ' print a row of the table
  myResults.movenext
loop
myResults.close
set myResults = nothing

does that explain it enough for you??

breeze76 · #5 (**permalink**) 04-22-04, 09:25

Quote:

Originally posted by rokslide
okie... the loop for the links....

Code:

Dim iAlpha, letter
' ascii value for A = 65, Z=90
for iAlpha = 65 to 90
  letter = chr(iAlpha)
  response.write "<a href='mypage.asp?letter=" & letter & "'>" & letter & "</a>"
next

then for the mypage.asp I would do something like...

Code:

Dim letter, sql
letter = request.querystring("letter")
sql = "select * from mytable where name like '" & letter & "%'"
set myResults = myConnection.Execute(sql)
do while not myResults.eof
  ' print a row of the table
  myResults.movenext
loop
myResults.close
set myResults = nothing

does that explain it enough for you??

Ok the first part.. it goes on one page while the other is on another page, correct? I also have to make my *,mdb connectiono on both pages, correct? and On the second page I still need to make my table to show the results of the data, correct?

Also, I want to say thanks for helping me here... right now I have the names in a drop down list and it is so cumberson for the users, I just thought this would be a quicker way to get to the data they are looking for..

Thanks again

breeze76

breeze76 · #6 (**permalink**) 04-22-04, 14:06

Another question??

How would I make a dropdown list do an auto-sort as the user is typing the word?

breeze76

breeze76 · #7 (**permalink**) 04-22-04, 14:18

Ok.. I am trying to use your example above.. but all the alphabets are joined together, how do I seperate them so I can choose on at a time?

breeze76

rokslide · #8 (**permalink**) 04-22-04, 19:22

Okie, to try and answer your questions....

1. Ok the first part.. it goes on one page while the other is on another page, correct? I also have to make my *,mdb connectiono on both pages, correct? and On the second page I still need to make my table to show the results of the data, correct?

Answer: The choice is yours really you can do it all on one page if you like it doesn't make a lot of difference. You just need to figure out what your flow of logic should be when you come into the page. Splitting it off to start with is probably a good idea as it will be somewhat simplier to write.

2. How would I make a dropdown list do an auto-sort as the user is typing the word?

Answer: Do you mean like it does it a windows environment. Where you type "A" and it jumps to the first word starting with A then you type a "B" so you have AB and it takes you to the first AB word? If that it what you mean you really can't do that with a web based drop down box.
It will only use the first letter eg A not AB

3. how do I seperate them so I can choose on at a time?

Answer: you currently have this

Code:

response.write "<a href='mypage.asp?letter=" & letter & "'>" & letter & "</a>"

use something like this instead

Code:

response.write "<a href='mypage.asp?letter=" & letter & "'>" & letter & "</a>&nbsp;&nbsp;&nbsp;"

Anything else I can help with??

breeze76 · #9 (**permalink**) 04-22-04, 23:42

Ok having a problem here...

Here is what I have so far:

FIRST PAGE
.................................................. ...
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>
<%
DIM conn
set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb"
%>

<%
Dim iAlpha, letter
' ascii value for A = 65, Z=90
for iAlpha = 65 to 90
letter = chr(iAlpha)
response.write "<a href='sortlist.asp?letter=" & letter & "'>" & letter & "</a> "
next
%>
</body>

</html>

...........................................

SECOND PAGE
...........................................
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>
<%
DIM conn
set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb"

%>

<%
Dim letter, sql
letter = request.querystring("letter")
sql = "select FIRM_NAME from FIRMS where name like '" & letter & "%'"
set myResults = myConnection.Execute(sql)
do while not myResults.eof
' print a row of the table
<table>
firm_name: <%=myResults("firm_name")%>
</table>
myResults.movenext
loop
myResults.close
set myResults = nothing
%>
</body>

</html>

somthing does not seem right and I can not figure it out.. any suggestions?

breeze76

rokslide · #10 (**permalink**) 04-26-04, 23:02

In access the like statement uses *'s instead of %'s I think.

Look up wildcards in the help to make sure of the syntax.

breeze76 · #11 (**permalink**) 04-26-04, 23:24

Quote:

Originally Posted by rokslide

In access the like statement uses *'s instead of %'s I think.

Look up wildcards in the help to make sure of the syntax.

Ok, I have done it a little differently.. here goes.. but still need some help though..

1 - I made this an include page:

<%
DIM conn, ltr
set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb"
%>

 

<table cellpadding="1" cellspacing="0" border="0" bgcolor="#666699" width="97%" align="center"><tr><td>
<table cellpadding="2" cellspacing="0" border="0" width="100%">
<tr align="center">
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=%23">#</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=A">A</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=B">B</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=C">C</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=D">D</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=E">E</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=F">F</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=G">G</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=H">H</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=I">I</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=J">J</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=K">K</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=L">L</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=M">M</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=N">N</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=O">O</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=P">P</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=Q">Q</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=R">R</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=S">S</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=T">T</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=U">U</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=V">V</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=W">W</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=X">X</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=Y">Y</a></td>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=Z">Z</a></td>
</tr>
</table>
</td></tr></table>

2 - Then I made this my main page:

<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>



<%
DIM con
set con = Server.CreateObject("ADODB.Connection")
con.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb"
%>

<%
Dim letter, sql, myConnection
set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open "Driver = {Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb"
letter = request.querystring("letter")
sql = "select FIRM_NAME from FIRMS where name like '" & letter & "%'"
set myResults = myConnection.Execute(sql) %>

<% myResults.movefirst
do while not myResults.eof %>

<%=myResults("FIRM_NAME")%>

<% myResults.movenext
loop%>

</body>
</html>

<%
myResults.close
set myResults = nothing
%>

3 - BUT.... when I run the page I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/sortlist.asp, line 21

4 - Here is the line code:
Dim letter, sql, myConnection
set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open "Driver = {Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb" <<<--- Line 21

So... any suggestions??

Thanks for your help..

breeze76

rokslide · #12 (**permalink**) 04-27-04, 00:33

My god that is ugly.....

But to try and answer your question,.... the first connection works but the second fails?? if so replace the second connection string with the first and see what happens. The only difference I can see between the two is some extra spaces in the second one.

Please replace 1 with something like this... please... for the love of all that is good....

<%
DIM conn, ltr
set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb"
%>

 

<table cellpadding="1" cellspacing="0" border="0" bgcolor="#666699" width="97%" align="center"><tr><td>
<table cellpadding="2" cellspacing="0" border="0" width="100%">
<tr align="center">
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=%23">#</a></td>
<%
for ltr = 65 to 90
%>
<td bgcolor="#e9e9e9"><a href="http://www.psrdocs.com/sortlist.asp?ltr=<%=chr(ltr)%>"><%=chr(ltr)%></a></td>
<%
next
%>

</tr>
</table>
</td></tr></table>

breeze76 · #13 (**permalink**) 04-27-04, 00:51

That bad, eh.... sorry about that, told you I was new at this... well.. I did replace it with your code. But what I did was I removed the include file and just ran the main page and still got this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/sortlist.asp, line 21

LINE 21: myConnection.Open "Driver = {Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb"

I am at a loss... of course as you can tell...LOL

any suggestions...

breeze76

rokslide · #14 (**permalink**) 04-27-04, 00:54

the other part of my post was to try this....

myConnection.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\repository\fpdb\USERNAMES_PASSWORD S.mdb"

you have two connections on that page and it seems to only complain about the second.... eg line 21. The only difference between the two that I could see was the spaces... so I removed them....

Internet Services

Web Development

Digital Marketing

Consumer Tech