<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="c#" runat="server">
OleDbDataAdapter OleDbDa;
OleDbConnection SqlCon;
DataView dvProducts;
DataSet ds;
protected void Page_Load(Object sender, EventArgs e){
string path = Request.MapPath("nwind.Mdb");
SqlCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" + path);
if (! IsPostBack){
BindGrid();
}
}
protected void BindGrid(){
if (! IsPostBack) {
OleDbDa = new OleDbDataAdapter("SELECT S.*, (SELECT COUNT(*)
FROM Products AS P WHERE P.SupplierID = S.SupplierID) AS [ProductCount]
FROM Suppliers AS S", SqlCon);
ds = new DataSet();
OleDbDa.Fill(ds, "Suppliers");
OleDbDa.SelectCommand = new OleDbCommand("SELECT ProductName, ProductID, SupplierID FROM Products", SqlCon);
OleDbDa.Fill(ds, "Products");
dvProducts = ds.Tables["Products"].DefaultView;
MyDataGrid.DataSource = ds.Tables["Suppliers"];
Save(ds);
}else{
dvProducts = Get().Tables["Products"].DefaultView;
MyDataGrid.DataSource = Get().Tables["Suppliers"];
}
Page.DataBind();
}
protected DataView GetProducts(int SupplierID){
dvProducts.RowFilter = "SupplierID = " + SupplierID;
return dvProducts;
}
protected void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs e) {
MyDataGrid.EditItemIndex = e.Item.ItemIndex;
BindGrid();
}
protected void MyDataGrid_Cancel(Object sender, DataGridCommandEventArgs e) {
MyDataGrid.EditItemIndex = -1;
BindGrid();
}
protected void MyDataGrid_Update(Object sender, DataGridCommandEventArgs e) {
string ChangeProductNameFrom = (string)((DropDownList)(e.Item.FindControl("edit_Product"))).SelectedItem.Text;
string ChangeProductNameTo = (string)((TextBox)(e.Item.FindControl("NewProductName"))).Text;
int ProductID = int.Parse((string)((DropDownList)(e.Item.FindControl("edit_Product"))).SelectedItem.Value);
if (ChangeProductNameTo.Length > 0) {
try {
DataTable dtProducts = Get().Tables["Products"];
DataRow[] drProduct = dtProducts.Select("ProductID = " + ProductID);
drProduct[0].BeginEdit();
drProduct[0]["ProductName"] = ChangeProductNameTo;
drProduct[0].EndEdit();
Message.Text = ChangeProductNameFrom + " HAS BEEN CHANGED TO " + ChangeProductNameTo;
} catch (Exception Ex) {
Message.Text = "An Exception Has Occured: " + Ex.Message.ToString();
} finally {
MyDataGrid.EditItemIndex = -1;
BindGrid();
}
} else {
Message.Text = "You must specify a new product name or hit cancel!";
}
}
protected void Save(DataSet DS){
Session.Add("ProductsDS", DS);
}
protected DataSet Get(){
return (DataSet)Session["ProductsDS"];
}
</script>
<html>
<body>
<form runat="server">
<center>
<asp:Label id="Message" MaintainState="false" ForeColor="#CC3300" Font-Size="11pt" runat="server"/><p>
<ASP:DataGrid
id="MyDataGrid"
runat="server"
Width="800"
CellPadding=3
CellSpacing="0"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
DataKeyField="SupplierID"
BorderColor="Tan"
ShowFooter="false"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-Font-Bold="True"
HeaderStyle-BackColor="Maroon"
AutoGenerateColumns="False"
HeaderStyle-ForeColor="Tan" >
<columns>
<asp:EditCommandColumn
EditText="Edit"
CancelText="Cancel"
UpdateText="Update"
ItemStyle-VerticalAlign="top" />
<asp:BoundColumn
HeaderText="ID"
ReadOnly="True"
DataField="SupplierID"
ItemStyle-Wrap="false"
ItemStyle-VerticalAlign="top" />
<asp:BoundColumn
HeaderText="ID"
ReadOnly="True"
DataField="CompanyName"
ItemStyle-Wrap="false"
ItemStyle-VerticalAlign="top" />
<asp:TemplateColumn HeaderText="Products" >
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "ProductCount") %> Products
</ItemTemplate>
<EditItemTemplate>
<TABLE Width="100%" CellPadding="0" CellSpacing="0" Border="0">
<TR>
<TD Style="font-size:8" Width="20%">
<b>CHANGE -</b>
</TD>
<TD>
<asp:DropDownList
Runat="server"
Id="edit_Product"
DataSource='<%# GetProducts((int)DataBinder.Eval(Container.DataItem, "SupplierID")) %>'
DataTextField="ProductName"
DataValueField="ProductID"
Width="200" />
</TD>
</TR>
<TR>
<TD Style="font-size:8" Width="20%">
<b>TO - </b>
</TD>
<TD>
<asp:TextBox
id="NewProductName"
runat="Server"
Width="200" />
</TD>
</TR>
</TABLE>
</EditItemTemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
</center>
</form>
</body>
</html>