You now finally have all the necessary ingredients to create a web page with in-place editing:
Mastery of the DataGrid and DataList controls, which support in-place editing
Mastery of retrieving data from and writing data back to the database
Mastery of templates for creating the editable columns
In the next example, you will create a grid with an Edit column. When the user clicks on the edit link, the selected row will be redrawn for editing, as shown in Figure 13-3. The user is free to change any or all fields and then to click the Save link to have the changes written back to the database, or Cancel to cancel the changes and return to non-edit mode.
Notice that the Product and Reported By fields are drop-down lists.
These are populated from the lkProduct and People tables,
respectively. What must be written back to the Bugs table, however,
is the ID rather than the text for each of these values. The complete
.aspx
page for the project is shown in Example 13-6; the complete code-behind page is shown in C#
in Example 13-7 and in VB.NET in Example 13-8. Analysis follows.
Example 13-6. The .aspx page for in-place editing
<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="BugHistoryInPlaceEditing.WebForm1" trace="false"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0"> <meta name="CODE_LANGUAGE" Content="C#"> <meta name="vs_defaultClientScript" content="JavaScript (ECMAScript)"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </HEAD> <body> <form id="Form1" method="post" runat="server"> <asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" CellPadding="5" HeaderStyle-BackColor="PapayaWhip" BorderWidth="5px" BorderColor="#000099" AlternatingItemStyle-BackColor="LightGrey" HeaderStyle-Font-Bold EditItemStyle-BackColor="Yellow" EditItemStyle-ForeColor="Black" DataKeyField ="BugID" OnEditCommand="OnEdit" OnCancelCommand="OnCancel" OnUpdateCommand="OnUpdate"> <Columns> <asp:BoundColumn DataField="BugID" HeaderText="ID" ReadOnly="True" /> <asp:BoundColumn DataField="Description" HeaderText="Description" /> <asp:TemplateColumn HeaderText="Version"> <ItemTemplate> <asp:Label Text='<%# Convert.ToString( DataBinder.Eval(Container.DataItem,"Version")) %>' Runat="server" ID="lblVersion"/> </ItemTemplate> <EditItemTemplate> <asp:TextBox Runat="server" ID="txtVersion" Text = '<%# Convert.ToString( DataBinder.Eval(Container.DataItem,"Version")) %>' Width="30" /> </EditItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Product"> <ItemTemplate> <asp:Label Text='<%# Convert.ToString( DataBinder.Eval( Container.DataItem,"ProductDescription")) %>' Runat="server" ID="lblProduct"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList Runat="server" ID="editProduct" DataSource='<%# GetValues("lkProduct") %>' DataTextField ="ProductDescription" DataValueField ="ProductID" Width ="200" /> </EditItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn HeaderText="Reported By"> <ItemTemplate> <asp:Label Text='<%# Convert.ToString( DataBinder.Eval(Container.DataItem,"FullName")) %>' ID="lblReported" Runat="server"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList Runat="server" ID="editReporter" DataSource='<%# GetValues("People") %>' DataTextField ="FullName" DataValueField ="PersonID" Width ="200" /> </EditItemTemplate> </asp:TemplateColumn> <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Save" /> </Columns> </asp:DataGrid> </form> </body> </HTML>
Some of the indentation was removed and some lines were broken up to enable printing the listing in this book.
Example 13-7. The C# code-behind page for in-place editing
using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; namespace BugHistoryInPlaceEditing { public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.Repeater Repeater1; protected System.Web.UI.WebControls.DataList DataList1; protected System.Web.UI.WebControls.DataGrid DataGrid1; protected System.Web.UI.WebControls.ListBox lbReportedby; public System.Data.SqlClient.SqlDataReader personReader; public WebForm1( ) { Page.Init += new System.EventHandler(Page_Init); } private void Page_Load(object sender, System.EventArgs e) { if (! Page.IsPostBack) { BindGrid( ); } } // extract the bug records and bind to the datagrid private void BindGrid( ) { // connect to the Bugs database string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs"; System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // get records from the Bugs table string commandString = "Select b.BugID, b.Version, b.Description, "; commandString += "p.ProductDescription, "; commandString += "peo.FullName from Bugs b "; commandString += "join lkProduct p on b.Product = p.ProductID "; commandString += "join People peo on b.Reporter = peo.PersonID "; System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.CommandText = commandString; command.Connection = connection; // Create the Reader adn bind it to the datagrid SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); DataGrid1.DataSource=reader; DataGrid1.DataBind( ); } // Given the name of a table, return a DataReader for // all values from that table public System.Data.SqlClient.SqlDataReader GetValues(string tableName) { // connect to the Bugs database string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs"; // create and open the connection object System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // get records from the Bugs table string commandString = "Select * from " + tableName; // create the command object and set its // command string and connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.CommandText = commandString; command.Connection = connection; // create the DataReader and return it return command.ExecuteReader(CommandBehavior.CloseConnection); } // Handle the Edit event - set the EditItemIndex of the // selected row public void OnEdit(Object source, DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = e.Item.ItemIndex; BindGrid( ); } // Handle the cancel event - set the EditItemIndex to -1 public void OnCancel(Object source, DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = -1; BindGrid( ); } // Handle the Update event // Extract the new values // Update the database and rebind the datagrid public void OnUpdate(Object source, DataGridCommandEventArgs e) { string PersonID = ((DropDownList)(e.Item.FindControl("editReporter"))). SelectedItem.Value; string newDescription = ((TextBox) e.Item.Cells[1].Controls[0]).Text; string ProductID = ((DropDownList)(e.Item.FindControl("editProduct"))). SelectedItem.Value; string newVersion = ((TextBox)(e.Item.FindControl("txtVersion"))).Text; // form the update statement string cmd = "Update Bugs set Product = " + ProductID + ", Version = '" + newVersion + "', Description = '" + newDescription + " ', Reporter = " + PersonID + " where BugID = " + DataGrid1.DataKeys[e.Item.ItemIndex]; // connect to the Bugs database string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs"; System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // call the update and rebind the datagrid System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.CommandText = cmd; command.Connection = connection; command.ExecuteNonQuery( ); DataGrid1.EditItemIndex = -1; BindGrid( ); } private void Page_Init(object sender, EventArgs e) { InitializeComponent( ); } #region Web Form Designer generated code private void InitializeComponent( ) { this.Load += new System.EventHandler(this.Page_Load); } #endregion }
}
Example 13-8. The VB.NET code-behind page for in-place editing
Imports System.Data.SqlClient Public Class WebForm1 Inherits System.Web.UI.Page Protected WithEvents repeater1 As System.Web.UI.WebControls.Repeater Protected WithEvents DataList1 As System.Web.UI.WebControls.DataList Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid Protected WithEvents lbReportedby As System.Web.UI.WebControls.ListBox Public personReader As System.Data.SqlClient.SqlDataReader #Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent( ) End Sub Private Sub Page_Init(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent( ) End Sub #End Region Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load If Not Page.IsPostBack Then BindGrid( ) End If End Sub ' extract the bug records and bind to the datagrid Private Sub BindGrid( ) ' connect to the Bugs database Dim connectionString As String = _ "server=YourServer; uid=sa; pwd=YourPassword; " & _ "database=ProgASPDotNetBugs" Dim connection As New SqlConnection(connectionString) connection.Open( ) ' get records from the Bugs table Dim commandString As String = _ "Select b.BugID, b.Version, b.Description, " & _ "p.ProductDescription, peo.FullName from Bugs b " & _ "join lkProduct p on b.Product = p.ProductID " & _ "join People peo on b.Reporter = peo.PersonID " Dim command As New SqlCommand( ) command.CommandText = commandString command.Connection = connection ' Create the Reader and bind it to the datagrid Dim reader As SqlDataReader = _ command.ExecuteReader(CommandBehavior.CloseConnection) DataGrid1.DataSource = reader DataGrid1.DataBind( ) End Sub ' Given the name of a table, return a DataReader for ' all values from that table Public Function GetValues(tableName As String) As SqlDataReader ' connect to the Bugs database Dim connectionString As String = _ "server=YourServer; uid=sa; " & _ "pwd=YourPassword; database=ProgASPDotNetBugs" ' create and open the connection object Dim connection As New SqlConnection(connectionString) connection.Open( ) ' get records from the Bugs table Dim commandString As String = "Select * from " & tableName ' create the command object and set its ' command string and connection Dim command As New SqlCommand command.CommandText = commandString command.Connection = connection ' create the DataReader and return it Return command.ExecuteReader(CommandBehavior.CloseConnection) End Function ' Handle the Edit event - set the EditItemIndex of the ' selected row Public Sub OnEdit(ByVal source As Object, ByVal e As DataGridCommandEventArgs) DataGrid1.EditItemIndex = e.Item.ItemIndex BindGrid( ) End Sub ' Handle the cancel event - set the EditItemIndex to -1 Public Sub OnCancel(ByVal source As Object, ByVal e As DataGridCommandEventArgs) DataGrid1.EditItemIndex = -1 BindGrid( ) End Sub ' Handle the Update event ' Extract the new values ' Update the database and rebind the datagrid Public Sub OnUpdate(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Dim PersonID As String = CType(e.Item.FindControl( _ "editReporter"), DropDownList).SelectedItem.Value Dim newDescription As String = CType(e.Item.Cells(1).Controls(0), _ TextBox).Text Dim ProductID As String = CType(e.Item.FindControl( _ "editProduct"), DropDownList).SelectedItem.Value Dim newVersion As String = CType(e.Item.FindControl( _ "txtVersion"), TextBox).Text ' form the update statement Dim cmd As String = "Update Bugs set Product = " & ProductID & _ ", Version = '" & newVersion & _ "', Description = '" & newDescription & _ " ', Reporter = " & PersonID & _ " where BugID = " & DataGrid1.DataKeys(e.Item.ItemIndex) ' connect to the Bugs database Dim connectionString As String = _ "server=YourServer; uid=sa; pwd=YourPassword; " & _ "database=ProgASPDotNetBugs" Dim connection As New SqlConnection(connectionString) connection.Open( ) ' call the update and rebind the datagrid Dim command As New SqlCommand( ) command.CommandText = cmd command.Connection = connection command.ExecuteNonQuery( ) DataGrid1.EditItemIndex = -1 BindGrid( ) End Sub End Class
To get
started with this exercise,
you’ll create an .aspx
page
with just a single DataGrid control:
<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" CellPadding="5" HeaderStyle-BackColor="PapayaWhip" BorderWidth="5px" BorderColor="#000099" AlternatingItemStyle-BackColor="LightGrey" HeaderStyle-Font-Bold EditItemStyle-BackColor="Yellow" EditItemStyle-ForeColor="Black" DataKeyField ="BugID"OnEditCommand="OnEdit"
OnCancelCommand="OnCancel"
OnUpdateCommand="OnUpdate">
Once again, you will set
AutoGenerateColumns
to false
. As you have in
the past, you must set
DataKeyField
to BugID
so that
the data grid can keep track of the primary key for each record on your
behalf. You’ll see how this comes in handy when
we’re ready to update the database with the edits
you’ll make.
There are three new attributes:
OnEditCommand
,
OnCancelCommand
, and
OnUpdateCommand
. These are used to wire up the event
handlers for the Edit, Cancel, and Update events, which are fired in
response to clicking on the Edit, Cancel, and Update links,
respectively.
You create these links by adding an
EditCommandColumn
tag to your data grid:
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Save" />
The EditText
is displayed when
not in edit mode. Clicking on that link redraws the data grid in edit
mode and displays the links with the text set in
CancelText
and
UpdateText
.
All that is left is to add the columns for each field
you’ll display in the data grid. You have a few
choices. For straightforward text, you can use a normal
BoundColumn
tag:
<asp:BoundColumn DataField="Description" HeaderText="Description" />
For columns that you do not want to be edited, you can add the
ReadOnly
attribute:
<asp:BoundColumn DataField="BugID" HeaderText="ID" ReadOnly="True" />
For some columns, you will want to take control of how the item is
displayed in normal and in edit mode. For example, the Version string
is quite small, and you might want to control the size of the text
box used when editing the data. To accomplish this, you will add a
TemplateColumn
tag. Within the
TemplateColumn
tag, you will add an
ItemTemplate
to control the display
in normal mode, and an
EditItemTemplate
to control the display
in edit mode:
<asp:TemplateColumn HeaderText="Version"> <ItemTemplate> <asp:Label Text='<%# Convert.ToString(DataBinder.Eval(Container.DataItem,"Version")) %>' Runat="server" ID="lblVersion"/> </ItemTemplate> <EditItemTemplate> <asp:TextBox Runat="server" ID="txtVersion" Text = '<%# Convert.ToString(DataBinder.Eval(Container.DataItem,"Version")) %>' Width="30" /> </EditItemTemplate> </asp:TemplateColumn>
This is very similar to the way you created template columns in the
previous section, except that in this example, you add the new
EditItemTemplate
. In this code shown here you
display a text box when you enter edit mode. The text box is
initialized with the current value of the Version field.
For the Product and Reporter fields, you want to provide a drop-down with the legal values for each field. To facilitate this, you’ll create a code-behind method, GetValues, which takes the name of a table and returns a DataReader object filled with the values in that table:
public System.Data.SqlClient.SqlDataReader GetValues(string tableName) { // connect to the Bugs database string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs"; // create and open the connection object System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // get records from the Bugs table string commandString = "Select * from " + tableName; // create the command object and set its // command string and connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.CommandText = commandString; command.Connection = connection; // create the DataReader and return it return command.ExecuteReader(CommandBehavior.CloseConnection); }
There is nothing surprising about this method. What is new is that
you will assign this method to the Product
EditItemTemplate
declaratively. You will also set
the DataTextField
to determine what value will be
displayed in the dropdown, and you’ll set the
DataValueField
to determine
the value held in the list box for each selection:
<asp:TemplateColumn HeaderText="Product"> <ItemTemplate> <asp:Label Text='<%# Convert.ToString(DataBinder.Eval(Container.DataItem,"ProductDescription")) %>' Runat="server" ID="lblProduct"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList Runat="server" ID="editProduct"DataSource='<%# GetValues("lkProduct") %>'
DataTextField ="ProductDescription"
DataValueField ="ProductID"
Width ="200" /> </EditItemTemplate> </asp:TemplateColumn>
When not in edit mode, the ItemTemplate
is used to
display the asp:Label
with the current value taken
from the ProductDescription field returned by the Sql query used to
fill the data grid. When in edit mode, however, the
asp:ListBox
is populated by binding to the
DataReader returned by GetValues, and the Text and Value fields are
bound based on the attributes shown.
The Reported By column is built in exactly the same way. Once again,
you call GetValues, except that this time you pass in the name of the
People table. You bind the DataTextField
to the
FullName field and the DataValueField
to the
PersonID field:
<asp:TemplateColumn HeaderText="Reported By"> <ItemTemplate> <asp:Label Text='<%# Convert.ToString(DataBinder.Eval(Container.DataItem,"FullName")) %>' ID="lblReported" Runat="server"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList Runat="server" ID="editReporter" DataSource='<%# GetValues("People") %>' DataTextField ="FullName" DataValueField ="PersonID" Width ="200" /> </EditItemTemplate> </asp:TemplateColumn>
With the .aspx file in hand and the GetValues method working, you are able to display the items and enter edit mode. All that remains is to write the event handlers.
The data grid is drawn in normal or edit mode based on the value of
the EditItemIndex
property of the DataGrid
control. Setting this zero-based property to a value other than
-1
enables editing controls for that item.
When the user clicks the Edit link on an item, the OnEditCommand event handler is called. At this point, you have an opportunity to intercept the event and redirect editing in any way you want. You could, for example, check permissions to see if the user is allowed to edit the item and cancel editing if it should not be permitted. The simplest and most common thing to do, however, is simply to set the DataGrid control’s EditItemIndex property to the ItemIndex property of the data grid item that was selected, and rebind the data grid, as shown in the following event handler:
public void OnEdit(Object source, DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = e.Item.ItemIndex; BindGrid( ); }
The BindGrid method is the same method you called originally to populate the data grid:
private void BindGrid( ) { // connect to the Bugs database string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs"; System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // get records from the Bugs table string commandString = "Select b.BugID, b.Version, b.Description, p.ProductDescription,"; commandString += "peo.FullName from Bugs b "; commandString += "join lkProduct p on b.Product = p.ProductID "; commandString += "join People peo on b.Reporter = peo.PersonID "; System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.CommandText = commandString; command.Connection = connection; SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); DataGrid1.DataSource=reader; DataGrid1.DataBind( ); }
By rebinding the data grid with the EditItemIndex property set to the selected item (the row in the data grid), that row will be displayed in edit mode.
If the user clicks Cancel, you can reset the EditItemIndex property to -1 and rebind the data grid:
public void OnCancel(Object source, DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = -1; BindGrid( ); }
The data grid will be redrawn in normal mode.
The OnUpdateCommand event handler is where all the real action is. Here you want to extract the new values from each of your widgets and then update the database accordingly.
There are two approaches to extracting the data. If
you’ve used a bound control, as you have in this
example with the Version value, you can access the control directly
through the Item property of the
DataGridCommandEventArgs
parameter.
The Item property is of type DataGridItem. Every DataGridItem object has a Cells collection that it inherits from the TableRow class. You can offset into that collection to extract the Controls collection from the appropriate cell. The first control in that collection will be the text box. The Controls collection returns an object of type Control, so you must cast it to TextBox. Once cast to TextBox, you can access the Text property, which is a string:
string newDescription = ((TextBox) e.Item.Cells[1].Controls[0]).Text;
In VB.NET, this is done with a call to the
CType
function:
Dim newDescription As String = CType(e.Item.Cells(1).Controls(0), _ TextBox).Text
The alternative method for extracting the data in your edit controls
is to use the
FindControl
method of the Item
. You pass in the name of the
control and get back an object of type Control, which again you will
cast:
string newVersion = ((TextBox)(e.Item.FindControl("txtVersion"))).Text;
In VB.NET, this would be done using:
Dim newVersion As String = CType(e.Item.FindControl("txtVersion"), TextBox).Text
When extracting the value from the drop-down listboxes, you will need to cast the Control object to a DropDownList object and then access the SelectedItem property, which returns a ListItem object. You can then access the Text property on that ListItem object to get the text displayed in the selected list item, or, in this case, you can access the Value property. The code to do this in C# is:
string PersonID = ((DropDownList)(e.Item.FindControl("editReporter"))). SelectedItem.Value; string ProductID = ((DropDownList)(e.Item.FindControl("editProduct"))). SelectedItem.Value;
The relationship between the Text and the Value properties of the
listbox and the values displayed and retrieved is established
declaratively in the template with the
DataTextField
and
DataValueField
attributes,
respectively.
In VB.NET, the code is:
Dim PersonID As String = CType(e.Item.FindControl("editReporter"), _ DropDownList).SelectedItem.Value Dim ProductID As String = CType(e.Item.FindControl("editProduct"), _ DropDownList).SelectedItem.Value
With the values retrieved from the controls, you are ready to
formulate an Update
statement and update the
database:
string cmd = "Update Bugs set Product = " + ProductID + ", Version = '" + newVersion + "', Description = '" + newDescription + " ', Reporter = " + PersonID + " where BugID = " + DataGrid1.DataKeys[e.Item.ItemIndex];
Notice that the Where
clause includes the BugID
from the current record. You obtain this by indexing the selected
item index into the data grid’s
DataKeys collection. This, finally,
is why you set the
DataKeyField
attribute on the data grid.
To invoke this SQL command you create the connection string and the
connection object and use both to set the properties of a
SqlCommand
object on which you invoke the
ExecuteNonQuery
method:
// connect to the Bugs database string connectionString = "server=YourServerYourServer; uid=sa; pwd=YourPWYourPW; database=ProgASPDotNetBugs"; System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.CommandText = cmd; command.Connection = connection; command.ExecuteNonQuery( );
All that remains is to return to non-edit mode and rebind the data grid with the new values from the newly updated database:
DataGrid1.EditItemIndex = -1; BindGrid( );
18.118.138.195