Section 4.3. Edit and Delete Records

4.3. Edit and Delete Records

---

Note: Let users edit and delete records in the GridView control.

---

Apart from simply displaying records in the GridView control, you can also allow users to edit or delete records directly while they're being viewed. Unlike the old DataGrid, the new GridView control makes editing and deleting records very simple. And with the wizards to help you configure the GridView control, your life as a developer could not be simpler.

4.3.1. How do I do that?

In the previous lab, you saw how GridView binds to the SqlDataSource control. You have also seen how rows can be sorted automatically without requiring you to write code to do it and how records can be displayed in multiple pages. In this lab, you'll go one step further. You will see how you can configure the GridView control for editing and deleting records.

1. Using the project built in the previous lab, you will now configure the SqlDataSource2 control (which was bound to the GridView control) so that it supports the editing and deletion of records.

2. In the SqlDataSource Tasks menu of SqlDataSource2, click the Configure Data Source... link (see Figure 4-29).

   Figure 4-29. Configuring the SqlDataSource2 control

   
   
   

3. Click Next in the following window and then, in the Configure Select Statement window, click on Advanced . . . . Check the "Generate Insert, Update, and Delete statements" checkbox to generate the appropriate SQL statements to perform modifications to the table (see Figure 4-30). To prevent concurrency conflicts, check the "Use optimistic concurrency" checkbox, too. Click OK.

   Figure 4-30. Generating the appropriate SQL statements to perform modifications to the table

   
   
   

4. Back in the Configure Select Statement window, click Next and then Finish in the next window.

5. To allow the GridView control to support editing and deleting of records, select the Enable Editing and Enable Deleting checkboxes in the GridView Tasks menu (see Figure 4-31).

   Figure 4-31. Checking the Enable Editing and Enable Deleting checkboxes

   
   
   

6. Press F5 to test the application. You will now be able to edit or delete records (see Figure 4-32) by clicking the Edit and Update links.

Figure 4-32. Editing and deleting records

4.3.2. What about...

...resolving concurrency conflicts?

Recall that in Figure 4-30 you checked the "Use optimistic concurrency" checkbox. By selecting this checkbox, the SqlDataSource control will detect any changes to the table before an update is performed. Consider a case in which two users are both trying to update the same record. If one user has updated the record, the other user will detect that the original values have been changed when he tries to update the same record, and the update will not be successful.

If you switch to Source View, you will notice that the SqlDataSource control has acquired a number of new elements and attributes. In particular, the DeleteCommand, InsertCommand, and UpdateCommand attributes take care of the tasks of deleting, inserting, and editing records in the database. Their syntax is shown in Example 4-1.

Example 4-1. DeleteCommand, InsertCommand, and UpdateCommand

DeleteCommand="DELETE FROM [authors]                WHERE [au_id] = @original_au_id                AND [au_lname] = @original_au_lname                AND [au_fname] = @original_au_fname                AND [phone] = @original_phone                AND [address] = @original_address                AND [city] = @original_city                AND [state] = @original_state                AND [zip] = @original_zip                AND [contract] = @original_contract" InsertCommand="INSERT INTO [authors] ([au_id], [au_lname],               [au_fname], [phone], [address], [city],                [state], [zip], [contract]) VALUES (@au_id,                @au_lname, @au_fname, @phone, @address,                @city, @state, @zip, @contract)" UpdateCommand="UPDATE [authors] SET [au_lname] = @au_lname,                [au_fname] = @au_fname, [phone] = @phone,                [address] = @address, [city] = @city,                [state] = @state, [zip] = @zip,                [contract] = @contract                WHERE                [au_id] = @original_au_id                AND [au_lname] = @original_au_lname                AND [au_fname] = @original_au_fname                AND [phone] = @original_phone                AND [address] = @original_address                AND [city] = @original_city                AND [state] = @original_state                AND [zip] = @original_zip                AND [contract] = @original_contract" ConflictDetection="CompareAllValues"

The SQL statements are structured so that they can detect concurrency issues. As an example, consider the UpdateCommand statement. An update can be performed successfully only when the values for the original fields are the same (checked using the @original_fieldname parameter).

To implement the "First-One Wins" strategy, you need to set the ConflictDetection attribute to CompareAllValues.

---

Note: The update conflict resolution strategy in which the first user to update a record wins is called "First-One Wins."

---

The <updateParameters> element keeps track of the old values of each field:

<UpdateParameters>   <asp:Parameter Type="String" Name="au_lname" />   <asp:Parameter Type="String" Name="au_fname" />   <asp:Parameter Type="String" Name="phone" />   <asp:Parameter Type="String" Name="address" />   <asp:Parameter Type="String" Name="city" />   <asp:Parameter Type="String" Name="state" />   <asp:Parameter Type="String" Name="zip" />   <asp:Parameter Type="Boolean" Name="contract" />   <asp:Parameter Type="String" Name="original_au_id" />   <asp:Parameter Type="String" Name="original_au_lname" />   <asp:Parameter Type="String" Name="original_au_fname" />   <asp:Parameter Type="String" Name="original_phone" />   <asp:Parameter Type="String" Name="original_address" />   <asp:Parameter Type="String" Name="original_city" />   <asp:Parameter Type="String" Name="original_state" />   <asp:Parameter Type="String" Name="original_zip" />   <asp:Parameter Type="Boolean" Name="original_contract" /> </UpdateParameters>

Note that the old value of each parameter is prefixed with the string "original_". You can change this value by setting the OldValuesParameterFormatString property of the SqlDataSource control.

...handling errors in updating?

If you try to update a record in the GridView control and an error occurs, you can trap this error via the RowUpdated event, like this:

Protected Sub GridView1_RowUpdated(_                   ByVal sender As Object, _                   ByVal e As System.Web.UI.WebControls. _                               GridViewUpdatedEventArgs) _                   Handles GridView1.RowUpdated    If e.Exception IsNot Nothing Then       Response.Write("Error in updating record.")       e.ExceptionHandled = True    End If End Sub

4.3.3. Where can I learn more?

To learn how to handle concurrency issues in .NET, check out the article at http://www.15seconds.com/issue/030604.htm.

To learn how to service the event fired by the GridView control before a record is deleted, check out the MSDN Help topic "GridView.RowDeleting Event."