<%
'*******************************************************
'* ASP 101 Sample Code - http://www.asp101.com/ *
'* *
'* This code is made available as a service to our *
'* visitors and is provided strictly for the *
'* purpose of illustration. *
'* *
'* http://www.asp101.com/samples/license.asp *
'* *
'* Please direct all inquiries to webmaster@asp101.com *
'*******************************************************
%>
<%
' 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") & "
"
'Response.Write Request.QueryString("order") & "
"
' 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) & "
"
' 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 "SQL Query: " & strSqlQuery & "
" & 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 "" & vbCrLf
' Write titles and include links to sort the table by each field
Response.Write vbTab & "" & vbCrLf
For Each objField in rstDbSort.Fields
Response.Write vbTab & vbTab & "| " & objField.Name
Response.Write " ("
Response.Write "+"
Response.Write " / "
Response.Write "-"
Response.Write ") | " & vbCrLf
Next 'objField
Response.Write vbTab & "
" & vbCrLf
' Display the data
blnColor = False
rstDbSort.MoveFirst
Do While Not rstDbSort.EOF
'Response.Write rstDbSort.Fields(0).Value & "
" & vbCrLf
Response.Write vbTab & "" & vbCrLf
For Each objField in rstDbSort.Fields
Response.Write vbTab & vbTab & "| " & Trim(objField.Value) & " | " & vbCrLf
Next 'objField
Response.Write vbTab & "
" & vbCrLf
' Toggle our colors
blnColor = Not blnColor
rstDbSort.MoveNext
Loop
' End the table
Response.Write "
" & vbCrLf
' Close data access objects and free variables
rstDbSort.Close
Set rstDbSort = Nothing
cnnDbSort.Close
Set cnnDbSort = Nothing
%>