www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: List First Letter

  1. #1
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898

    List First Letter

    I have a new member database where I would like to display across the top of the page the first letter of the last name for those members in the database so if you click on the letter "R" you get all members whose last name begins with the letter R. How can I do this. I am totally new to ASP.

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Location
    Lake Havasu City, Arizona
    Posts
    527
    In each section named, Section A, Section B, Section C, or however they are named. Make an name tag like this:
    <a name="A">SectionA</a> that surrounds that text,
    Make the linking tagto it like this:
    <a href="#SectionA">Section A</a>

  3. #3
    Join Date
    Dec 2002
    Location
    Lake Havasu City, Arizona
    Posts
    527
    I forgot to mention that, The W3C in the new XHTML and XML standards, are calling for the use of a new attribute for the <a> tag called id to replace the name attribute.
    So if you want you can comply with the new standard like this:

    <a name=SectionA" id="SectionA" Section A</a>

  4. #4
    Join Date
    Dec 2002
    Posts
    54
    HI,
    Like jdavia wrote you can use named hyperlinks to get the list of memebers if you have listings of all the members on the same page.
    But if you want that on clicking on "R" or any other letter your ASP page should get the members name from the database then Make your hyperlinks pointing to ASP page and on that page run the query
    Select * from table_members where cell_lname like 'R%'
    Last edited by vishu_gupt; 01-10-2003 at 08:15 AM.

  5. #5
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    Ok, that's what I am loking for, to query the database based on the letter they clicked on. Now, How can I only list the letters at the top of the page for only the Letters we have? For instance, If we don't have any Last Names that begin with "R", then I don't want to show the letter R at the top of the page. How can I do that?

  6. #6
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    When using the following query:

    SQLstmt = "SELECT * from New_Members where LastName is like '" & sortby & "%'"

    I get the following error:

    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression 'LastName is like 'D%''.

  7. #7
    Join Date
    Dec 2002
    Posts
    54
    HI,

    For that you can use following query:
    select distinct(left(Isnull(cell_Lname,''),1)) as First_letter from Table_Members order by First_letter
    Hope you know the functions used in this query. This is tested in SQL server 7.0 and SQL server 2000.

    Bye
    Vishal Gupta

  8. #8
    Join Date
    Dec 2002
    Posts
    54
    HI,
    If you are using SQL server as database then your query is not correct. Write this way
    SQLstmt = "SELECT * from New_Members where LastName like '" & sortby & "%'"
    If you are using MSAccess then last query will be

    select distinct(left(client_name,1)) as First_letter from client_info


    and first query will be

    SQLstmt = "SELECT * from New_Members where LastName like '" & sortby & "*'"
    Vishal Gupta

  9. #9
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    I am using MSAccess. I used your new codes, but I am not getting any results. If I try to get all members with the letter R, it brings back all my members, not just the letter R. Also, for getting the letters to display at the top of the page, it is not displaying any letters. Here is my code for that.

    <%
    Set objLettersRS = Server.CreateObject("adodb.recordset")

    SQL_query = "select distinct(left(LastName,1)) as First_letter from New_Members "

    objLettersRS.Open SQL_query,conn,3,1

    Response.Write First_Letter
    %>

  10. #10
    Join Date
    Dec 2002
    Posts
    54
    Can you write your both queries and sample data in the databse. My end both queries are working and see your code

    SQL_query = "select distinct(left(LastName,1)) as First_letter from New_Members "

    objLettersRS.Open SQL_query,conn,3,1

    Response.Write First_Letter

    Can cause an error use this one

    SQL_query = "select distinct(left(LastName,1)) as First_letter from New_Members "

    objLettersRS.Open SQL_query,conn,3,1
    do while not objLettersRS.eof
    Response.Write First_Letter & " "
    objLettersRS.movenext
    loop
    Vishal Gupta

  11. #11
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    Hmm, well, here is my code now for the letters, and still, I don't get any results:

    <%
    Set objLettersRS = Server.CreateObject("adodb.recordset")

    SQL_query = "select distinct(left(LastName,1)) as First_letter from New_Members "

    objLettersRS.Open SQL_query,conn,3,1
    do while not objLettersRS.eof
    Response.Write First_Letter & " "
    objLettersRS.movenext
    loop
    %>

  12. #12
    Join Date
    Dec 2002
    Posts
    54
    Use this

    <%
    Set objLettersRS = Server.CreateObject("adodb.recordset")

    SQL_query = "select distinct(left(LastName,1)) as First_letter from New_Members "

    objLettersRS.Open SQL_query,conn,3,1
    do while not objLettersRS.eof
    Response.Write objLettersRS("First_letter") & " "
    objLettersRS.movenext
    loop
    %>
    Vishal Gupta

  13. #13
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    Yeah, thanks a lot for all your help, that finally worked!!!

    Now, i am still having a problem sorting by that letter. When I click the letter, my link looks like this (index.asp?sortby=Lettertheyclickedon) and my code looks like this:

    If Request.QueryString("sortby") Then
    SQLstmt = "SELECT * from New_Members where LastName like '" & sortby & "*'"
    End If

    It is bringing back all my members, not just the ones that start with the letter they clicked on.

    Thanks again for all your help.
    Last edited by jrthor2; 01-10-2003 at 09:06 AM.

  14. #14
    Join Date
    Dec 2002
    Posts
    54
    Originally posted by jrthor2
    Yeah, thanks a lot for all your help, that finally worked!!!

    Now, i am still having a problem sorting by that letter. When I click the letter, my link looks like this (index.asp?sortby=Lettertheyclickedon) and my code looks like this:

    If Request.QueryString("sortby") Then
    SQLstmt = "SELECT * from New_Members where LastName like '" & sortby & "%'"
    End If

    It is bringing back all my members, not just the ones that start with the letter they clicked on.

    Thanks again for all your help.
    Use this
    If trim(Request.QueryString("sortby"))<>"" Then
    SQLstmt = "SELECT * from New_Members where LastName like '" & sortby & "*'"
    End If


    See my post related to the MS access database queries.
    Message me if it works now.
    Vishal Gupta

  15. #15
    Join Date
    Jan 2003
    Location
    PA
    Posts
    1,898
    Nope, it still is showing all of my members, no matter what letter they click on.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles