Another DataGrid Sorting problem

D

ddaniel

I have read many posts and seen many papers on the different
techniques for sort and filtering datagrids. Many do re-queries
against the dB ala Fritz Onion. I am trying to leverage the Dataview.
The following control simply responds to a sort request and/or a
pageing reqeust with an empty table (header only). Any ideas ?

Code behind :

namespace OakTree.data
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for AttendeeListControl.
/// </summary>
public class AttendeeListControl : System.Web.UI.UserControl
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Data.OleDb.OleDbConnection oleDbConnection1;
protected OakTree.data.DataSet1 dataSet11;
protected System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
protected System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
protected System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
protected System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
protected System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;

private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
LoadData();

}

BindData();



}

DataView GetData()
{
DataSet ds = (DataSet)Session["Data"];

DataTable dt = ds.Tables[0];
return dt.DefaultView;

}

void LoadData()
{
//store the DataSet in Cache
oleDbDataAdapter1.Fill(dataSet11);

Session["Data"] = dataSet11;
}

void BindData()
{

// Create a DataView from the DataTable.
DataView dv = GetData();

DataGrid1.DataSource = dv.Table;
DataGrid1.DataMember = dv.Table.TableName;
// DataGrid1.DataKeyField = dv.Table.Columns[0].ColumnName;
DataGrid1.DataBind();
}



#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.dataSet11 = new OakTree.data.DataSet1();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
this.DataGrid1.PageIndexChanged += new
System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.Handle_PageIndex_changed);
this.DataGrid1.SortCommand += new
System.Web.UI.WebControls.DataGridSortCommandEventHandler(this.Handle_sort);
//
// oleDbConnection1
//
this.oleDbConnection1.ConnectionString = @"User ID=sa;Data
Source=""SF-DDANIEL-XP"";Tag with column collation when
possible=False;Initial Catalog=Northwind;Use Procedure for
Prepare=1;Auto Translate=True;Persist Security
Info=False;Provider=""SQLOLEDB.1"";Workstation
ID=""SF-DDANIEL-XP"";Use Encryption for Data=False;Packet Size=4096";
//
// dataSet11
//
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new
System.Globalization.CultureInfo("en-US");
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText = "SELECT ContactName,
CompanyName, Address, City, Region, PostalCode, Country, Phon" +
"e, Fax, CustomerID FROM Customers";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
//
// oleDbInsertCommand1
//
this.oleDbInsertCommand1.CommandText = @"INSERT INTO
Customers(ContactName, CompanyName, Address, City, Region, PostalCode,
Country, Phone, Fax, CustomerID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,
?); SELECT ContactName, CompanyName, Address, City, Region,
PostalCode, Country, Phone, Fax, CustomerID FROM Customers WHERE
(CustomerID = ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("ContactName",
System.Data.OleDb.OleDbType.VarWChar, 30, "ContactName"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CompanyName",
System.Data.OleDb.OleDbType.VarWChar, 40, "CompanyName"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Address",
System.Data.OleDb.OleDbType.VarWChar, 60, "Address"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("City",
System.Data.OleDb.OleDbType.VarWChar, 15, "City"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Region",
System.Data.OleDb.OleDbType.VarWChar, 15, "Region"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("PostalCode",
System.Data.OleDb.OleDbType.VarWChar, 10, "PostalCode"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Country",
System.Data.OleDb.OleDbType.VarWChar, 15, "Country"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Phone",
System.Data.OleDb.OleDbType.VarWChar, 24, "Phone"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Fax",
System.Data.OleDb.OleDbType.VarWChar, 24, "Fax"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CustomerID",
System.Data.OleDb.OleDbType.VarWChar, 5, "CustomerID"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Select_CustomerID",
System.Data.OleDb.OleDbType.VarWChar, 5, "CustomerID"));
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText = @"UPDATE Customers SET
ContactName = ?, CompanyName = ?, Address = ?, City = ?, Region = ?,
PostalCode = ?, Country = ?, Phone = ?, Fax = ?, CustomerID = ? WHERE
(CustomerID = ?) AND (Address = ? OR ? IS NULL AND Address IS NULL)
AND (City = ? OR ? IS NULL AND City IS NULL) AND (CompanyName = ?) AND
(ContactName = ? OR ? IS NULL AND ContactName IS NULL) AND (Country =
? OR ? IS NULL AND Country IS NULL) AND (Fax = ? OR ? IS NULL AND Fax
IS NULL) AND (Phone = ? OR ? IS NULL AND Phone IS NULL) AND
(PostalCode = ? OR ? IS NULL AND PostalCode IS NULL) AND (Region = ?
OR ? IS NULL AND Region IS NULL); SELECT ContactName, CompanyName,
Address, City, Region, PostalCode, Country, Phone, Fax, CustomerID
FROM Customers WHERE (CustomerID = ?)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("ContactName",
System.Data.OleDb.OleDbType.VarWChar, 30, "ContactName"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CompanyName",
System.Data.OleDb.OleDbType.VarWChar, 40, "CompanyName"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Address",
System.Data.OleDb.OleDbType.VarWChar, 60, "Address"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("City",
System.Data.OleDb.OleDbType.VarWChar, 15, "City"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Region",
System.Data.OleDb.OleDbType.VarWChar, 15, "Region"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("PostalCode",
System.Data.OleDb.OleDbType.VarWChar, 10, "PostalCode"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Country",
System.Data.OleDb.OleDbType.VarWChar, 15, "Country"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Phone",
System.Data.OleDb.OleDbType.VarWChar, 24, "Phone"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Fax",
System.Data.OleDb.OleDbType.VarWChar, 24, "Fax"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("CustomerID",
System.Data.OleDb.OleDbType.VarWChar, 5, "CustomerID"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_CustomerID",
System.Data.OleDb.OleDbType.VarWChar, 5,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CustomerID", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Address",
System.Data.OleDb.OleDbType.VarWChar, 60,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Address1",
System.Data.OleDb.OleDbType.VarWChar, 60,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_City",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "City", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_City1",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "City", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_CompanyName",
System.Data.OleDb.OleDbType.VarWChar, 40,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CompanyName",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_ContactName",
System.Data.OleDb.OleDbType.VarWChar, 30,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ContactName",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_ContactName1",
System.Data.OleDb.OleDbType.VarWChar, 30,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ContactName",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Country",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Country1",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Fax",
System.Data.OleDb.OleDbType.VarWChar, 24,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Fax", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Fax1",
System.Data.OleDb.OleDbType.VarWChar, 24,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Fax", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Phone",
System.Data.OleDb.OleDbType.VarWChar, 24,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Phone1",
System.Data.OleDb.OleDbType.VarWChar, 24,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_PostalCode",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "PostalCode", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_PostalCode1",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "PostalCode", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Region",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Region", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Region1",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Region", System.Data.DataRowVersion.Original,
null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Select_CustomerID",
System.Data.OleDb.OleDbType.VarWChar, 5, "CustomerID"));
//
// oleDbDeleteCommand1
//
this.oleDbDeleteCommand1.CommandText = @"DELETE FROM Customers
WHERE (CustomerID = ?) AND (Address = ? OR ? IS NULL AND Address IS
NULL) AND (City = ? OR ? IS NULL AND City IS NULL) AND (CompanyName =
?) AND (ContactName = ? OR ? IS NULL AND ContactName IS NULL) AND
(Country = ? OR ? IS NULL AND Country IS NULL) AND (Fax = ? OR ? IS
NULL AND Fax IS NULL) AND (Phone = ? OR ? IS NULL AND Phone IS NULL)
AND (PostalCode = ? OR ? IS NULL AND PostalCode IS NULL) AND (Region =
? OR ? IS NULL AND Region IS NULL)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_CustomerID",
System.Data.OleDb.OleDbType.VarWChar, 5,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CustomerID", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Address",
System.Data.OleDb.OleDbType.VarWChar, 60,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Address1",
System.Data.OleDb.OleDbType.VarWChar, 60,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_City",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "City", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_City1",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "City", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_CompanyName",
System.Data.OleDb.OleDbType.VarWChar, 40,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CompanyName",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_ContactName",
System.Data.OleDb.OleDbType.VarWChar, 30,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ContactName",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_ContactName1",
System.Data.OleDb.OleDbType.VarWChar, 30,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ContactName",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Country",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Country1",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Fax",
System.Data.OleDb.OleDbType.VarWChar, 24,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Fax", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Fax1",
System.Data.OleDb.OleDbType.VarWChar, 24,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Fax", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Phone",
System.Data.OleDb.OleDbType.VarWChar, 24,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Phone1",
System.Data.OleDb.OleDbType.VarWChar, 24,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_PostalCode",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "PostalCode", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_PostalCode1",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "PostalCode", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Region",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Region", System.Data.DataRowVersion.Original,
null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Region1",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Region", System.Data.DataRowVersion.Original,
null));
//
// oleDbDataAdapter1
//
this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new
System.Data.Common.DataTableMapping("Table", "Customers", new
System.Data.Common.DataColumnMapping[] {
new
System.Data.Common.DataColumnMapping("ContactName", "ContactName"),
new
System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
new
System.Data.Common.DataColumnMapping("Address", "Address"),
new
System.Data.Common.DataColumnMapping("City", "City"),
new
System.Data.Common.DataColumnMapping("Region", "Region"),
new
System.Data.Common.DataColumnMapping("PostalCode", "PostalCode"),
new
System.Data.Common.DataColumnMapping("Country", "Country"),
new
System.Data.Common.DataColumnMapping("Phone", "Phone"),
new
System.Data.Common.DataColumnMapping("Fax", "Fax"),
new
System.Data.Common.DataColumnMapping("CustomerID", "CustomerID")})});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
this.Load += new System.EventHandler(this.Page_Load);
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();

}
#endregion

private void Handle_sort(object source,
System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{


DataView dv = GetData();
dv.Sort = e.SortExpression;



DataGrid1.DataSource = dv;
DataGrid1.DataMember = dv.Table.TableName;
DataGrid1.DataBind();



}

private void Handle_PageIndex_changed(object source,
System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{


DataGrid1.CurrentPageIndex = e.NewPageIndex;
BindData();
}
}
}


Code in front:

<%@ Control Language="c#" AutoEventWireup="false"
Codebehind="AttendeeListControl.ascx.cs"
Inherits="OakTree.data.AttendeeListControl"
TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%>
<P>
<asp:DataGrid id=DataGrid1 runat="server" PageSize="20"
DataSource="<%# dataSet11 %>" DataMember="Customers"
BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"
BackColor="White" CellPadding="3" GridLines="Vertical"
ForeColor="Black" DataKeyField="CustomerID" AllowSorting="True"
AutoGenerateColumns="False" AllowPaging="True">
<SelectedItemStyle Font-Bold="True" ForeColor="White"
BackColor="#000099"></SelectedItemStyle>
<AlternatingItemStyle BackColor="#CCCCCC"></AlternatingItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White"
BackColor="Black"></HeaderStyle>
<FooterStyle BackColor="#CCCCCC"></FooterStyle>
<Columns>
<asp:BoundColumn DataField="ContactName"
SortExpression="ContactName"
HeaderText="ContactName"></asp:BoundColumn>
<asp:BoundColumn DataField="CompanyName"
SortExpression="CompanyName"
HeaderText="CompanyName"></asp:BoundColumn>
<asp:BoundColumn DataField="City" SortExpression="City"
HeaderText="City"></asp:BoundColumn>
<asp:BoundColumn DataField="Region" SortExpression="Region"
HeaderText="Region"></asp:BoundColumn>
</Columns>
<PagerStyle NextPageText="next &amp;gt;" PrevPageText="previous
&amp;lt;" HorizontalAlign="Center"
ForeColor="Black" Position="Top" BackColor="#999999"
Mode="NumericPages"></PagerStyle>
</asp:DataGrid></P>
 
J

Jared

ddaniel,
Try something like this.
Jared

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then LoadData()
End Sub

Private Sub LoadData()
Dim conn As New SqlClient.SqlConnection
conn.ConnectionString =
"Server=localhost;Database=Northwind;Trusted_Connection=True;"
conn.Open()
Dim da As New SqlClient.SqlDataAdapter("Select TitleOfCourtesy,
FirstName, LastName, Title FROM Employees Order By FirstName", conn)
Dim ds As New DataSet
da.Fill(ds)
conn.Close()
Me.DataGrid1.DataSource = ds
Me.DataGrid1.DataBind()
Dim reader As SqlClient.SqlDataReader
If viewstate.Item("ds") Is Nothing Then
viewstate.Add("ds", ds)
End If
End Sub

Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
DataGrid1.SortCommand
If viewstate.Item("SortExp") Is Nothing Then
viewstate.Add("SortExp", e.SortExpression)
Else
If viewstate.Item("SortExp") = e.SortExpression Then
viewstate.Item("SortExp") = e.SortExpression & " DESC"
Else
viewstate.Item("SortExp") = e.SortExpression
End If
End If
Sort()
End Sub

Private Sub Sort()
If Not viewstate.Item("ds") Is Nothing Then
Dim myds As DataSet = DirectCast(viewstate.Item("ds"), DataSet)
Dim dv As DataView = myds.Tables(0).DefaultView
dv.Sort = viewstate("SortExp")
Me.DataGrid1.DataSource = dv
Me.DataGrid1.DataBind()
End If
End Sub


ddaniel said:
I have read many posts and seen many papers on the different
techniques for sort and filtering datagrids. Many do re-queries
against the dB ala Fritz Onion. I am trying to leverage the Dataview.
The following control simply responds to a sort request and/or a
pageing reqeust with an empty table (header only). Any ideas ?
Click to expand...
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
LoadData();

}

BindData();
}

DataView GetData()
{
DataSet ds = (DataSet)Session["Data"];

DataTable dt = ds.Tables[0];
return dt.DefaultView;

}

void LoadData()
{
//store the DataSet in Cache
oleDbDataAdapter1.Fill(dataSet11);

Session["Data"] = dataSet11;
}

void BindData()
{

// Create a DataView from the DataTable.
DataView dv = GetData();

DataGrid1.DataSource = dv.Table;
DataGrid1.DataMember = dv.Table.TableName;
// DataGrid1.DataKeyField = dv.Table.Columns[0].ColumnName;
DataGrid1.DataBind();
}

private void Handle_sort(object source,
System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
DataView dv = GetData();
dv.Sort = e.SortExpression;

DataGrid1.DataSource = dv;
DataGrid1.DataMember = dv.Table.TableName;
DataGrid1.DataBind();
}

private void Handle_PageIndex_changed(object source,
System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{


DataGrid1.CurrentPageIndex = e.NewPageIndex;
BindData();
}
}
}


Code in front:

<%@ Control Language="c#" AutoEventWireup="false"
Codebehind="AttendeeListControl.ascx.cs"
Inherits="OakTree.data.AttendeeListControl"
TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%>
<P>
<asp:DataGrid id=DataGrid1 runat="server" PageSize="20"
DataSource="<%# dataSet11 %>" DataMember="Customers"
BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"
BackColor="White" CellPadding="3" GridLines="Vertical"
ForeColor="Black" DataKeyField="CustomerID" AllowSorting="True"
AutoGenerateColumns="False" AllowPaging="True">
<SelectedItemStyle Font-Bold="True" ForeColor="White"
BackColor="#000099"></SelectedItemStyle>
<AlternatingItemStyle BackColor="#CCCCCC"></AlternatingItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White"
BackColor="Black"></HeaderStyle>
<FooterStyle BackColor="#CCCCCC"></FooterStyle>
<Columns>
<asp:BoundColumn DataField="ContactName"
SortExpression="ContactName"
HeaderText="ContactName"></asp:BoundColumn>
<asp:BoundColumn DataField="CompanyName"
SortExpression="CompanyName"
HeaderText="CompanyName"></asp:BoundColumn>
<asp:BoundColumn DataField="City" SortExpression="City"
HeaderText="City"></asp:BoundColumn>
<asp:BoundColumn DataField="Region" SortExpression="Region"
HeaderText="Region"></asp:BoundColumn>
</Columns>
<PagerStyle NextPageText="next &amp;gt;" PrevPageText="previous
&amp;lt;" HorizontalAlign="Center"
ForeColor="Black" Position="Top" BackColor="#999999"
Mode="NumericPages"></PagerStyle>
</asp:DataGrid></P>
Click to expand...
 
D

ddaniel

Jared,

Thanks for the response the new code snippet looks like:

private void Sort()
{

if(ViewState["Data"]!=null)
{
DataSet ds= (DataSet)ViewState["Data"];
DataView dv= ds.Tables[0].DefaultView;
dv.Sort = (string)ViewState["SortExp"];
this.DataGrid1.DataSource = dv;
this.DataGrid1.DataBind();

}
}


private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
LoadData();

}

BindData();


}


void LoadData()
{
oleDbDataAdapter1.Fill(dataSet11);

if(this.ViewState["Data"] == null)
{
this.ViewState["Data"] = dataSet11;
}

}

void BindData()
{

this.DataGrid1.DataSource = dataSet11;
DataGrid1.DataBind();
}

private void Handle_sort(object source,
System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{


if (this.ViewState["SortExp"] == null)
{

ViewState.Add("SortExp" , e.SortExpression);
}
else if ((string)this.ViewState["SortExp"] == e.SortExpression)
{
ViewState["SortExp"] = e.SortExpression + " DESC";

}
else
{
ViewState["SortExp"] = e.SortExpression;

}


Sort();

}


the Init coe is the same as before. Still not responding with rows.
There must be something really dumb in there but I just Don't see it
!!!!!

Darin



Jared said:
ddaniel,
Try something like this.
Jared

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then LoadData()
End Sub

Private Sub LoadData()
Dim conn As New SqlClient.SqlConnection
conn.ConnectionString =
"Server=localhost;Database=Northwind;Trusted_Connection=True;"
conn.Open()
Dim da As New SqlClient.SqlDataAdapter("Select TitleOfCourtesy,
FirstName, LastName, Title FROM Employees Order By FirstName", conn)
Dim ds As New DataSet
da.Fill(ds)
conn.Close()
Me.DataGrid1.DataSource = ds
Me.DataGrid1.DataBind()
Dim reader As SqlClient.SqlDataReader
If viewstate.Item("ds") Is Nothing Then
viewstate.Add("ds", ds)
End If
End Sub

Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
DataGrid1.SortCommand
If viewstate.Item("SortExp") Is Nothing Then
viewstate.Add("SortExp", e.SortExpression)
Else
If viewstate.Item("SortExp") = e.SortExpression Then
viewstate.Item("SortExp") = e.SortExpression & " DESC"
Else
viewstate.Item("SortExp") = e.SortExpression
End If
End If
Sort()
End Sub

Private Sub Sort()
If Not viewstate.Item("ds") Is Nothing Then
Dim myds As DataSet = DirectCast(viewstate.Item("ds"), DataSet)
Dim dv As DataView = myds.Tables(0).DefaultView
dv.Sort = viewstate("SortExp")
Me.DataGrid1.DataSource = dv
Me.DataGrid1.DataBind()
End If
End Sub


ddaniel said:
I have read many posts and seen many papers on the different
techniques for sort and filtering datagrids. Many do re-queries
against the dB ala Fritz Onion. I am trying to leverage the Dataview.
The following control simply responds to a sort request and/or a
pageing reqeust with an empty table (header only). Any ideas ?
Click to expand...
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
LoadData();

}

BindData();
}

DataView GetData()
{
DataSet ds = (DataSet)Session["Data"];

DataTable dt = ds.Tables[0];
return dt.DefaultView;

}

void LoadData()
{
//store the DataSet in Cache
oleDbDataAdapter1.Fill(dataSet11);

Session["Data"] = dataSet11;
}

void BindData()
{

// Create a DataView from the DataTable.
DataView dv = GetData();

DataGrid1.DataSource = dv.Table;
DataGrid1.DataMember = dv.Table.TableName;
// DataGrid1.DataKeyField = dv.Table.Columns[0].ColumnName;
DataGrid1.DataBind();
}

private void Handle_sort(object source,
System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
DataView dv = GetData();
dv.Sort = e.SortExpression;

DataGrid1.DataSource = dv;
DataGrid1.DataMember = dv.Table.TableName;
DataGrid1.DataBind();
}

private void Handle_PageIndex_changed(object source,
System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{


DataGrid1.CurrentPageIndex = e.NewPageIndex;
BindData();
}
}
}


Code in front:

<%@ Control Language="c#" AutoEventWireup="false"
Codebehind="AttendeeListControl.ascx.cs"
Inherits="OakTree.data.AttendeeListControl"
TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%>
<P>
<asp:DataGrid id=DataGrid1 runat="server" PageSize="20"
DataSource="<%# dataSet11 %>" DataMember="Customers"
BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"
BackColor="White" CellPadding="3" GridLines="Vertical"
ForeColor="Black" DataKeyField="CustomerID" AllowSorting="True"
AutoGenerateColumns="False" AllowPaging="True">
<SelectedItemStyle Font-Bold="True" ForeColor="White"
BackColor="#000099"></SelectedItemStyle>
<AlternatingItemStyle BackColor="#CCCCCC"></AlternatingItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White"
BackColor="Black"></HeaderStyle>
<FooterStyle BackColor="#CCCCCC"></FooterStyle>
<Columns>
<asp:BoundColumn DataField="ContactName"
SortExpression="ContactName"
HeaderText="ContactName"></asp:BoundColumn>
<asp:BoundColumn DataField="CompanyName"
SortExpression="CompanyName"
HeaderText="CompanyName"></asp:BoundColumn>
<asp:BoundColumn DataField="City" SortExpression="City"
HeaderText="City"></asp:BoundColumn>
<asp:BoundColumn DataField="Region" SortExpression="Region"
HeaderText="Region"></asp:BoundColumn>
</Columns>
<PagerStyle NextPageText="next &amp;gt;" PrevPageText="previous
&amp;lt;" HorizontalAlign="Center"
ForeColor="Black" Position="Top" BackColor="#999999"
Mode="NumericPages"></PagerStyle>
</asp:DataGrid></P>
Click to expand...
Click to expand...
 
J

Jared

When you step through the code does (DataSet)ViewState["Data"] contain your
data? Does ViewState["SortExp"] hold your sort expression? Try creating a
seperate page, drop in a new datagrid, connect to your database, use a
sample of data from your database (small select, three or four columns.),
have the columns generated dynamically, allow paging and sorting. Does it
work?

ddaniel said:
Jared,

Thanks for the response the new code snippet looks like:

private void Sort()
{

if(ViewState["Data"]!=null)
{
DataSet ds= (DataSet)ViewState["Data"];
DataView dv= ds.Tables[0].DefaultView;
dv.Sort = (string)ViewState["SortExp"];
this.DataGrid1.DataSource = dv;
this.DataGrid1.DataBind();

}
}


private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
LoadData();

}

BindData();


}


void LoadData()
{
oleDbDataAdapter1.Fill(dataSet11);

if(this.ViewState["Data"] == null)
{
this.ViewState["Data"] = dataSet11;
}

}

void BindData()
{

this.DataGrid1.DataSource = dataSet11;
DataGrid1.DataBind();
}

private void Handle_sort(object source,
System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{


if (this.ViewState["SortExp"] == null)
{

ViewState.Add("SortExp" , e.SortExpression);
}
else if ((string)this.ViewState["SortExp"] == e.SortExpression)
{
ViewState["SortExp"] = e.SortExpression + " DESC";

}
else
{
ViewState["SortExp"] = e.SortExpression;

}


Sort();

}


the Init coe is the same as before. Still not responding with rows.
There must be something really dumb in there but I just Don't see it
!!!!!

Darin



Jared said:
ddaniel,
Try something like this.
Jared

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack Then LoadData()
End Sub

Private Sub LoadData()
Dim conn As New SqlClient.SqlConnection
conn.ConnectionString =
"Server=localhost;Database=Northwind;Trusted_Connection=True;"
conn.Open()
Dim da As New SqlClient.SqlDataAdapter("Select TitleOfCourtesy,
FirstName, LastName, Title FROM Employees Order By FirstName", conn)
Dim ds As New DataSet
da.Fill(ds)
conn.Close()
Me.DataGrid1.DataSource = ds
Me.DataGrid1.DataBind()
Dim reader As SqlClient.SqlDataReader
If viewstate.Item("ds") Is Nothing Then
viewstate.Add("ds", ds)
End If
End Sub

Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
DataGrid1.SortCommand
If viewstate.Item("SortExp") Is Nothing Then
viewstate.Add("SortExp", e.SortExpression)
Else
If viewstate.Item("SortExp") = e.SortExpression Then
viewstate.Item("SortExp") = e.SortExpression & " DESC"
Else
viewstate.Item("SortExp") = e.SortExpression
End If
End If
Sort()
End Sub

Private Sub Sort()
If Not viewstate.Item("ds") Is Nothing Then
Dim myds As DataSet = DirectCast(viewstate.Item("ds"), DataSet)
Dim dv As DataView = myds.Tables(0).DefaultView
dv.Sort = viewstate("SortExp")
Me.DataGrid1.DataSource = dv
Me.DataGrid1.DataBind()
End If
End Sub


ddaniel said:
I have read many posts and seen many papers on the different
techniques for sort and filtering datagrids. Many do re-queries
against the dB ala Fritz Onion. I am trying to leverage the Dataview.
The following control simply responds to a sort request and/or a
pageing reqeust with an empty table (header only). Any ideas ?
Click to expand...
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
LoadData();

}

BindData();
}

DataView GetData()
{
DataSet ds = (DataSet)Session["Data"];

DataTable dt = ds.Tables[0];
return dt.DefaultView;

}

void LoadData()
{
//store the DataSet in Cache
oleDbDataAdapter1.Fill(dataSet11);

Session["Data"] = dataSet11;
}

void BindData()
{

// Create a DataView from the DataTable.
DataView dv = GetData();

DataGrid1.DataSource = dv.Table;
DataGrid1.DataMember = dv.Table.TableName;
// DataGrid1.DataKeyField = dv.Table.Columns[0].ColumnName;
DataGrid1.DataBind();
}

private void Handle_sort(object source,
System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
DataView dv = GetData();
dv.Sort = e.SortExpression;

DataGrid1.DataSource = dv;
DataGrid1.DataMember = dv.Table.TableName;
DataGrid1.DataBind();
}

private void Handle_PageIndex_changed(object source,
System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{


DataGrid1.CurrentPageIndex = e.NewPageIndex;
BindData();
}
}
}


Code in front:

<%@ Control Language="c#" AutoEventWireup="false"
Codebehind="AttendeeListControl.ascx.cs"
Inherits="OakTree.data.AttendeeListControl"
TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%>
<P>
<asp:DataGrid id=DataGrid1 runat="server" PageSize="20"
DataSource="<%# dataSet11 %>" DataMember="Customers"
BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"
BackColor="White" CellPadding="3" GridLines="Vertical"
ForeColor="Black" DataKeyField="CustomerID" AllowSorting="True"
AutoGenerateColumns="False" AllowPaging="True">
<SelectedItemStyle Font-Bold="True" ForeColor="White"
BackColor="#000099"></SelectedItemStyle>
<AlternatingItemStyle BackColor="#CCCCCC"></AlternatingItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White"
BackColor="Black"></HeaderStyle>
<FooterStyle BackColor="#CCCCCC"></FooterStyle>
<Columns>
<asp:BoundColumn DataField="ContactName"
SortExpression="ContactName"
HeaderText="ContactName"></asp:BoundColumn>
<asp:BoundColumn DataField="CompanyName"
SortExpression="CompanyName"
HeaderText="CompanyName"></asp:BoundColumn>
<asp:BoundColumn DataField="City" SortExpression="City"
HeaderText="City"></asp:BoundColumn>
<asp:BoundColumn DataField="Region" SortExpression="Region"
HeaderText="Region"></asp:BoundColumn>
</Columns>
<PagerStyle NextPageText="next &amp;gt;" PrevPageText="previous
&amp;lt;" HorizontalAlign="Center"
ForeColor="Black" Position="Top" BackColor="#999999"
Mode="NumericPages"></PagerStyle>
</asp:DataGrid></P>
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concurrency Error in Update. 1
Insert into Access with C# 1
urgent !!!! problem updating data set 3
sintax error in INSERT INTO statement 2
What are OleDbParameter names with 'Original_' prefix? 5
Database wont update but getting no errors.... 7
Cant update my database. 5
update oledb database from text field 9

Top