We help IT Professionals succeed at work.

Data sort issue

Hello, can someone help me to understand how this could be happening? I am working on someone else s code and cant seem to figure this out.

I am pulling records from a SQL DD into a classic ASP report. The column headers sort the data by running another query and using ORDER BY ASC or DESC. The attached image is of the report sorted by ActivityNumber ASC. All the other columns on the report sort properly.

TIA,
Andrew

 ASC Sort
<%@ Language=VBScript %>
<% response.expires = 0 %>
<!-- #include file="ASPConnection.inc" -->

<%

Server.ScriptTimeout = 3000

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConn

Set oConn2 = Server.CreateObject("ADODB.Connection")
oConn2.Open strConn2

'if Request.QueryString("sort") <> "" then
'	Session("sQuery") = ""
'end if

if Session("sQuery") = "" then

	sSQL = "select b.orgTrayQty,b.StakeOwner, b.*, c.* from sql01.Directory.dbo._Employees a, PTR b, Location c where "

	if request("txtInFrom") <> "" or request("txtInTo") <> "" then
		sSQL = sSQL & "b.DateCreated between '" & request("txtInFrom") & "' and '" & request("txtInTo") & "' and "
	end if

	if request("txtOutFrom") <> "" or request("txtOutTo") <> "" then
		sSQL = sSQL & "b.CheckOutDate between '" & request("txtOutFrom") & "' and '" & request("txtOutTo") & "' and "
	end if

	if request("cboEmp") <> "0" then
		sSQL = sSQL & "b.EmpID = " & request("cboEmp") & " and "
	end if

	if request("cboDept") <> "0" then
		sSQL = sSQL & "b.DeptID = " & request("cboDept") & " and "
	end if

	if request("cboExt") <> "0" then
		sSQL = sSQL & "b.ExternalID = " & request("cboExt") & " and "
	end if

	if request("cboLoc") <> "0" then
		sSQL = sSQL & "b.LocCurr = " & request("cboLoc") & " and "
	end if

	if request("cboStatus") <> "0" then
		if request("cboStatus") = "False" then
			sSQL = sSQL & "b.CheckedOut = 0 and "
		else
			sSQL = sSQL & "b.CheckedOut = 1 and "
		end if
	end if

	sSQL = sSQL & "a.EmpID =* b.EmpID and c.LID = b.LocCurr"
	
'Response.Write sSQL

else
	sSQL = Session("sQuery")
end if

Session("sQuery") = sSQL

if Request.QueryString("order") = "ASC" then
	sOrder = "ASC"
else
	sOrder = "DESC"
end if

if Request.QueryString("sort") = "" then
	sSort = "b.ID"
else
	sSort = Request.QueryString("sort")
end if

sSQL = sSQL & " order by " & sSort & " " & sOrder

'Response.Write sSQL
set rs = oConn.Execute(sSQL)

i = 1

if sOrder = "ASC" then
	sOrder = "DESC"
else
	sOrder = "ASC"
end if

Function GetExternalactivityCode (ExternalId)  
Set oConnExt = Server.CreateObject("ADODB.Connection")
sSql = "select activityNum from external where EID = " &  ExternalId

oConnExt.Open strConn
Set objRS = oConnExt.Execute(sSQL)
   
    	if not objRS.bof and not objRS.eof then 
			 do until objRS.eof 
                  If objRS("ActivityNum") > 0 then 
                    GetExternalactivityCode = CStr(objRS("ActivityNum"))
                  Else
                    GetExternalactivityCode = "0"
                  End if
                  objRS.movenext
             loop   
        Else
            GetExternalactivityCode = 0   
        End If 
          
oConnExt.close
End Function

%>

<%'= sSQL %>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<SCRIPT ID=clientEventHandlersJS LANGUAGE=javascript>
<!--

function window_onload() 
{
}

function doMove()
{
window.document.frmGo.action="MoveMultipleStep1.asp";
window.document.frmGo.submit();
}

//-->
</SCRIPT>
</HEAD>
<BODY LANGUAGE=javascript onload="return window_onload()">
<form action="CheckOutMultiple.asp" method="POST" name="frmGo">
<table width="100%" cellpadding="4" cellspacing="0">
		<tr>
			<td colspan="3" width="100%" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="3" color="#663300"><b>PTR Search Results</b></font></td>
		</tr>
		<tr>
			<td width="1%" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b></b>&nbsp;</font></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=ID"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>ID</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=LName"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Owner</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=ActivityCode"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Activity</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=Description"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Location</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=Species"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Species</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=TrayQty"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Qty</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=OrgTrayQty"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Orginal Qty</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=CTID"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Container</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=DateCreated"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Date Created</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=CheckOutDate"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Check-Out Date</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=CheckedOut"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1" color="#663300"><b>Checked In/Out</b>&nbsp;</font></a></td>
			<td width="90%"></td>

		</tr>
		<% sBGColor = "lightgrey" %>
		<% if not rs.bof and not rs.eof then %>
			<% do until rs.eof %>

			<tr>
			
			 <%'if rs("ActivityCode")= "0" then response.Write  GetExternalactivityCode(rs("ExternalID")) Else response.Write  rs("ActivityCode") end if %>
			 <%'response.end %>
				<% if rs("TrayQty") > 0 or rs("CheckedOut") = 1 then %>
				    <td width="1%" bgcolor="<%= sBGColor %>" nowrap><input type="checkbox" name="chkClose<%=i %>" value="<%= rs("ID") %>"></td>
				<% else %>
					<td width="1%" bgcolor="<%= sBGColor %>" nowrap></td>
				<% end if %>
				
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><a href="ViewPTR.asp?PTR=<%= rs("ID") %>"><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("ID") %></font></a></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><a href="ViewPTR.asp?PTR=<%= rs("ID") %>"><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("StakeOwner") %></font></a></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%if rs("ActivityCode")= "0" then response.Write GetExternalactivityCode(rs("ExternalID")) Else response.Write CStr(rs("ActivityCode")) end if%></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("Description") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("Species") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("TrayQty") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("OrgTrayQty") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("CTID") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= DateValue(rs("DateCreated")) %></font></td>			
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><% if rs("CheckOutDate") <> "1/1/1900" then Response.write DateValue(rs("CheckOutDate")) else Response.Write "..." end if %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><% if rs("CheckedOut") = True then Response.Write "Out" else Response.Write "In" end if %></font></td>
				<td width="910" bgcolor="<%= sBGColor %>" ></td>
			</tr>
			<% i = i + 1 %>
			<% rs.movenext %>
			<% Response.Flush() %>
			<%
			if sBGColor = "white" then
				sBGColor = "lightgrey"
			else
				sBGColor = "white"
			end if
			%>
			
			<% loop %>
			<tr>
				<td width="100%" colspan="9"><input type="hidden" name="txtCount" value="<%= i %>"></td>
			</tr>
			<tr>
				<td width="100%" colspan="9">
				<input type="submit" name="cmdClose" value="Check-out Selected">
				<input type="submit" name="cmdMove" value="Move Selected" onClick="javascript:doMove();">
				</td>

			</tr>
		<% else %>
			<tr>
				<td width="100%" colspan="9"><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300">No records found...</font></td>
			</tr>		
		<% end if %>
	
		

</table>
</form>

</BODY>
</HTML>

Open in new window

Comment
Watch Question

Author

Commented:
ActivityCode is of type VARCHAR(50) in the SQL SB.
If you are 100% certain that the field will contain ONLY numeric integer values, you can change this line of code:


sSQL = sSQL & " order by " & sSort & " " & sOrder

to read:

sSQL = sSQL & " order by CONVERT(INT, " & sSort & ") " & sOrder


BUT I would also recommend rewriting significant portions of this code, as it appears vulnerable to SQL injection attacks.
Actually, this makes more sense in the overall context:

If (sSort = 'ActivityNumber') Then
    sSQL = sSQL & " order by CONVERT(INT, " & sSort & ") " & sOrder
Else
    sSQL = sSQL & " order by " & sSort & " " & sOrder
End If

Author

Commented:
Thanks. I will give that a shot. I am well aware of the vulnerabilities to SQL injection, but need a quick fix for this for now,  as I am tasked to rewrite this in ASP.Net very soon.

Author

Commented:
After applying your recommendation, something is still incorrect...

 SS
<%@ Language=VBScript %>
<% response.expires = 0 %>
<!-- #include file="ASPConnection.inc" -->

<%

Server.ScriptTimeout = 3000

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConn

Set oConn2 = Server.CreateObject("ADODB.Connection")
oConn2.Open strConn2

'if Request.QueryString("sort") <> "" then
'	Session("sQuery") = ""
'end if

if Session("sQuery") = "" then

	sSQL = "select b.orgTrayQty,b.StakeOwner, b.*, c.* from sql01.Directory.dbo._Employees a, PTR b, Location c where "

	if request("txtInFrom") <> "" or request("txtInTo") <> "" then
		sSQL = sSQL & "b.DateCreated between '" & request("txtInFrom") & "' and '" & request("txtInTo") & "' and "
	end if

	if request("txtOutFrom") <> "" or request("txtOutTo") <> "" then
		sSQL = sSQL & "b.CheckOutDate between '" & request("txtOutFrom") & "' and '" & request("txtOutTo") & "' and "
	end if

	if request("cboEmp") <> "0" then
		sSQL = sSQL & "b.EmpID = " & request("cboEmp") & " and "
	end if

	if request("cboDept") <> "0" then
		sSQL = sSQL & "b.DeptID = " & request("cboDept") & " and "
	end if

	if request("cboExt") <> "0" then
		sSQL = sSQL & "b.ExternalID = " & request("cboExt") & " and "
	end if

	if request("cboLoc") <> "0" then
		sSQL = sSQL & "b.LocCurr = " & request("cboLoc") & " and "
	end if

	if request("cboStatus") <> "0" then
		if request("cboStatus") = "False" then
			sSQL = sSQL & "b.CheckedOut = 0 and "
		else
			sSQL = sSQL & "b.CheckedOut = 1 and "
		end if
	end if

	sSQL = sSQL & "a.EmpID =* b.EmpID and c.LID = b.LocCurr"
	
'Response.Write sSQL

else
	sSQL = Session("sQuery")
end if

Session("sQuery") = sSQL

if Request.QueryString("order") = "ASC" then
	sOrder = "ASC"
else
	sOrder = "DESC"
end if

if Request.QueryString("sort") = "" then
	sSort = "b.ID"
else
	sSort = Request.QueryString("sort")
end if

If (sSort = "ActivityCode") Then
    sSQL = sSQL & " order by CONVERT(INT, " & sSort & ") " & sOrder
    response.write "ActivityCode"
Else
    sSQL = sSQL & " order by " & sSort & " " & sOrder
End If

'sSQL = sSQL & " order by " & sSort & " " & sOrder

'Response.Write sSQL
set rs = oConn.Execute(sSQL)

i = 1

if sOrder = "ASC" then
	sOrder = "DESC"
else
	sOrder = "ASC"
end if

Function GetExternalactivityCode (ExternalId)  
Set oConnExt = Server.CreateObject("ADODB.Connection")
sSql = "select activityNum from external where EID = " &  ExternalId

oConnExt.Open strConn
Set objRS = oConnExt.Execute(sSQL)
   
    	if not objRS.bof and not objRS.eof then 
			 do until objRS.eof 
                  If objRS("ActivityNum") > 0 then 
                    GetExternalactivityCode = objRS("ActivityNum")
                  Else
                    GetExternalactivityCode = "0"
                  End if
                  objRS.movenext
             loop   
        Else
            GetExternalactivityCode = 0   
        End If 
          
oConnExt.close
End Function

%>

<%'= sSQL %>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<SCRIPT ID=clientEventHandlersJS LANGUAGE=javascript>
<!--

function window_onload() 
{
}

function doMove()
{
window.document.frmGo.action="MoveMultipleStep1.asp";
window.document.frmGo.submit();
}

//-->
</SCRIPT>
</HEAD>
<BODY LANGUAGE=javascript onload="return window_onload()">
<form action="CheckOutMultiple.asp" method="POST" name="frmGo">
<table width="100%" cellpadding="4" cellspacing="0">
		<tr>
			<td colspan="3" width="100%" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="3" color="#663300"><b>PTR Search Results</b></font></td>
		</tr>
		<tr>
			<td width="1%" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b></b>&nbsp;</font></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=ID"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>ID</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=LName"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Owner</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=ActivityCode"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Activity</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=Description"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Location</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=Species"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Species</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=TrayQty"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Qty</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=OrgTrayQty"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Orginal Qty</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=CTID"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Container</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=DateCreated"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Date Created</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=CheckOutDate"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1"><b>Check-Out Date</b>&nbsp;</font></a></td>
			<td width="1%" nowrap><a href="ListPTRStep2.asp?order=<%= sOrder %>&sort=CheckedOut"><font face="Verdana, Arial, Helvetica, sans-serif" color="black" size="1" color="#663300"><b>Checked In/Out</b>&nbsp;</font></a></td>
			<td width="90%"></td>

		</tr>
		<% sBGColor = "lightgrey" %>
		<% if not rs.bof and not rs.eof then %>
			<% do until rs.eof %>

			<tr>
			
			 <%'if rs("ActivityCode")= "0" then response.Write  GetExternalactivityCode(rs("ExternalID")) Else response.Write  rs("ActivityCode") end if %>
			 <%'response.end %>
				<% if rs("TrayQty") > 0 or rs("CheckedOut") = 1 then %>
				    <td width="1%" bgcolor="<%= sBGColor %>" nowrap><input type="checkbox" name="chkClose<%=i %>" value="<%= rs("ID") %>"></td>
				<% else %>
					<td width="1%" bgcolor="<%= sBGColor %>" nowrap></td>
				<% end if %>
				
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><a href="ViewPTR.asp?PTR=<%= rs("ID") %>"><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("ID") %></font></a></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><a href="ViewPTR.asp?PTR=<%= rs("ID") %>"><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("StakeOwner") %></font></a></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%if rs("ActivityCode")= "0" then response.Write GetExternalactivityCode(rs("ExternalID")) Else response.Write rs("ActivityCode") end if%></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("Description") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("Species") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("TrayQty") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("OrgTrayQty") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= rs("CTID") %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><%= DateValue(rs("DateCreated")) %></font></td>			
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><% if rs("CheckOutDate") <> "1/1/1900" then Response.write DateValue(rs("CheckOutDate")) else Response.Write "..." end if %></font></td>
				<td width="1%" bgcolor="<%= sBGColor %>" nowrap align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300"><% if rs("CheckedOut") = True then Response.Write "Out" else Response.Write "In" end if %></font></td>
				<td width="910" bgcolor="<%= sBGColor %>" ></td>
			</tr>
			<% i = i + 1 %>
			<% rs.movenext %>
			<% Response.Flush() %>
			<%
			if sBGColor = "white" then
				sBGColor = "lightgrey"
			else
				sBGColor = "white"
			end if
			%>
			
			<% loop %>
			<tr>
				<td width="100%" colspan="9"><input type="hidden" name="txtCount" value="<%= i %>"></td>
			</tr>
			<tr>
				<td width="100%" colspan="9">
				<input type="submit" name="cmdClose" value="Check-out Selected">
				<input type="submit" name="cmdMove" value="Move Selected" onClick="javascript:doMove();">
				</td>

			</tr>
		<% else %>
			<tr>
				<td width="100%" colspan="9"><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#663300">No records found...</font></td>
			</tr>		
		<% end if %>
	
		

</table>
</form>

</BODY>
</HTML>

Open in new window

Author

Commented:
I have found that some of the values are indeed alphanumeric. So, converting to INT will not work for this anyways it appears.
Ok. Well, here's alternative SQL syntax for sometimes numeric and sometimes alphabetical sorting (it sorts numeric columns by padding them with zeroes in database memory during sorting operation, so they can still sort properly with alphabetical values). So, when sorted, you would have numeric fields sorted by number, followed by alphanumeric fields sorted alphabetically.

sSQL = sSQL & String.Format("order by case IsNumeric({0}) when 1 then Replicate(Char(0), 100 - Len({1})) + {2} else {3} end {4}", sSort, sSort, sSort, sSort, sOrder)

Author

Commented:
What about using a client side table sort function like JQuery? Would that be a better solution?
Well, it would be more conservative of database resources, since you wouldn't have to re-query the DB and shuffle a bunch of data across the network in order to simply re-order it. But, I think it would require more work, as you would have to put the data into a structure that JQuery can work with.

Maybe there's a UI extension that will automatically parse the contents of a table into a data set... that would be useful. Then you'd have a lot more control over how the data is sorted, too.