sorting data into an A to Z?

hi, how would i go about sorting a directory of data into an A to Z? i.e the user clicks 'A' and gets all the results of company names beginning with the letter 'A'

?

thanks ppl

Select * FROM table_name WHERE SUBSTRING(COMPANY_NAME, 1, 1) = 'A'

I think,

I'd use:

Select * FROM table_name WHERE COMPANY_NAME like 'A%'

Of course we're both assuming the question was asking about something that actually involved SQL but since the question is so vague it's not an unreasonable assumption.

yeah Roy you're right, that's the better solution,...

I'd use:

Select * FROM table_name WHERE COMPANY_NAME like 'A%'

Of course we're both assuming the question was asking about something that actually involved SQL but since the question is so vague it's not an unreasonable assumption.

You'll also want to sort by alphabetical order.

Page.asp?letter=A

strLetter = Request.QueryString("letter")

SQL = "SELECT * FROM tblTable WHERE fldCompanyName like '" & strLetter & "%' ORDER BY fldCompanyName ASC"


You also would want to have a function around Request.QueryString("letter") to make sure it's a letter from A-Z only. IsAlpha().


Function IsAlpha(str)
Dim ianRegEx
Set ianRegEx = New RegExp
ianRegEx.Pattern = "[^a-zA-Z]"
ianRegEx.Global = True
IsAlpha = (ianRegEx.Test(str) = False)
End Function

strLetter = IsAlpha(Request.QueryString("letter"))

SQL = "SELECT * FROM tblTable WHERE fldCompanyName like '" & strLetter & "%' ORDER BY fldCompanyName ASC"

The IsAlpha function returns a boolean so strLetter will contain true or false not the alphabet.

Function ValidateAlpha(str)
Dim ianRegEx
Set ianRegEx = New RegExp
ianRegEx.Pattern = "[^a-zA-Z]"
ianRegEx.Global = True
If ianRegEx.Test(str) then
ValidateAlpha = str
Else
ValidateAlpha = "A" 'default
End If
End Function

strLetter = ValidateAlpha(Request.QueryString("letter"))

oops.
I just grabbed the function whammy made.

<%
Function ValidateAlpha(str)
Dim ianRegEx
Set ianRegEx = New RegExp
ianRegEx.Pattern = "[^a-zA-Z]"
ianRegEx.Global = True
If ianRegEx.Test(str) then
ValidateAlpha = str
Else
ValidateAlpha = "A" 'default
End If
End Function

strLetter = ValidateAlpha(Request.QueryString("letter"))

SQL = "SELECT * FROM NETWORK WHERE Category like '" & strLetter & "%' ORDER BY Category ASC"

objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
IF NOT objRS.EOF THEN
%>

<%DO WHILE NOT objRS.EOF%>

<%=objRS("Category")%></a><BR>

<%
objRS.MoveNext
Loop
objRS.Close
%>


<%END IF%>

i tired this but it didnt work, im quite new to all this! please help, thanks

i tired this but it didnt work, im quite new to all this! please help, thanks
What doesn't work? Please be specific.

sorry!

well is just doesnt list the results beginning with 'a' or whatever on my database. Sometimes it lists the first entry but i dont get the list!

Thanks

Might this be it?


<%
Function ValidateAlpha(str)
Dim ianRegEx
Set ianRegEx = New RegExp
ianRegEx.Pattern = "[^a-zA-Z]"
ianRegEx.Global = True
If ianRegEx.Test(str) then
ValidateAlpha = str
Else
ValidateAlpha = "A" 'default
End If
End Function

strLetter = ValidateAlpha(Request.QueryString("letter"))

SQL = "SELECT * FROM NETWORK WHERE Category like '" & strLetter & "%' ORDER BY Category ASC"

objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
IF NOT objRS.EOF THEN
%>

<%DO WHILE NOT objRS.EOF%>

<%=objRS("Category")%></a><BR>

<%
objRS.MoveNext
Loop
objRS.Close
%>


<%END IF%>

ill try it out and let you know,

thank you

it still doesnt work!! i dont know why. I tried the code you gave and then changed around the strSQl and SQL bits but it only shows the first entry of the database or just loops the page and times out.

help!

... and then changed around the strSQl and SQL bits ...

"Changed them around"? You switched them? They should both look like "strSQL" (without the quoatationmarks).

I assume you DIDN'T, and did it the correct way, but I am just double checking.

Now, your problem is with the code and not the syntax.

Let me do some tests, I will get back to you ASAP.

i changed them to strSQL

thanks, i appriciate your input.

... but it only shows the first entry of the database ...

I am having problems with my tests.

Can you give me more code?