<%@ Page Language="VB" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<script language="VB" runat="server">
	Sub Page_Load(sender As Object, e As EventArgs)
		Dim arrSortField  As Array   ' Array of fields to sort by
		Dim arrSortOrder  As Array   ' "ASC" or "DESC" for each field
		Dim strSortClause As String  ' Sort part of our SQL query
		Dim strSortQS     As String  ' The QueryString we use to pass our sort sequence
		Dim strSQLQuery   As String  ' Full SQL Query
		Dim myConnection  As SqlConnection  ' Data access objects
		Dim myDataAdapter As SqlDataAdapter
		Dim myDataSet     As DataSet
		Dim intSortSequence As Integer ' Rank of sorting field (looping var)
		' 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 arrSortField(0) = Nothing Then
			arrSortField(0) = "SalesmanId"
			arrSortOrder(0) = "asc"
		End If
		' Loop over sort fields, making sure they contain valid values and
		' building the SORT BY portion of our SQL Query and the Querystring
		' to pass to subsequemt pages.
		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 />")
			' Build the SORT BY clause
			' 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
			' Build our QueryString.  Again we use case insensitive matching.
			' 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 so people can see it.
		lblSQLQuery.Text = "<strong>SQL Query:</strong> " & Server.HTMLEncode(strSQLQuery)
		' Database stuff:
		myConnection = New SqlConnection("Data Source=10.2.2.133;" _
		   & "Initial Catalog=samples;User Id=samples;Password=password;" _
		   & "Connect Timeout=15;Network Library=dbmssocn;")
		myDataAdapter = New SqlDataAdapter(strSQLQuery, myConnection)
		myDataSet = new Dataset()
	    myDataAdapter.Fill(myDataSet, "sales")
		' Set headings so they pass sort order when clicked.
		DBSortDataGrid.Columns(0).HeaderText = "SalesmanId (<a href='?field=SalesmanId&order=asc" & strSortQS & "'>+</a> / <a href='?field=SalesmanId&order=desc" & strSortQS & "'>-</a>)"
		DBSortDataGrid.Columns(1).HeaderText = "Timestamp  (<a href='?field=Timestamp&order=asc"  & strSortQS & "'>+</a> / <a href='?field=Timestamp&order=desc"  & strSortQS & "'>-</a>)"
		DBSortDataGrid.Columns(2).HeaderText = "Amount     (<a href='?field=Amount&order=asc"     & strSortQS & "'>+</a> / <a href='?field=Amount&order=desc"     & strSortQS & "'>-</a>)"
		' Databind
		DBSortDataGrid.DataSource = myDataSet
		DBSortDataGrid.Databind()
	End Sub
</script>
<html>
<head>
<title>ASP.NET Database Multi-Sort Sample</title>
</head>
<body>
<form EnableViewState="false" runat="server">
<asp:Label id="lblSQLQuery" runat="server" />
<asp:DataGrid id="DBSortDataGrid" runat="server" 
	Border      = 1
	CellSpacing = 1
	HeaderStyle-BackColor = "#CCCCCC"
    HeaderStyle-ForeColor = "#000000"
	HeaderStyle-Font-Bold = "True"
    ItemStyle-BackColor   = "#FFFFFF"
	AlternatingItemStyle-BackColor = "#CCCCFF"
    AutoGenerateColumns = "False"
	>
	<Columns>
		<asp:BoundColumn DataField="SalesmanId" />
		<asp:BoundColumn DataField="Timestamp"  />
		<asp:BoundColumn DataField="Amount"     />
	</Columns>
</asp:DataGrid>
</form>
<hr />
<p>
Click <a href="http://www.asp101.com/samples/db_sort_multi_aspx.asp">here</a>
to read about and download the source code.
</p>
</body>
</html>