List First Letter

[jrthor2]

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!

[jdavia]

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>

[jdavia]

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>

[vishu_gupt]

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

[jrthor2]

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?

[jrthor2]

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

[vishu_gupt]

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

[vishu_gupt]

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 & "*'"

[jrthor2]

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

[vishu_gupt]

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

[jrthor2]

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

[vishu_gupt]

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

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

[vishu_gupt]

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.

[jrthor2]

Nope, it still is showing all of my members, no matter what letter they click on.