<% '******************************************************* '* 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 strSortField ' Field to sort by Dim strSortOrder ' "ASC" or "DESC" Dim objField ' Used for table display Dim blnColor ' Alternating color indicator ' 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;" ' Retrieve sorting parameters: ' Get the field name and make sure the input is one of our field names. strSortField = Request.QueryString("field") Select Case LCase(strSortField) Case "last_name", "first_name", "sales" strSortField = strSortField Case Else strSortField = "id" End Select ' Check for descending o/w we default to ascending Select Case LCase(Request.QueryString("order")) Case "desc" strSortOrder = "desc" Case Else strSortOrder = "asc" End Select ' Build our SQL query strSqlQuery = "SELECT * FROM [sample] ORDER BY [" & strSortField & "] " & strSortOrder & ";" ' 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 "
" If objField.Name = strSortField And strSortOrder = "asc" Then Response.Write "" & objField.Name & "" Else Response.Write "" & objField.Name & "" End If Response.Write "" If objField.Name = strSortField Then If LCase(strSortOrder) = "asc" Then Response.Write " " Response.Write " ↑ " Else Response.Write " " Response.Write " ↓ " End If End If Response.Write "
" & Trim(objField.Value) & "
" & vbCrLf ' Close data access objects and free variables rstDbSort.Close Set rstDbSort = Nothing cnnDbSort.Close Set cnnDbSort = Nothing %>