Although DataGrids might not be the best place to edit your data, sometimes there are situations where it's more efficient to include the extra controls needed to edit the data rather than direct the user to a different page. Fortunately, Microsoft included the tools you need to edit your data directly in the DataGrid control itself. Over the next few DataStream articles, you'll learn how to use these controls in your own applications. For more information tips and tricks on DataGrid see, "Top 10 DataGrid Tips " and " DataGrid Magic ."
When defining columns for the DataGrid, you have the option to include ButtonColumns, which can be used to trigger events in your program. A ButtonColumn is exactly what it sounds like - a separate column in your DataGrid containing either a LinkButton or a PushButton.
You identify each ButtonColumn by assigning a unique value for the CommandName property. You control the text displayed on the button by specifying a value for the Text property or by identifying the name of the bound field, which would contain the text value to be displayed.
When the user clicks on the button, the ItemCommand event is fired, and the CommandName value and a reference to the current row are passed to the event. Based on this information, your event can perform the requested task on the row containing the button.
The DataGrid control defines some special ButtonColumns to support editing. For example, if you set CommandName to Delete, the DeleteCommand event is triggered rather than the ItemCommand event. These events have the same arguments as the ItemCommand event. Other special ButtonColumns include Cancel, Edit, and Update.
Delete a Row
The sample DataGrid program I've used in the last few DataStream articles forms the basis of this sample. In Figure 1, you can see that a column of push buttons, each labeled Delete, are displayed as the last column of the grid, while the Customer Id column is displayed as the first column in the grid.
Figure 1. Clicking on the Delete button displayed on the DataGrid will delete the entire row from the DataGrid.
You define the ButtonColumn in a DataGrid by including an
element similar to this one within the
When the user clicks on this button, the DeleteCommand event is fired (see Figure 2).
Sub DataGrid1_DeleteCommand(s As Object, _ e As DataGridCommandEventArgs) Dim ConnectionStr As String = _ ConfigurationSettings.AppSettings("ConnStr") Dim conn As SqlConnection Dim cmd As SqlCommand Dim Id As Integer Id = CInt(e.Item.Cells(0).Text) conn = New SqlConnection(ConnectionStr) cmd = New SqlCommand("DeleteCustomer", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = Id cmd.Connection.Open() cmd.ExecuteNonQuery() cmd.Connection.Close() RefreshData() End Sub
Figure 2. Pressing the Delete button in a DataGrid fires the DeleteCommand event, which is the logical place to delete a row of data from your DataGrid.
The DataGridCommandEventsArgs contains the name of the command (which in this case will always be Delete) as well as an object pointer to a DataGrid item, which contains information about the current row of the DataGrid including the contents of each cell in the row.
The DeleteComment (see Figure 2) event begins by declaring some local variables that will be used to access the database. Then it computes the CustomerId value by converting into an Integer the Text value stored in the first cell. Once this value has been extracted, you can use the value to call a stored procedure that will delete the row from the database. Once the row in the database has been deleted, you need to get a fresh copy of the data and redisplay it in the DataGrid.
The RefreshData routine (see Figure 3) defines a stored procedure that gets the data from the database and uses it with a data adapter to load the data into a DataSet. The default DataView associated with the downloaded data is saved in the Session object, which in turn is bound to the DataGrid. Finally, the UpdatePageLocation routine initializes the paging information. This routine was discussed in detail in DataStream: One Page at a Time.
Private Sub RefreshData() Dim ds As DataSet Dim ConnectionStr As String = _ ConfigurationSettings.AppSettings("ConnStr") Dim adpt As SqlDataAdapter Dim conn As SqlConnection Dim cmd As SqlCommand ds = New DataSet() conn = New SqlConnection(ConnectionStr) cmd = New SqlCommand("GetCustomers", conn) cmd.CommandType = CommandType.StoredProcedure adpt = New SqlDataAdapter(cmd) adpt.Fill(ds, "Customers") Session("Customers") = ds.Tables("Customers").DefaultView DataGrid1.DataSource = Session("Customers") DataGrid1.DataBind() UpdatePageLocation(0) End Sub
Figure 3. The RefreshData routine gets a fresh copy of the data from the database using a stored procedure and a DataAdapter.
Confirm a Delete
Because a user could inadvertently delete a row from the DataGrid, you might want to force the user to confirm that they really want to delete the row before you remove the row from the base. One way to accomplish this is to require that the user click on the Delete button twice.
Figure 4 contains a slight modification to the DeleteCommand routine shown in Figure 3. This modification compares the newly computed value for CustomerId against a value held in a Session object. If the values are different, a message is displayed to the user instructing them to press the button again if they want to delete that particular customer. Then the customer id value is saved in Session object for the next time this routine is called.
Sub DataGrid1_DeleteCommand(s As Object, _ e As DataGridCommandEventArgs) Dim ConnectionStr As String = _ ConfigurationSettings.AppSettings("ConnStr") Dim conn As SqlConnection Dim cmd As SqlCommand Dim Id As Integer Id = CInt(e.Item.Cells(0).Text) If Id <> Session("CustomerIdToDelete") Then Label1.Text = _ "Press the Delete button again if you want to " & _ "delete customer id #" & Id.ToString Session("CustomerIdToDelete") = Id Return End If Session("CustomerIdToDelete") = -1 conn = New SqlConnection(ConnectionStr) cmd = New SqlCommand("DeleteCustomer", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = Id cmd.Connection.Open() cmd.ExecuteNonQuery() cmd.Connection.Close() RefreshData() End Sub
Figure 4. Ensuring the user really wants to delete a particular row from the database involves a simple modification to the DeleteCommand routine that forces the user to press the Delete button twice in a row.
If the computed customer id value is identical to the one in the Session object, the value in the Session object is set to -1 before deleting the row from the database, ensuring that the DeleteCommand routine is ready for the next row to be deleted.
The sample code in this article is available for download.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.