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.

[vishu_gupt]

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

See your problem is you do not use your variable name properly... OK here is the full code.... But take care of the way you write the code..


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

[jrthor2]

Well, I am new to ASP, but that code did not work either. It brings back all of my members. Below is how I have it:

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

[jrthor2]

Oops, nevermind, I got it to work. Stupid mistake on my part.

Thanks for all your help!!!!!

[ruwas]

Hi

i have the same problem how to appear the first character of the name if i need only ones who's begin there name with the letter A but i am using MSaccess and i used

rs.open "Select * from table_members where cell_lname like 'R%'"

but it didn't work and i used

rs.open"select distinct(left(client_name,1)) as First_letter from client_info"

also it didn't work and error appear to me how i can do it what i can do please help me i really need it

thank you alot

[jrthor2]

Here is my code:

<%
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 "<a href=index.asp?sortby=" & objLettersRS("First_letter") & "&order=1 onFocus=if(this.blur)this.blur()>" & objLettersRS("First_letter") & "</a> "

objLettersRS.movenext
loop
%>

[ruwas]

sorry i am new in asp and i use this code but there was an error in this line

objLettersRS.Open SQL_query,conn,3,1

did i must do anything to identify the connection i identify the database i am using msaccess i don't know what is the problem please help me .....

thanks

[ruwas]

Hi all sorry for annoying really i don't know what is the problem i try and without anything

Set myConn = Server.CreateObject("ADODB.Connection")
Set myRst = Server.CreateObject("ADODB.Recordset")
myConn.Open ("connection.asp ")
myRst.Open mySQL, myConn, 2, 3

the bold text the file i but my database in db.mdb is it right and after it i add the code for the list


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

myRst.Open SQL_query,myconn,3,1
do while not objLettersRS.eof

Response.Write "<a href=index.asp?sortby=" & myRst("First_letter") & "&order=1 onFocus=if(this.blur)this.blur()>" & myRst("First_letter") & "</a> "

myRst.movenext
loop

it appear nothing what to do to solve this provlem and to have the name depend on the character

sorry again and thank you

[Ribeyed]

are you trying to do a DNS connection through ODBC?

if this is the case this is the code

set myconn server.createobject("adodb.connection")
myconn.open "databaseODBCname"