Results 1 to 14 of 14

Thread: Sort DB help

  1. #1
    Join Date
    Apr 2004
    Posts
    75

    Unanswered: Sort DB help

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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...

  3. #3
    Join Date
    Apr 2004
    Posts
    75
    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

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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??

  5. #5
    Join Date
    Apr 2004
    Posts
    75
    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
    Last edited by breeze76; 04-22-04 at 15:04.

  6. #6
    Join Date
    Apr 2004
    Posts
    75
    Another question??

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

    breeze76

  7. #7
    Join Date
    Apr 2004
    Posts
    75
    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

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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??

  9. #9
    Join Date
    Apr 2004
    Posts
    75
    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><font>
    <p><b>firm_name:</b>&nbsp;<%=myResults("firm_name")%></p></font>
    </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

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    In access the like statement uses *'s instead of %'s I think.

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

  11. #11
    Join Date
    Apr 2004
    Posts
    75
    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"
    %>


    <font face="verdana,arial,helvetica" size="1" ><br></font>

    <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"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=%23">#</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=A">A</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=B">B</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=C">C</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=D">D</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=E">E</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=F">F</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=G">G</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=H">H</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=I">I</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=J">J</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=K">K</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=L">L</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=M">M</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=N">N</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=O">O</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=P">P</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=Q">Q</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=R">R</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=S">S</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=T">T</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=U">U</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=V">V</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=W">W</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=X">X</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=Y">Y</a></font></td>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=Z">Z</a></font></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>

    <!-- #INCLUDE FILE="alphabets.asp" -->

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

    <p><%=myResults("FIRM_NAME")%></p>

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

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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"
    %>


    <font face="verdana,arial,helvetica" size="1" ><br></font>

    <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"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=%23">#</a></font></td>
    <%
    for ltr = 65 to 90
    %>
    <td bgcolor="#e9e9e9"><font face="verdana, arial, helvetica" size="2" ><a href="http://www.psrdocs.com/sortlist.asp?ltr=<%=chr(ltr)%>"><%=chr(ltr)%></a></font></td>
    <%
    next
    %>

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

  13. #13
    Join Date
    Apr 2004
    Posts
    75
    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

  14. #14
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •