ASP.NET 2.0: A Developeramp;#039;s Notebook [Electronic resources] نسخه متنی

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.

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.

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

Figure 4-29. Configuring the SqlDataSource2 control

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

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

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

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.

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."