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
figs/pan2_1304.gif

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
figs/pan2_1305.gif

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"  />             <asp:Button CommandName ="Delete" Text="Delete"  Runat="server" />          <asp:Button CommandName ="Cancel" Text="Cancel"  Runat="server" />

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"                            Text = '<%# Convert.ToString(             DataBinder.Eval(Container.DataItem,"Description")) %>'              Width="300"             />       <br> <asp:DropDownList              Runat="server"                           DataSource='<%# GetValues("lkProduct") %>'             DataTextField ="ProductDescription"             DataValueField ="ProductID"             Width ="300" />    <br> <asp:DropDownList              Runat="server"                           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


Programming ASP. NET
Programming ASP.NET 3.5
ISBN: 0596529562
EAN: 2147483647
Year: 2003
Pages: 156

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net