By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,398 Members | 1,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,398 IT Pros & Developers. It's quick & easy.

Another DataGrid Sorting problem

P: n/a
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.d ataSet11)).BeginInit();
this.DataGrid1.PageIndexChanged += new
System.Web.UI.WebControls.DataGridPageChangedEvent Handler(this.Handle_PageIndex_changed);
this.DataGrid1.SortCommand += new
System.Web.UI.WebControls.DataGridSortCommandEvent Handler(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_CustomerI D",
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_Custome rID",
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_Address 1",
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_Company Name",
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_Contact Name",
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_Contact Name1",
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_Country 1",
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_PostalC ode",
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_PostalC ode1",
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_CustomerI D",
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_Custome rID",
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_Address 1",
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_Company Name",
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_Contact Name",
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_Contact Name1",
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_Country 1",
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_PostalC ode",
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_PostalC ode1",
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.d ataSet11)).EndInit();

}
#endregion

private void Handle_sort(object source,
System.Web.UI.WebControls.DataGridSortCommandEvent Args 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.DataGridPageChangedEvent Args 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>
Nov 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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_Conne ction=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.DataGridSortCommandEvent Args) 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" <da*********@yahoo.com> wrote in message
news:36**************************@posting.google.c om...
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 ?
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.DataGridSortCommandEvent Args 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.DataGridPageChangedEvent Args 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>

Nov 18 '05 #2

P: n/a
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" <da*********@yahoo.com> wrote in message
news:36**************************@posting.google.c om...
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.DataGridSortCommandEvent Args 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" <VB***********@email.com> wrote in message
news:<10*************@corp.supernews.com>...
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_Conne ction=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.DataGridSortCommandEvent Args) 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" <da*********@yahoo.com> wrote in message
news:36**************************@posting.google.c om...
>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 ?
>

> 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.DataGridSortCommandEvent Args 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.DataGridPageChangedEvent Args 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>

Nov 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.