Sorting column headings

Discussion in 'ASP General' started by carmen, Jul 21, 2004.

  1. carmen

    carmen Guest

    I'm using the code below to display the results in rows with the column
    header as hyperlinks that will sort the rows by colum heading. Is there a
    way to have the results sorted (as default) in reverse order so that the
    most recent are at the top of the page also when the user selects the Open
    Date or Status column headings, sort the data in reverse order .
    Code is below.
    Thanks

    <%
    ppl_ctr=0
    dim mthd
    ' Set up SELECT Statement (Order by if user selected)
    If IsEmpty(Request.QueryString("Order")) Then
    SQLStatement = "Select * From calls Order by callID"
    strORDER = "Ordered by ID"
    Else
    SQLStatement = "SELECT * FROM calls ORDER BY " &
    Request.QueryString("Order") & ", callID"
    If (Request.QueryString("Order") = "callID") Then
    strORDER = "Ordered by ID"
    Else
    strORDER = "Ordered by " & Request.QueryString("Order")
    End If
    End If
    %>
    <div align="center">
    <center>
    <table border="0" cellpadding="0" cellspacing="0" width="100%"
    style="border-collapse: collapse" bordercolor="#111111">
    <tr>
    <td align="center" width="100%">
    <font size="5">Support Calls</font>
    </td>
    </tr>
    <tr>
    <td align="center" width="100%">
    <a href="default.asp">Return to Support Call Home Page</a></td>
    </tr>
    </tr>
    <tr>
    <td align="center" width="100%">
    &nbsp;</td>
    </tr>
    </table>
    </center>
    </div>

    <% Set RScalls = dbConnection.Execute(SQLStatement) %>

    <table align="center" border="1" cellspacing="0" cellpadding="5">
    <tr bgcolor="#B1C1D1">
    <td rowspan="2"><font size="-1"><b><a
    href="call_list.asp?Order=callID">ID</a></b><br></font></td>
    <td width="50%" rowspan="2"><font size="-1"><b><a
    href="call_list.asp?Order=Title">Title</a></b></font></td>
    <td rowspan="2"><font size="-1"><b><a
    href="call_list.asp?Order=OpenDate">Open Date</a></b></font></td>
    <td rowspan="2"><font size="-1"><b><a
    href="call_list.asp?Order=RecvBy">Logged By</a></b></font></td>
    <td rowspan="2"><font size="-1"><b><a
    href="call_list.asp?Order=status">Status</a></b></font></td>
    <td rowspan="2"><font size="-1"><b><a
    href="call_list.asp?Order=state">State</a></b></font></td>
    <td colspan="3">
    <p align="center"><font size="-1"><b>Severity</a></b></font></td>
    </tr>
    <tr bgcolor="#B1C1D1">
    <td><font size="-1"><b><a href="call_list.asp?Order=Severity1 DESC">Sev
    1</a></b></font></td>
    <td><font size="-1"><b><a href="call_list.asp?Order=Severity2 DESC">Sev
    2</a></b></font></td>
    <td><font size="-1"><b><a href="call_list.asp?Order=Severity3 DESC">Sev
    3</a></b></font></td>
    </tr>
    <% Do While Not RScalls.EOF
    callscounter=callscounter+1 %>
    <tr>
    <td>
    <small>
    <% If (Request.QueryString("Order") <> "") Then %>

    <form method="Get" action="call_call_list.asp" id=form24 name=form24>
    <input type="hidden" name="Order"
    value="<%=Request.QueryString("Order") %>">
    <% End If %>

    <a href="update.asp?submit=Update&callID=<%=RScalls("callID")%>">
    <%=RScalls("callID")%>
    </a>
    </td>
    <td width="45%"><small><%=RScalls("title") %></small>&nbsp;</td>
    <td><small><%=RScalls("OpenDate")%></small>&nbsp;</td>
    <td><small><%=RScalls("RecvBy")%></small>&nbsp;</td>
    <td><small><%=RScalls("status")%></small>&nbsp;</td>
    <td><small><%=RScalls("state")%></small>&nbsp;</td>
    <td><small><%=RScalls("severity1")%></small>&nbsp;</td>
    <td><small><%=RScalls("severity2")%></small>&nbsp;</td>
    <td><small><%=RScalls("severity3")%></small>&nbsp;</td>
    </tr>
    <% RScalls.movenext
    Loop
    %>
    <% If callscounter=0 Then %>
    <%= "No Calls were found at this time." %>
    <% End If %>
    </table>
    carmen, Jul 21, 2004
    #1
    1. Advertising

  2. Carmen --

    You can hard-code " DESC " to your SQLStatement, or make it variable like
    your Querystring("Order"):

    SQLStatement = "Select * From calls Order by callID DESC"
    SQLStatement = "SELECT * FROM calls ORDER BY " &
    Request.QueryString("Order") & ", callID DESC"

    You can also sort different columns ASC (ascending) or DESC (descending),
    for instance:

    SELECT callID, customerName FROM calls ORDER BY callID DESC, customerName
    ASC

    would give you the calls in newer to older order, but then sort them by the
    customer name column.

    Do be careful, however, when using ORDER BY and make sure you avoid using
    column collections that don't have a corresponding or covering index if the
    dataset being sorted is large, or you're likely to impair performance (and
    possibly elicit the wrath of your DBA).

    Alan

    =====

    "carmen" <carmen@iyahoo_Spam.com> wrote in message
    news:...
    > I'm using the code below to display the results in rows with the column
    > header as hyperlinks that will sort the rows by colum heading. Is there a
    > way to have the results sorted (as default) in reverse order so that the
    > most recent are at the top of the page also when the user selects the Open
    > Date or Status column headings, sort the data in reverse order .
    > Code is below.
    > Thanks
    >
    > <%
    > ppl_ctr=0
    > dim mthd
    > ' Set up SELECT Statement (Order by if user selected)
    > If IsEmpty(Request.QueryString("Order")) Then
    > SQLStatement = "Select * From calls Order by callID"
    > strORDER = "Ordered by ID"
    > Else
    > SQLStatement = "SELECT * FROM calls ORDER BY " &
    > Request.QueryString("Order") & ", callID"
    > If (Request.QueryString("Order") = "callID") Then
    > strORDER = "Ordered by ID"
    > Else
    > strORDER = "Ordered by " & Request.QueryString("Order")
    > End If
    > End If
    > %>

    [snip]
    J. Alan Rueckgauer, Jul 21, 2004
    #2
    1. Advertising

  3. carmen

    dlbjr Guest

    Get data once.
    Place in XML Data Island on Client and sort with JavaScript.
    Why hit database and reload the data each time?

    dlbjr
    Pleading sagacious indoctrination!
    dlbjr, Jul 21, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tracey
    Replies:
    0
    Views:
    363
    Tracey
    Jul 23, 2003
  2. Seth Williams

    freeze column headings

    Seth Williams, Oct 17, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    434
  3. S. Hussain Akbar

    Repeating column headings

    S. Hussain Akbar, Jan 5, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    113
    S. Hussain Akbar
    Jan 5, 2004
  4. Greg Phillips

    Datagrid render column headings. ASPX Page Model. Events

    Greg Phillips, Oct 11, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    179
    Greg Phillips
    Oct 11, 2004
  5. Steven Van Dyke

    HOwTO: Set column headings based on dynamic DataView?

    Steven Van Dyke, Nov 12, 2003, in forum: ASP .Net Mobile
    Replies:
    1
    Views:
    174
    Steven Van Dyke
    Nov 12, 2003
Loading...

Share This Page