You now finally have all the necessary ingredients to create a web page with in-place editing:
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. Figure 13-3. Editing in place![]() 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 method="post" runat="server"> <asp:DataGrid 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" /> </ItemTemplate> <EditItemTemplate> <asp:TextBox Runat="server" 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" /> </ItemTemplate> <EditItemTemplate> <asp:DropDownList Runat="server" 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")) %>' Runat="server"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList Runat="server" 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> Example 13-7. The C# code-behind page for in-place editingusing 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 editingImports 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 13.4.1 Creating the EditTemplate ColumnsTo get started with this exercise, you'll create an .aspx page with just a single DataGrid control: <asp:DataGrid 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 Save 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" /> </ItemTemplate> <EditItemTemplate> <asp:TextBox Runat="server" 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 the 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); } Example 13-10 (at the end of this chapter) shows the corresponding VB .NET code. 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 drop-down, 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" /> </ItemTemplate> <EditItemTemplate> <asp:DropDownList Runat="server" 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")) %>' Runat="server"/> </ItemTemplate> <EditItemTemplate> <asp:DropDownList Runat="server" DataSource='<%# GetValues("People") %>' DataTextField ="FullName" DataValueField ="PersonID" Width ="200" /> </EditItemTemplate> </asp:TemplateColumn> 13.4.2 Implementing the Edit CycleWith 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. 13.4.2.1 Implementing the OnEditCommand event handlerWhen 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( ); } In VB .NET, this code is: Public Sub OnEdit(ByVal source As Object, _ ByVal e As DataListCommandEventArgs) DataList1.EditItemIndex = e.Item.ItemIndex BindGrid( ) End Sub 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( ); } Example 13-10 (at the end of this chapter) shows the corresponding VB .NET code. 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. 13.4.2.2 Implementing the OnCancelCommand event handlerIf 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( ); } In VB.NET, this code is: Public Sub OnCancel(ByVal source As Object, _ ByVal e As DataListCommandEventArgs) DataList1.EditItemIndex = -1 BindGrid( ) End Sub The data grid will be redrawn in normal mode. 13.4.2.3 Implementing the OnUpdateCommand event handlerThe 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;
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( ); In VB .NET, the code is this: // connect to the Bugs database Dim connectionString As String = _ "server=YourServerYourServer; uid=sa; pwd=YourPWYourPW" & _ "database=ProgASPDotNetBugs" Dim connection As New _ System.Data.SqlClient.SqlConnection(connectionString) connection.Open( ) Dim command As 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( ); |