<!-- #include file="adovbs.inc" -->
<%
' ADO constants included above.  Questions about adovbs.inc?
' See "What is Adovbs.inc and Why Do I Need It?"
'   http://www.asp101.com/articles/john/adovbs/default.asp
Dim strConnString   ' SQL Server connection string
Dim cnnDbSort       ' ADO Connection object
Dim rstDbSort       ' ADO Recordset object
Dim strSqlQuery     ' Our SQL query
Dim strSortClause   ' Sort part of our SQL query
Dim strSortQS       ' The QueryString we use to pass our sort sequence
Dim arrSortField    ' Array of fields to sort by
Dim arrSortOrder    ' "ASC" or "DESC" for each field
Dim objField        ' Used for table display
Dim blnColor        ' Alternating color indicator
Dim intSortSequence ' Rank of sorting field (looping var)
' Set our connection string
strConnString = "Provider=SQLOLEDB;Data Source=10.2.2.133;" _
	& "Initial Catalog=samples;User Id=samples;Password=password;" _
	& "Connect Timeout=15;Network Library=dbmssocn;"
'Response.Write Request.QueryString("field") & "<br />"
'Response.Write Request.QueryString("order") & "<br />"
' Retrieve sorting parameters:
' Get the field name and make sure the input is one of our field names.
arrSortField = Split(Request.QueryString("field"), ",")
arrSortOrder = Split(Request.QueryString("order"), ",")
' If no data then start out sorted Asc on SalesmanId
If UBound(arrSortField) = -1 Then
	arrSortField = Array("SalesmanId")
	arrSortOrder = Array("asc")
End If
' Loop over sort fields, making sure they contain valid values and
' building the SORT BY portion of our SQL Query
For intSortSequence = LBound(arrSortField) To UBound(arrSortField)
	Select Case LCase(Trim(arrSortField(intSortSequence)))
		Case "salesmanid", "timestamp", "amount"
			' Trim spaces
			arrSortField(intSortSequence) = Trim(arrSortField(intSortSequence))
		Case Else
			arrSortField(intSortSequence) = "SalesmanId"
	End Select
	' Check for descending o/w we default to ascending
	Select Case LCase(Trim(arrSortOrder(intSortSequence)))
		Case "desc"
			arrSortOrder(intSortSequence) = "desc"
		Case Else
			arrSortOrder(intSortSequence) = "asc"
	End Select
	' Debugging line in case you need to check something
	'Response.Write intSortSequence & " " & arrSortField(intSortSequence) & " " _
	'	& arrSortOrder(intSortSequence) & "<br />"
	' First time we don't need a comma or to check if we're already sorting by the same field 
	If strSortClause = "" Then
		strSortClause = " [" & arrSortField(intSortSequence) & "] " & arrSortOrder(intSortSequence)
	Else
		' Check for previous sort conditions using the same field and if found ignore older ones.
		' Make sure to use case insensitive matching!
		If InStr(1, strSortClause, arrSortField(intSortSequence), 1) = 0 Then
			strSortClause = strSortClause & ", [" & arrSortField(intSortSequence) & "] " & arrSortOrder(intSortSequence)
		End If
	End If
Next ' intSortSequence	
' Build our QueryString to pass sort sequence.  Again we use case insensitive matching
For intSortSequence = LBound(arrSortField) To UBound(arrSortField)
	' If field is already there we don't sort by it twice.
	If InStr(1, strSortQS, arrSortField(intSortSequence), 1) = 0 Then
		strSortQS = strSortQS & "&field=" & arrSortField(intSortSequence) & "&order=" & arrSortOrder(intSortSequence)
	End If
Next ' intSortSequence
' Build our SQL query
strSqlQuery = "SELECT * FROM [sales] ORDER BY" & strSortClause & ";"
' Echo it out:
Response.Write "<p><strong>SQL Query:</strong> " & strSqlQuery & "</p>" & vbCrLf
' Open connection
Set cnnDbSort = Server.CreateObject("ADODB.Connection")
cnnDbSort.Open strConnString
' Get recordset
Set rstDbSort = Server.CreateObject("ADODB.Recordset")
rstDbSort.Open strSqlQuery, cnnDbSort
' Build our table:
' Start the table
Response.Write "<table border=""1"" cellspacing=""0"">" & vbCrLf
' Write titles and include links to sort the table by each field
Response.Write vbTab & "<tr>" & vbCrLf
For Each objField in rstDbSort.Fields
	Response.Write vbTab & vbTab & "<td bgcolor=""#CCCCCC""><strong>" & objField.Name
	Response.Write " ("
	Response.Write "<a href=""?field=" & objField.Name & "&order=asc" & strSortQS & """>+</a>"
	Response.Write " / "
	Response.Write "<a href=""?field=" & objField.Name & "&order=desc" & strSortQS & """>-</a>"
	Response.Write ")</strong></td>" & vbCrLf
Next 'objField
Response.Write vbTab & "</tr>" & vbCrLf
' Display the data
blnColor = False
rstDbSort.MoveFirst
Do While Not rstDbSort.EOF
	'Response.Write rstDbSort.Fields(0).Value & "<br />" & vbCrLf
	Response.Write vbTab & "<tr>" & vbCrLf
	For Each objField in rstDbSort.Fields
		Response.Write vbTab & vbTab & "<td bgcolor="""
		' Decide what color to output
		If blnColor Then
			Response.Write "#CCCCFF"  ' Light blueish
		Else
			Response.Write "#FFFFFF"  ' White
		End If
		Response.Write """>" & Trim(objField.Value) & "</td>" & vbCrLf
	Next 'objField
	Response.Write vbTab & "</tr>" & vbCrLf
	' Toggle our colors
	blnColor = Not blnColor
	rstDbSort.MoveNext
Loop
' End the table
Response.Write "</table>" & vbCrLf
' Close data access objects and free variables
rstDbSort.Close
Set rstDbSort = Nothing
cnnDbSort.Close
Set cnnDbSort = Nothing
%>