Sorting column headings

C

carmen

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>
 
J

J. Alan Rueckgauer

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 said:
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]
 
D

dlbjr

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!
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top