Updating DetailsView Records

Figure 11.17. The DetailsView’s BoundFields have been converted to TemplateFields

Updating DetailsView Records

Now that you have your DetailsView control in place, let’s complete its functionality by making the Update link functional. To begin, we’ll generate the ItemUpdating event handler. The ItemUpdating event is triggered when the

Update link is clicked—an action that will occur once the user enters new data into the text boxes and is ready to commit the updated data to the database.

Open AddressBook.aspx in the designer, select the DetailsView control, and switch to the events viewer by clicking the Event button (the little lightning symbol) in the Properties window. There, double-click the ItemUpdating row to have the designer generate the employeeDetails_ItemUpdating method for you, and update the handler with the code shown below:

Visual Basic File: AddressBook.aspx.vb (excerpt)

Protected Sub employeeDetails_ItemUpdating( _ ByVal sender As Object, ByVal e As _

System.Web.UI.WebControls.DetailsViewUpdateEventArgs) _ Handles employeeDetails.ItemUpdating

' Read the employee ID from the DetailsView object

Dim employeeId As Integer = employeeDetails.DataKey.Value ' Find the TextBox controls with updated data

Dim newAddressTextBox As TextBox = _

463

Chapter 11: Managing Content Using Grid View and Details View

employeeDetails.FindControl("editAddressTextBox") Dim newCityTextBox As TextBox = _

employeeDetails.FindControl("editCityTextBox") ' Extract the updated data from the TextBoxes

Dim newAddress As String = newAddressTextBox.Text Dim newCity As String = newCityTextBox.Text

'Declare data objects Dim conn As SqlConnection Dim comm As SqlCommand

'Read the connection string from Web.config Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _ "Dorknozzle").ConnectionString

'Initialize connection

conn = New SqlConnection(connectionString) ' Create command

comm = New SqlCommand("UpdateEmployeeDetails", conn) comm.CommandType = Data.CommandType.StoredProcedure

'Add command parameters comm.Parameters.Add("@EmployeeID", Data.SqlDbType.Int) comm.Parameters("@EmployeeID").Value = employeeId

comm.Parameters.Add("@NewAddress", Data.SqlDbType.NVarChar, 50) comm.Parameters("@NewAddress").Value = newAddress comm.Parameters.Add("@NewCity", Data.SqlDbType.NVarChar, 50) comm.Parameters("@NewCity").Value = newCity

'Enclose database code in Try-Catch-Finally

Try

'Open the connection conn.Open()

'Execute the command comm.ExecuteNonQuery()

Finally

' Close the connection

conn.Close()

End Try

'Exit edit mode employeeDetails.ChangeMode(DetailsViewMode.ReadOnly)

'Reload the employees grid

BindGrid()

' Reload the details view BindDetails()

End Sub

C#	File: AddressBook.aspx.cs (excerpt)

protected void employeeDetails_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)

464

Updating DetailsView Records

{

// Read the employee from the DetailsView object

int employeeId = (int)employeeDetails.DataKey.Value;

//Find the TextBox controls with updated data TextBox newAddressTextBox =

(TextBox)employeeDetails.FindControl("editAddressTextBox"); TextBox newCityTextBox =

(TextBox)employeeDetails.FindControl("editCityTextBox");

//Extract the updated data from the TextBoxes

string newAddress = newAddressTextBox.Text; string newCity = newCityTextBox.Text;

//Define data objects SqlConnection conn; SqlCommand comm;

//Initialize connection string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

//Initialize connection

conn = new SqlConnection(connectionString); // Create command

comm = new SqlCommand("UpdateEmployeeDetails", conn); comm.CommandType = CommandType.StoredProcedure;

//Add command parameters comm.Parameters.Add("EmployeeID", SqlDbType.Int); comm.Parameters["EmployeeID"].Value = employeeId; comm.Parameters.Add("NewAddress", SqlDbType.NVarChar, 50); comm.Parameters["NewAddress"].Value = newAddress; comm.Parameters.Add("NewCity", SqlDbType.NVarChar, 50); comm.Parameters["NewCity"].Value = newCity;

//Enclose database code in Try-Catch-Finally

try

{

//Open the connection conn.Open();

//Execute the command comm.ExecuteNonQuery();

}

finally

{

// Close the connection conn.Close();

}

//Exit edit mode employeeDetails.ChangeMode(DetailsViewMode.ReadOnly);

//Reload the employees grid

465

Chapter 11: Managing Content Using Grid View and Details View

BindGrid();

// Reload the details view BindDetails();

}

This code is pretty straightforward. It starts by reading the value of the DataKey of the DetailsView object. As we saw earlier, the DetailsView, like the GridView, is able to store the ID of the record (or records) it’s displaying. You’ll remember that we made the DetailsView object aware of the EmployeeID data key when we bound the DetailsView to its data source in the BindDetails method. We read this information in the ItemUpdating event handler, like so:

Visual Basic File: AddressBook.aspx.vb (excerpt)

' Read the employee from the DetailsView object

Dim employeeId As Integer	= employeeDetails.DataKey.Value
C#	File: AddressBook.aspx.cs (excerpt)
// Read the employee from	the DetailsView object

int employeeId = (int) employeeDetails.DataKey.Value;

The next step is to find the TextBox objects that contain the updated data. We do this using the FindControl method, as we’ve seen previously. After we obtain the control references, we obtain the string values that we’re interested in simply by reading their Text properties, as is shown in the following code snippets:

Visual Basic

File: AddressBook.aspx.vb (excerpt)

'Find the TextBox controls with updated data Dim newAddressTextBox As TextBox = _

employeeDetails.FindControl("editAddressTextBox") Dim newCityTextBox As TextBox = _

employeeDetails.FindControl("editCityTextBox")

'Extract the updated data from the TextBoxes

Dim newAddress As String = newAddressTextBox.Text

Dim newCity As String = newCityTextBox.Text

C#	File: AddressBook.aspx.cs (excerpt)

//Find the TextBox controls with updated data TextBox newAddressTextBox =

(TextBox)employeeDetails.FindControl("editAddressTextBox"); TextBox newCityTextBox =

(TextBox)employeeDetails.FindControl("editCityTextBox");

//Extract the updated data from the TextBoxes

string newAddress = newAddressTextBox.Text; string newCity = newCityTextBox.Text;

466

Updating DetailsView Records

Figure 11.18. Updating an employee’s address and city

Next, we call a stored procedure to take care of the database update. To create this stored procedure, run the following script in SQL Server Management Studio:

CREATE PROCEDURE UpdateEmployeeDetails

(

@EmployeeID Int, @NewAddress nvarchar(50), @NewCity nvarchar(50)

)

AS

UPDATE Employees

SET Address = @NewAddress, City = @NewCity WHERE EmployeeID = @EmployeeID

467