<% '******************************************************* '* 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 & "" & 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 & "" & vbCrLf Next 'objField Response.Write vbTab & "" & vbCrLf ' Toggle our colors blnColor = Not blnColor rstDbSort.MoveNext Loop ' End the table Response.Write "
" & objField.Name Response.Write " (" Response.Write "+" Response.Write " / " Response.Write "-" Response.Write ")
" & Trim(objField.Value) & "
" & vbCrLf ' Close data access objects and free variables rstDbSort.Close Set rstDbSort = Nothing cnnDbSort.Close Set cnnDbSort = Nothing %>