...

View Full Version : sorting data into an A to Z?



startbar
08-11-2004, 04:00 PM
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

GzArIa
08-11-2004, 04:20 PM
Select * FROM table_name WHERE SUBSTRING(COMPANY_NAME, 1, 1) = 'A'

I think,

Roy Sinclair
08-11-2004, 10:29 PM
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.

GzArIa
08-11-2004, 10:33 PM
yeah Roy you're right, that's the better solution,...

Morgoth
08-11-2004, 11:03 PM
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"

glenngv
08-12-2004, 07:16 AM
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"))

Morgoth
08-13-2004, 05:32 AM
oops.
I just grabbed the function whammy made.

startbar
01-31-2005, 06:44 PM
<%
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

glenngv
02-01-2005, 02:28 AM
i tired this but it didnt work, im quite new to all this! please help, thanks
What doesn't work? Please be specific.

startbar
02-01-2005, 04:28 PM
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

Morgoth
02-02-2005, 01:21 AM
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%>

startbar
02-02-2005, 12:15 PM
ill try it out and let you know,

thank you

startbar
02-10-2005, 07:03 PM
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!

Morgoth
02-11-2005, 02:47 AM
... 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.

startbar
02-11-2005, 08:50 AM
i changed them to strSQL

thanks, i appriciate your input.

Morgoth
02-11-2005, 12:22 PM
... but it only shows the first entry of the database ...

I am having problems with my tests.

Can you give me more code?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum