Programming ASP.NET

Javascript validator Javascripts

Main Page

↑

13.5 DataList Editing

The DataList control also provides extensive support for in-place editing. In the next example, you'll modify the data list you built earlier to display Bugs, but this time you'll add in-place editing.

The same control over look and feel that the data list provides through templates can be extended to the look and feel of the editing process. In the next example, you'll create a data list with two columns of data. Each record will include an Edit button to put your grid into edit mode for that record, as shown in Figure 13-4.

Figure 13-4. The DataList with Edit buttons

When the user presses the Edit button the EditItemTemplate tag will dictate the exact look and feel of the editing user interface, as shown in Figure 13-5.

Figure 13-5. The DataList in edit mode

To accomplish this, you'll create an .aspx file with a single data list. You'll add attributes to set the edit, cancel, and update commands, and this time you'll also add an attribute for the delete command. The DataList tag appears as follows:

<asp:DataList
id ="DataList1"
runat="server" 
CellPadding="5" 
HeaderStyle-BackColor="PapayaWhip" 
BorderWidth="5px" 
BorderColor="#000099" 
AlternatingItemStyle-BackColor="LightGrey" 
HeaderStyle-Font-Bold
EditItemStyle-BackColor="Yellow"
EditItemStyle-ForeColor="Black"
RepeatColumns="2"
RepeatDirection="Vertical"
DataKeyField ="BugID"
OnEditCommand="OnEdit"
OnDeleteCommand="OnDelete"
OnCancelCommand="OnCancel"
OnUpdateCommand="OnUpdate">

Within the DataList definition, you'll add templates for the header, items, edititems, separator, and footer.

The Header, Separator, and Footer are unchanged from the previous example. The ItemTemplate is also unchanged, except for the addition of a Button object before the <div> that holds the other elements:

<ItemTemplate>
<asp:Button CommandName="Edit" Text="Edit" Runat="server" />
   <div class ="item">
<b>Bug: </b> 
  <%# Convert.ToString(
       DataBinder.Eval(Container.DataItem, "BugID")) %> <br />
<b>Description: </b> 
  <%# Convert.ToString(
      DataBinder.Eval(Container.DataItem,"Description")) %> <br />
<b>Product: </b>
  <%# Convert.ToString(
      DataBinder.Eval(Container.DataItem,"ProductDescription")) %><br />
<b>Reported by: </b> </b>
  <%# Convert.ToString(
      DataBinder.Eval(Container.DataItem,"FullName")) %>
  </div>
</ItemTemplate>

So far, not much change from the previous data list. The one new element will be the EditItemTemplate tag, which (no surprise) will be used to draw the data list item when it is in edit mode.

You begin with the EditItemTemplate element. You will then add text to show the BugID. For example:

<EditItemTemplate>
<b>Bug: </b> 
<%# Convert.ToString(
       DataBinder.Eval(Container.DataItem, "BugID")) %> <br />

With the BugID displayed, you want to place the three Buttons:

<asp:Button CommandName ="Update" Text="Update" 
Runat="server" ID="btnUpdate" />            

<asp:Button CommandName ="Delete" Text="Delete" 
Runat="server" ID="btnDelete"/>         

<asp:Button CommandName ="Cancel" Text="Cancel" 
Runat="server" ID="btnCancel"/>

After the buttons, you'll add a break so that the edit boxes are each on their own line. The attributes for the TextBox and DropDownLists are obtained directly from the previous example, as is the supporting GetValues method:

   <br>
   
<asp:TextBox 
            Runat="server" 
            ID="txtDescription" 
            Text = '<%# Convert.ToString(
            DataBinder.Eval(Container.DataItem,"Description")) %>' 
            Width="300"
            />   
   <br>
<asp:DropDownList 
            Runat="server" 
            ID="editProduct"
            DataSource='<%# GetValues("lkProduct") %>'
            DataTextField ="ProductDescription"
            DataValueField ="ProductID"
            Width ="300" />
   <br>
<asp:DropDownList 
            Runat="server" 
            ID="editReporter"
            DataSource='<%# GetValues("People") %>'
            DataTextField ="FullName"
            DataValueField ="PersonID"
            Width ="300" />
            
   <br>                        
            </EditItemTemplate>
</asp:DataList>

All that is left is to implement the event handlers. The Cancel and Edit events are nearly identical to the previous example:

public void OnEdit(Object source, DataListCommandEventArgs e)
{
   DataList1.EditItemIndex = e.Item.ItemIndex;
   BindGrid(  );
}     

public void OnCancel(Object source, DataListCommandEventArgs e)
{
   DataList1.EditItemIndex = -1;
   BindGrid(  );
}
Note: the VB.NET code is identical except for the semicolons.

The only change is to the type of the second argument, now set to DataListCommandEventArgs.

Both the Update and the Delete event handlers will need to invoke a SQL statement, so you'll factor out the common code into a helper routine, ExecuteQuery:

private int ExecuteQuery(string sqlCmd)
{
   // 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 = sqlCmd;
   command.Connection = connection;
   return command.ExecuteNonQuery(  );
}

In VB.NET, the code is:

Private Function ExecuteQuery(ByVal sqlCmd As String) As Integer
   ' connect to the Bugs database
   Dim connectionString As String = "server=YourServer uid=sa " & _
      "pwd=YourPassword database=ProgASPDotNetBugs"

   Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString)
   myConnection.Open(  )

   ' call the update and rebind the datagrid
   Dim myCommand As New System.Data.SqlClient.SqlCommand(  )
   myCommand.CommandText = sqlCmd
   myCommand.Connection = myConnection
   Return myCommand.ExecuteNonQuery(  )
End Function

The Update statement is also very similar to that used in the previous example:

public void OnUpdate(Object source, DataListCommandEventArgs e)
{
   
   string PersonID =  
      ((DropDownList)(e.Item.FindControl("editReporter"))).
      SelectedItem.Value;

   string newDescription = 
      ((TextBox)(e.Item.FindControl("txtDescription"))).Text;

   string ProductID = 
      ((DropDownList)(e.Item.FindControl("editProduct"))).
      SelectedItem.Value;

   // form the update statement
   string cmd = "Update Bugs set Product = " + ProductID + 
      ", Description = '" + newDescription + 
      " ', Reporter = " + PersonID +
      " where BugID = " + DataList1.DataKeys[e.Item.ItemIndex];

   ExecuteQuery(cmd);
   DataList1.EditItemIndex = -1;
   BindGrid(  );

}

In VB.NET, the code is:

Public Sub OnUpdate( _
    ByVal source As Object, ByVal e As DataListCommandEventArgs)
   Dim PersonID As String = CType(e.Item.FindControl( _
                  "editReporter"), DropDownList).SelectedItem.Value

   Dim newDescription As String = _
        CType(e.Item.FindControl("txtDescription"), _
                                  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 = " & DataList1.DataKeys(e.Item.ItemIndex)

   ExecuteQuery(cmd)
   DataList1.EditItemIndex = -1
   BindGrid(  )
End Sub

Notice that once again you use the DataKeys collection (this time of the DataList control) to retrieve the BugID. This depends on your setting the DataKeyField attribute of the DataList.

Finally, you add a delete command event handler that forms the SQL statement to delete the current record:

public void OnDelete(Object source, DataListCommandEventArgs e)
{
   
   string cmd = "Delete from Bugs where BugID = " + 
      DataList1.DataKeys[e.Item.ItemIndex];
   int rowsDeleted = ExecuteQuery(cmd);

   DataList1.EditItemIndex = -1;
   BindGrid(  );
}

In VB.NET, the code is:

Public Sub OnDelete( _
   ByVal source As Object, ByVal e As DataListCommandEventArgs)
   Dim cmd As String = "Delete from Bugs where BugID = " & _
         DataList1.DataKeys(e.Item.ItemIndex)
   DataList1.EditItemIndex = -1
   BindGrid(  )
End Sub

The complete C# listing is shown in Example 13-9 and the VB.NET code in Example 13-10.

Example 13-9. Using the DataList to edit in place (C#)

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 BugHistoryInPlaceDataListEdit
{
   public class WebForm1 : System.Web.UI.Page
   {
      protected System.Web.UI.WebControls.DataList DataList1;
      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=YourPW; 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, 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);
         DataList1.DataSource=reader;
         DataList1.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=YourPW; 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, DataListCommandEventArgs e)
      {
         DataList1.EditItemIndex = e.Item.ItemIndex;
         BindGrid(  );
      }     

      private int ExecuteQuery(string sqlCmd)
      {
         // connect to the Bugs database
         string connectionString = 
            "server=YourServer; uid=sa; pwd=YourPW; 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 = sqlCmd;
         command.Connection = connection;
         return command.ExecuteNonQuery(  );
      }

      public void OnDelete(Object source, DataListCommandEventArgs e)
      {
         
         string cmd = "Delete from Bugs where BugID = " + 
            DataList1.DataKeys[e.Item.ItemIndex];
         int rowsDeleted = ExecuteQuery(cmd);

         DataList1.EditItemIndex = -1;
         BindGrid(  );
      }     

      // Handle the cancel event - set the EditItemIndex to -1
      public void OnCancel(Object source, DataListCommandEventArgs e)
      {
         DataList1.EditItemIndex = -1;
         BindGrid(  );

      }

      // Handle the Update event
      // Extract the new values
      // Update the database and rebind the datagrid
      public void OnUpdate(Object source, DataListCommandEventArgs e)
      {
         
         string PersonID =  
            ((DropDownList)(e.Item.FindControl("editReporter"))).
            SelectedItem.Value;

         string newDescription = 
            ((TextBox)(e.Item.FindControl("txtDescription"))).Text;

         string ProductID = 
            ((DropDownList)(e.Item.FindControl("editProduct"))).
            SelectedItem.Value;

         // form the update statement
         string cmd = "Update Bugs set Product = " + ProductID + 
            ", Description = '" + newDescription + 
            " ', Reporter = " + PersonID +
            " where BugID = " + DataList1.DataKeys[e.Item.ItemIndex];

         ExecuteQuery(cmd);
         DataList1.EditItemIndex = -1;
         BindGrid(  );
 
      }

      private void Page_Init(object sender, EventArgs e)
      {
         //
         // CODEGEN: This call is required by the ASP.NET Web Form Designer.
         //
         InitializeComponent(  );
      }

        #region Web Form Designer generated code
      /// <summary>
      /// Required method for Designer support - do not modify
      /// the contents of this method with the code editor.
      /// </summary>
      private void InitializeComponent(  )
      {    
         this.Load += new System.EventHandler(this.Page_Load);

      }
        #endregion
   }
}

Example 13-10. Using the DataList to edit in place (VB.NET)

Imports System.Data.SqlClient

Public Class WebForm1
   Inherits System.Web.UI.Page

   Protected WithEvents DataList1 As System.Web.UI.WebControls.DataList
   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=YourPW; " & _
          "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)
      DataList1.DataSource = reader
      DataList1.DataBind(  )
   End Sub

   ' Given the name of a table, return a DataReader for
   ' all values from that table
   Public Function GetValues(ByVal 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 DataListCommandEventArgs)
      DataList1.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 DataListCommandEventArgs)
      DataList1.EditItemIndex = -1
      BindGrid(  )
   End Sub

   ' Handle the delete event 
   Public Sub OnDelete(ByVal source As Object, ByVal e As DataListCommandEventArgs)
      Dim cmd As String = "Delete from Bugs where BugID = " & _
            DataList1.DataKeys(e.Item.ItemIndex)
      DataList1.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 DataListCommandEventArgs)
      Dim PersonID As String = CType(e.Item.FindControl( _
                     "editReporter"), DropDownList).SelectedItem.Value

      Dim newDescription As String = CType(e.Item.FindControl("txtDescription"), _
                                     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 = " & DataList1.DataKeys(e.Item.ItemIndex)

      ExecuteQuery(cmd)
      DataList1.EditItemIndex = -1
      BindGrid(  )
   End Sub
   Private Function ExecuteQuery(ByVal sqlCmd As String) As Int32
      ' connect to the Bugs database
      Dim connectionString As String = _
            "server=YourServer; uid=sa; pwd=YourPW;" & _
            "database=ProgASPDotNetBugs"

      Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString)
      myConnection.Open(  )

      ' call the update and rebind the datagrid
      Dim myCommand As New System.Data.SqlClient.SqlCommand(  )
      myCommand.CommandText = sqlCmd
      myCommand.Connection = myConnection
      Return myCommand.ExecuteNonQuery(  )
   End Function
End Class