Enabling Editing Using the DataGrid
DataGrid"-->
| only for RuBoard |
Enabling Editing Using the DataGrid
Chapter 7, "Working with ASP.NET Server Controls Templates," discussed working with templates and columns, and you learned what bound columns are. The EditCommandColumn is one of these columns; it's a single row within a DataGrid and by default doesn't contain any data from your data source. Listing 10.1 shows how to insert the EditCommandColumn in your DataGrid .
Listing 10.1 Inserting the EditCommandColumn
01: <asp:DataGrid 02: id="DGProducts" 03: runat="server" 04: AutoGenerateColumns="true" 05: OnEditCommand="DGProducts_Edit" 06: OnCancelCommand="DGProducts_Cancel" 07: OnUpdateCommand="DGProducts_Update" 08: OnDeleteCommand="DGProducts_Delete" 09: > 10: <Columns> 11: <asp:EditCommandColumn 12: ButtonType="LinkButton" 13: CancelText="Cancel" 14: EditText="Edit" 15: UpdateText="Update" 16: Visible="true" 17: /> 18: </columns> 19: </asp:DataGrid>
Notice that the EditCommandColumn isn't bound to any field from the data source (lines 11 “17), but instead is bound to the row as a whole from the data source. This means you can use the DataGrid in any way you want even when you enable editing. For example, you can set AutoGenerateColumns to false and create a custom layout for the DataGrid . You also can enable paging and/or sorting without having to change any code within the EditCommandColumn .
The EditCommandColumn contains all the styling information for edit mode, but it doesn't contain information about server events. (The use of event handlers will be discussed later in this chapter.)
One attribute from the EditCommandColumn I would like to discuss is the ButtonType attribute, which is used to determine what type of button should be used for the column for the user to click on. You have the choice of two types of buttons :
-
LinkButton ” The button comes in the form of a hyperlink.
-
PushButton ” The button comes in the form of an HTML input button.
Tip
When you use the LinkButton as the ButtonType , you can use an image for the button with the following code:
ButtonType="LinkButton" EditText="<img src='/images/edit.gif' border='0'>"
The image will be placed between the <a href=""></a> tags.
You set the text for the three buttons by using the following attributes:
-
EditText ” Attribute used to set the text for the Edit button.
-
UpdateText ” Attribute used to set the text of the Update button.
-
CancelText ” Attribute used to set the text of the Cancel button.
Listing 10.2 shows how to use the DataGrid with editing enabled. After this example, we will dissect the code.
Listing 10.2 Enabling Editing in the DataGrid
[Visual Basic] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: <script language="vb" runat="server"> 04: private sSqlCon as string = "server=localhost;uid=sa;pwd=;database=northwind" 05: 06: public sub Page_Load(sender as Object, e as EventArgs) 07: 08: if not IsPostBack then 09: Bind() 10: end if 11: 12: end sub 13: 14: private sub Bind() 15: 16: dim SqlCon as new SqlConnection(sSqlCon) 17: dim SqlCmd as new StringBuilder() 18: SqlCmd.Append("SELECT ProductName,UnitPrice,") 19: SqlCmd.Append("UnitsInStock,UnitsOnOrder,") 20: SqlCmd.Append("ProductID FROM Products") 21: dim sda as new SqlDataAdapter(SqlCmd.ToString(),SqlCon) 22: dim ds as new DataSet() 23: sda.Fill(ds,"products") 24: DGProducts.DataSource = ds.Tables("Products").DefaultView 25: DGProducts.DataBind() 26: 27: end sub 28: 29: public sub DGProducts_Edit(sender as Object, e as DataGridCommandEventArgs) 30: 31: DGProducts.EditItemIndex = e.Item.ItemIndex 32: Bind() 33: 34: end sub 35: 36: public sub DGProducts_Cancel(sender as Object, e as DataGridCommandEventArgs) 37: 38: DGProducts.EditItemIndex = -1 39: Bind() 40: 41: end sub 42: 43: public sub DGProducts_Update(sender as Object, e as DataGridCommandEventArgs) 44: 45: dim sProductName,sUnitsInStock,sUnitsOnOrder,sProductID as string 46: sProductName = CType(e.Item.Cells(1).Controls(0), TextBox).Text 47: sUnitsInStock = CType(e.Item.Cells(2).Controls(0), TextBox).Text 48: sUnitsOnOrder = CType(e.Item.Cells(3).Controls(0), TextBox).Text 49: sProductID = e.Item.Cells(5). Text 50: 51: dim sSqlCmd as new StringBuilder() 52: sSqlCmd.Append("UPDATE Products ") 53: sSqlCmd.Append("SET ProductName = @ProductName,") 54: sSqlCmd.Append("UnitsInStock = @UnitsInStock,") 55: sSqlCmd.Append("UnitsOnOrder = @UnitsOnOrder ") 56: sSqlCmd.Append("WHERE ProductID = @ProductID") 57: 58: dim SqlCon as new SqlConnection(sSqlCon) 59: Dim SqlCmd as new SqlCommand(sSqlCmd.ToString(),SqlCon) 60: SqlCmd.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.NVarChar, 40)) 61: SqlCmd.Parameters("@ProductName").Value = sProductName 62: SqlCmd.Parameters.Add(new SqlParameter("@UnitsInStock", SqlDbType.SmallInt)) 63: SqlCmd.Parameters("@UnitsInStock").Value = sUnitsInStock 64: SqlCmd.Parameters.Add(new SqlParameter("@UnitsOnOrder", SqlDbType.SmallInt)) 65: SqlCmd.Parameters("@UnitsOnOrder").Value = sUnitsOnOrder 66: SqlCmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.SmallInt)) 67: SqlCmd.Parameters("@ProductID").Value = sProductID 68: 69: SqlCon.Open() 70: SqlCmd.ExecuteNonQuery() 71: SqlCon.Close() 72: 73: DGProducts.EditItemIndex = -1 74: Bind() 75: 76: end sub 77: 78: </script> 79: <html> 80: <body> 81: <head> 82: 83: <style rel="stylesheet"> 84: H3 { font: bold 11pt Verdana, Arial, sans-serif; } 85: .products { font: 9pt Verdana, Arial, sans-serif; } 86: .productsHead { font: bold 9pt Verdana, Arial, sans-serif; 87: background-color:Maroon; color:white; } 88: a { text-decoration:none; } 89: a:hover { text-decoration:underline; color:maroon; } 90: </style> 91: 92: </head> 93: <form runat="server"> 94: <H3>Northwind Inventory Management - VisualBasic.NET</H3> 95: 96: <asp:DataGrid 97: id="DGProducts" 98: runat="server" 99: Cellpadding="4" Cellspacing="0" Width="750" 100: BorderWidth="1" Gridlines="None" 101: AlternatingItemStyle-BackColor="Tan" 102: HeaderStyle-CssClass="productsHead" 103: Font-Size="12" 104: AutoGenerateColumns="false" 105: OnEditCommand="DGProducts_Edit" 106: OnCancelCommand="DGProducts_Cancel" 107: OnUpdateCommand="DGProducts_Update" 108: > 109: <Columns> 110: <asp:EditCommandColumn 111: ButtonType="LinkButton" 112: CancelText="Cancel" 113: EditText="Edit" 114: UpdateText="Update" 115: /> 116: <asp:BoundColumn DataField="ProductName" HeaderText="Product Name" /> 117: <asp:BoundColumn DataField="UnitsInStock" HeaderText="Units in Stock" /> 118: <asp:BoundColumn DataField="UnitsOnOrder" HeaderText="Units on Order" /> 119: <asp:BoundColumn DataField="UnitPrice" HeaderText="Price Per Unit" 120: DataFormatString="{ 0:C} " ReadOnly="true" /> 121: <asp:BoundColumn DataField="ProductID" 122: HeaderText="Product ID" ReadOnly="true" /> 123: </columns> 124: </asp:DataGrid> 125: </form> 126: </body> 127: </html> [C#Replace server code] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: <script language="c#" runat="server"> 04: private string sSqlCon = "server=localhost;uid=sa;pwd=;database=northwind"; 05: 06: public void Page_Load(Object sender, EventArgs e){ 07: 08: if (! IsPostBack){ 09: Bind(); 10: } 11: 12: } 13: 14: private void Bind() { 15: 16: SqlConnection SqlCon = new SqlConnection(sSqlCon); 17: StringBuilder SqlCmd = new StringBuilder(); 18: SqlCmd.Append("SELECT ProductName,UnitPrice,"); 19: SqlCmd.Append("UnitsInStock,UnitsOnOrder,"); 20: SqlCmd.Append("ProductID FROM Products"); 21: SqlDataAdapter sda = new SqlDataAdapter(SqlCmd.ToString(),SqlCon); 22: DataSet ds = new DataSet(); 23: sda.Fill(ds,"products"); 24: DGProducts.DataSource = ds.Tables["Products"].DefaultView; 25: DGProducts.DataBind(); 26: 27: } 28: 29: public void DGProducts_Edit(Object sender, DataGridCommandEventArgs e) { 30: 31: DGProducts.EditItemIndex = e.Item.ItemIndex; 32: Bind(); 33: 34: } 35: 36: public void DGProducts_Cancel(Object sender, DataGridCommandEventArgs e) { 37: 38: DGProducts.EditItemIndex = -1; 39: Bind(); 40: 41: } 42: 43: public void DGProducts_Update(Object sender, DataGridCommandEventArgs e) { 44: 45: string sProductName,sUnitsInStock,sUnitsOnOrder,sProductID; 46: sProductName = ((TextBox)e.Item.Cells[1].Controls[0]).Text; 47: sUnitsInStock = ((TextBox)e.Item.Cells[2].Controls[0]).Text; 48: sUnitsOnOrder = ((TextBox)e.Item.Cells[3].Controls[0]).Text; 49: sProductID = e.Item.Cells[5]. Text; 50: 51: StringBuilder sSqlCmd = new StringBuilder(); 52: sSqlCmd.Append("UPDATE Products "); 53: sSqlCmd.Append("SET ProductName = @ProductName,"); 54: sSqlCmd.Append("UnitsInStock = @UnitsInStock,"); 55: sSqlCmd.Append("UnitsOnOrder = @UnitsOnOrder "); 56: sSqlCmd.Append("WHERE ProductID = @ProductID"); 57: 58: SqlConnection SqlCon = new SqlConnection(sSqlCon); 59: SqlCommand SqlCmd = new SqlCommand(sSqlCmd.ToString(),SqlCon); 60: SqlCmd.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.NVarChar, 40)); 61: SqlCmd.Parameters["@ProductName"].Value = sProductName; 62: SqlCmd.Parameters.Add(new SqlParameter("@UnitsInStock", SqlDbType.SmallInt)); 63: SqlCmd.Parameters["@UnitsInStock"].Value = sUnitsInStock; 64: SqlCmd.Parameters.Add(new SqlParameter("@UnitsOnOrder", SqlDbType.SmallInt)); 65: SqlCmd.Parameters["@UnitsOnOrder"].Value = sUnitsOnOrder; 66: SqlCmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.SmallInt)); 67: SqlCmd.Parameters["@ProductID"].Value = sProductID; 68: 69: SqlCon.Open(); 70: SqlCmd.ExecuteNonQuery(); 71: SqlCon.Close(); 72: 73: DGProducts.EditItemIndex = -1; 74: Bind(); 75: 76: } 77: 78: </script> We will go over Listing 10.2 in detail in the following three sections. The first section, " Putting the DataGrid into Edit Mode" explains how to configure the DataGrid to support editing. The next section, "Updating Data with the DataGrid ," explains how to update your database with the edited data. Finally, " Cancelling out of Edit Mode," explains how to cancel out of edit mode without saving the edited data back to the database.
Putting the DataGrid into Edit Mode
Going through Listing 10.2 you'll notice that putting the DataGrid into edit mode isn't as simple as setting one attribute to true or false. You must tell the DataGrid that you want it to go into edit mode and handle certain events appropriately. First, you need to set the OnEditCommand attribute of the DataGrid (line 105). As previously mentioned the OnEditCommand method raises the EditCommand event which will be handled by the event named as the value of the OnEditCommand attribute. This brings us to the second step: handling the EditCommand event. This code is found on lines 29 “34; the method name is DGProducts_Edit . The event handler expects two parameters; Object and DataGridCommandEventArgs .
The DataGridCommandEventArgs object contains all the information you need to retrieve information about what raised the event. The following list contains all the properties contained in the DataGridCommandEventArgs :
-
Item ” Gets the selected item (the row within the DataGrid that the event was raised by) (see DataGridItem class for more detail).
-
CommandArgument ” Gets the argument for the command.
-
CommandName ” Gets the name of the command.
-
CommandSource ” Gets the source of the command.
Once you have handled the EditCommand event, you will need to use an attribute of the Item property to figure out which row of the DataGrid you want to put into edit mode. You do this by setting the DataGrid 's EditItemIndex attribute to be equal to the index of the item that raised the event. This code can be found on line 31 and you use the Item objects ItemIndex property to retrieve it. Once in edit mode, you need to re-bind the DataGrid to its data source (line 32). Any columns within the DataGrid that are visible and are not read-only will contain TextBox's with the original values in them ”very similar to Figure 10.1.
Figure 10.1. The DataGrid in edit mode ”TextBox controls are used to edit data.
You can use the ReadOnly Attribute to disable editing of certain fields in the DataGrid as I did for the ProductID column on line 121. This attribute is set to false by default, and a text box will appear for every field from the data source. To disable a certain field, set this attribute to true.
Now that some of the DataGrid 's columns can be changed we need to save these changes back to our database. In the next section we will be illustrating how to handle these updates using the UpdateCommand .
Updating Data with the DataGrid
Updating data with the DataGrid requires a little more work than putting the DataGrid into edit mode. The first thing we have to do is set up the DataGrid to allow updating. You can accomplish this by setting the OnUpdateCommand attribute to the name of the method that will handle the event (line 107). The method you use to handle the event expects the same two arguments as the DataGrid_Edit from the previous section. In this example, the name for this method is DGProducts_Update (lines 43 “76). This is the method you want to use to gain access to the edited items within the DataGrid , validate it, and save it back to the database.
Retrieving the edited data is a bit tricky because the entire row from the DataGrid is contained within the Item object. Because the edited data is located in controls (ex: TextBox ) that are in turn located within table cells we have to drill down through the Item object to each individual control; first through the cells collection and then through the controls collection within each cell . This is done in lines 45 “49. First, on line 45 I create 4 variables that I'll use to hold the values of the edited item(s). In lines 46 “49 I assign values to each variable by accessing the Text property of the first control within the cells that are allowed to be edited. On line 49 I need to only access the Cell 's Text attribute since the ProductID is not contained within a control but rather within the Cell itself.
Next I construct my SQL UPDATE statement and update the backend database (lines 51 “71). On line 73 the DataGrid is taken out of edit mode by setting its EditItemIndex to -1 and finally the DataGrid is re-bound (line 74). In the next section we will demonstrate how to take the DataGrid out of edit mode without saving changes back to the database.
Figure 10.2 is the DataGrid after I edited the row containing Chef Anton's Cajun Seasoning to Don Mack's Cajun Seasoning .
Figure 10.2. Edited DataGrid ”Chef Anton's Cajun Seasoning has been changed to Don Mack's Cajun Seasoning.
Cancelling Out of Edit Mode
Cancelling out of edit mode is a very simple task. You enable this feature by setting the OnCancelCommand attribute equal to what you're going to use to handle the event (line 106). Next you need to create the event handler (lines 36 “41). When you're cancelling out of edit mode, you need only set its EditItemIndex attribute to -1 .
| only for RuBoard |
EAN: 2147483647
Pages: 170