Using Parameters with ADO.NET to Update Data in ASP.NET 2.0 Pages - Explaining the modified version of the example
(Page 4 of 4 )
In this modified version of the code we are updating the Employees table and we are using the following T-SQL UPDATE statement:
UPDATE Employees
SET LastName = @LastName,
FirstName = @FirstName
WHERE EmployeeID = @EmployeeID
As you can see, we are using three parameters to update the data. To work with parameters from an ADO.NET application you need to create instances of the SqlParameter class and associate them with the command object along with their values. We have done this in the code by using the SqlCommand.Parameters.AddWithValue() method, which accepts the name parameter and the value and creates it for you on the server in T-SQL code.
In our code we have two methods that access the database, namely the GetEmployeesRecords() method and the UpdateEmployeeButton_Click() event handler method. We have seen the GetEmployeesRecords() method in the previous example which uses the SqlDataReader to retrieve the data from the Employees table and populate the ListBox control.
The GetEmployeesRecords() method is called in the PopulateList() method. The PopulateList() method is used to populate the list for the first time by selecting the first item in the ListBox control, and also by calling the event handler method of the SelectedIndexChanged event. The code that access the database to make the update operation is written inside the UpdateEmployeeButton_Click event handler method of the click event of the button control.
protected void UpdateEmployeeButton_Click(object sender,
EventArgs e){
try{
using (SqlConnection connection = new SqlConnection
(connectionString)){
string commandText = "UPDATE Employees SET LastName =
@LastName, FirstName = @FirstName " + "WHERE EmployeeID =
@EmployeeID";
SqlCommand command = new SqlCommand(commandText,
connection);
command.Parameters.AddWithValue("@LastName",
LastNameTextBox.Text);
command.Parameters.AddWithValue("@FirstName",
FirstNameTextBox.Text);
command.Parameters.AddWithValue("@EmployeeID",
EmployeeIDTextBox.Text);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 1){
MessageLabel.Text = "The Employee record has been
updated.";
ListBox1.Items.Clear();
PopulateList();
}
}
}
catch (Exception ex){
MessageLabel.Text = ex.Message;
}
}
In this method, we created the SqlConnection and the SqlCommand objects needed to execute the T-SQL UPDATE statement on the database, and then we created the three parameters that we need for the UPDATE statement. Note that we get the new values for those parameters by using the Text property of the TextBox class. We then executed the SqlCommand.ExecuteNonQuery() method and in case the statement has been successfully executed, we check to see whether the returned value of the SqlCommand.ExecuteNonQuery() method is equal to 1. Then we display to the user that 1 row has been updated in the database. At this point, we need to reflect the updated record to the ListBox.
You might think of the reason we are doing this, but think about it for a minute or two. When the user updates a record it makes sense that the update should be reflected in the items of the ListBox control. That means we need to access the database again and get a new version of the records. This is possible through the call of the PopulateList() method. But don't forget that we need to clear the ListBox items through the use the ListBox.Items.Clear method, or what we will be doing is adding the new fresh version of the records to the old ones.
This happens because the ListBox control uses ASP.NET's ViewState feature to preserve its state, including its list items, across multiple post backs. So it makes perfect sense to clear the list and populate it again using the PopulateList() method. If you want see the difference you can try running the page without calling the ListBox1.Items.Clear() method, and then try running it one more time without calling both the ListBox1.Items.Clear() method and the PopulateList() method.
One more thing: when you first load the page you will find that the update button is disabled. This makes sense because you should not have an update button enabled without doing any kind of updates to the data. When you update any value in the TextBoxes you will find that the page is posting back to the server and the update button is enabled. To do this we needed to set the AutoPostBack property, in markup we should say the AutoPostBack attribute, to true for both the text boxes and handle the TextChanged event handler for both the LastNameTextBox and the FirstNameTextBox. We used one event handler method for both controls because we needed to enable the update button when the user changed the text of those controls. Here is the markup that we have used:
<asp:TextBox ID="LastNameTextBox" runat="server"
OnTextChanged="TextBoxes_TextChanged"
AutoPostBack="True"></asp:TextBox>
<asp:TextBox ID="FirstNameTextBox" runat="server"
OnTextChanged="TextBoxes_TextChanged"
AutoPostBack="True"></asp:TextBox>
And here is the TextBoxes_TextChanged event handler:
protected void TextBoxes_TextChanged(object sender, EventArgs e){
UpdateEmployeeButton.Enabled = true;
}
In the next article, we are going to add code to perform INSERT and DELETE operations on the Employees table.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 12 
