I have a GridView attached to a SQLDatasource, it works fine, and I can sort it properly with the original data.
If I tell the codebehind to find specific information via a pre-defined SQLDataSource on the ASPX page (SqlDataSource2), subsequent sorting works properly.
If I tell the codebehind to modify a pre-existing sqldatasource and bind that, subsequent sorting does not work properly, but returns original data.
Prereq: SQL Data source connectionstring defined in Web.config, with id, last_modified, and view_count columns in table document_index.
ASPX:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" EnablePartialRendering="true" runat="server" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
SelectCommand="SELECT id, last_modified, view_count FROM [document_index] ORDER by id"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
SelectCommand= "SELECT id, last_modified, view_count FROM [document_index] WHERE view_count LIKE '7'"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" />
<div>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<asp:Button runat="server" Text="Select View Using ASPX SQL Source2 (Sort Works)" OnClick="aspxviewchange" />
<asp:Button runat="server" Text="Select View Using Codebehind SQL Source1 Change Data (Sort Breaks)" OnClick="cbviewchange" />
<asp:GridView ID="GridView1" EnableSortingAndPagingCallbacks= "true" runat="server" AllowSorting="True" DataSourceID="SqlDataSource1" AutoGenerateColumns="False" Width="100%" CellPadding="4" Padding="20" DataKeyNames="id" ForeColor="#333333" GridLines="None" AllowPaging="True" PageSize="25" AllowCustomPaging="True">
<Columns>
</Columns>
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>
C# Codebehind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
CreateNewColumn("last_modified", "Last Modified");
CreateNewColumn("view_count", "Views");
}
}
protected void CreateNewColumn(string SQLcolname, string header)
{
BoundField NewColumnName = new BoundField();
NewColumnName.DataField = SQLcolname;
NewColumnName.SortExpression = SQLcolname;
NewColumnName.HeaderText = header;
GridView1.Columns.Add(NewColumnName);
}
protected void aspxviewchange(object sender, EventArgs e) // SUBSEQUENT SORTING WORKS
{
GridView1.DataSourceID = "SQLDataSource2";
GridView1.DataBind();
}
protected void cbviewchange(object sender, EventArgs e) // SUBSEQUENT SORTING BROKEN
{
SqlDataSource1.SelectCommand = "SELECT id, last_modified, view_count FROM [document_index] WHERE view_count LIKE '7'";
GridView1.DataSourceID = "SQLDataSource1";
GridView1.DataBind();
}
}
Selecting the ASPX SQL button sorts properly, and subsequent sorting works. Selecting the Codebehind SQL button sorts properly, but subsequent sorting does not work.
I've decided to work around the issue by using IF/BEGIN/END/ELSE IF clauses in a single SQL statement based on my codebehind parameter. It seems like the best way to go, considering dynamically changing the select command leads to other issues.