Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Web and Business Development from Cornwall - Startbar.co.uk

  • #2
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ...

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

    I think,

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #4
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    yeah

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

  • #5
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Roy Sinclair
    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
    Code:
    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().

    Code:
    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"

  • #6
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,043
    Thanks
    0
    Thanked 251 Times in 247 Posts
    The IsAlpha function returns a boolean so strLetter will contain true or false not the alphabet.
    Code:
    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"))

  • #7
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    oops.
    I just grabbed the function whammy made.

  • #8
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    help

    Code:
    <%
    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
    Web and Business Development from Cornwall - Startbar.co.uk

  • #9
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,043
    Thanks
    0
    Thanked 251 Times in 247 Posts
    Quote Originally Posted by startbar
    i tired this but it didnt work, im quite new to all this! please help, thanks
    What doesn't work? Please be specific.

  • #10
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Web and Business Development from Cornwall - Startbar.co.uk

  • #11
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    Might this be it?

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

  • #12
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ill try it out and let you know,

    thank you
    Web and Business Development from Cornwall - Startbar.co.uk

  • #13
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!
    Web and Business Development from Cornwall - Startbar.co.uk

  • #14
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by startbar
    ... 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.

  • #15
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i changed them to strSQL

    thanks, i appriciate your input.
    Web and Business Development from Cornwall - Startbar.co.uk


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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