Using Parameters with ADO.NET to Update Data in ASP.NET 2.0 Pages

As promised in the previous article, this article will discuss in detail how to create and use Sqlparameter objects with parameterized queries and stored procedures. You will use these queries and procedures to retrieve and update information in a database.

Contributed by Michael Youssef
Rating:

/ 15
September 25, 2007

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Explaining the code in the example

The first method that we need to talk about is the GetEmployeesRecords() method. It uses a SqlConnection, SqlCommand and a SqlDataReader objects to retrieve the employee's EmployeeID, LastName and FirstName columns from the database, and assigns the returned rows to the ListBox control. The ListBox control represents its list in the form of ListItem objects; we can use the ListBox.Items property to access the collection of the list items of the ListBox control.

The ListBox.Items.Add() method is used to create a ListItem object; to add a ListItem object to the collection, you simply pass the string that represents the list item to the method. We read a row from the database by calling the SqlDataReader.Read() method; combined with the while block, we can access the fields (that we have asked to return through the T-SQL statement), create one string that represents the row and add it to the ListBox control. We have placed the data access code inside a try/catch block to tell the user about the exception message if an exception is raised.

We have created two private arrays, namely employeeFields and splitChar, which are used by the ListBox1_SelectedIndexChanged() event handler of the ListBox control. At this point, we need a mechanism for displaying the individual fields of each record in the three textboxes we have on the page, when the user selects a ListBox item.

You already retrieved this data and stored it in the ListBox control. The mechanism we used doesn't access the database, but it uses the already existing data in the ListBox control. When the user selects another item, the SelectedIndexChanged event fires and the ListBox1_SelectedIndexChanged() method is called. Inside the method we can get to the new list item that the user has selected, through the use of the ListBox.SelectedItem property.

The ListBox.SelectedItem property in turn has a property called Value that returns the string value that represents the ListItem object. So it's a string and all strings have the Split() method that accepts an array of type char, which contains one or more characters that are used to split the string, and returns an array of type string that contains the new partial strings. In our code, we have passed the comma as the separator character that's used to cut a string like "1, Davolio, Nancy" to 1 Davolio Nancy. The items are stored in the employeeFields string array and then assigned to the textboxes.

protected void ListBox1_SelectedIndexChanged(object sender,
EventArgs e){
employeeFields = ListBox1.SelectedItem.Value.Split(splitChar);
EmployeeIDTextBox.Text = employeeFields[0];
LastNameTextBox.Text = employeeFields[1];
FirstNameTextBox.Text = employeeFields[2];
}

This happens every time you select a new item from the ListBox.

When the ListBox control is populated its doesn't select any items. This means that our SelectedIndexChanged event will not fire for the first time the page loads. We have solved this problem by using the following code for the Page_Load event handler:

protected void Page_Load(object sender, EventArgs e){
if (!IsPostBack){
    GetEmployeesRecords();
    ListBox1.Items[0].Selected = true;
    ListBox1_SelectedIndexChanged(this, EventArgs.Empty);
}
}

The Page class has a very important property called IsPostBack which returns false if the page is loaded for the first time and returns true if the page is posted back to the server. We test whether the page is loading for the first time. If so, we call the GetEmployeesRecords() method to populate the ListBox control with the data from the Employees table, then we select the first item in the ListBox through accessing it, through the indexer, and setting the Selected property to true.

You may think that the last line of code is not necessary, but try to comment out that third line and run the page. You will find that the ListBox's first item is selected, but the textboxes are not reflecting that. This happens because the SelectedIndexChanged event fires when the selected index changes and because the selected index was -1. Before we select the first item we need to call this event handler directly from the page_Load method to make sure that the textboxes are populated with the appropriate values. If you need to know more about C# events please consult my articles C# Delegates Explained and C# Events Explained. Now let's modify the code to give the user the ability to update the values on those textboxes and submit the changes to the database.

Modifying the example to provide the update feature

As usual let's see the code first, and then we will talk about what has been added to make this work. The following is the code for the Default.aspx page:

<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="~/Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
    <div>
      <asp:ListBox ID="ListBox1" runat="server" Height="180px"
Width="200px"
OnSelectedIndexChanged="ListBox1_SelectedIndexChanged"
AutoPostBack="True">
      </asp:ListBox> <br /><br />
      <asp:Label ID="EmployeeIDLabel" runat="server"
Text="Employee ID"></asp:Label>
      <asp:TextBox ID="EmployeeIDTextBox" runat="server"
Enabled="false"></asp:TextBox><br />
      <asp:Label ID="LastNameLabel" runat="server" Text="Last
Name"></asp:Label>
         <asp:TextBox ID="LastNameTextBox"
runat="server" OnTextChanged="TextBoxes_TextChanged"
AutoPostBack="True"></asp:TextBox><br />
      <asp:Label ID="FirstNameLabel" runat="server" Text="First
Name"></asp:Label>
        <asp:TextBox ID="FirstNameTextBox"
runat="server" OnTextChanged="TextBoxes_TextChanged"
AutoPostBack="True"></asp:TextBox><br /><br />
      <asp:Button ID="UpdateEmployeeButton" runat="server"
Text="Update Employee" OnClick="UpdateEmployeeButton_Click"
Enabled="False" /><br /><br />
      <asp:Label ID="MessageLabel" runat="server"></asp:Label>
    </div>
</form>
</body>
</html>

Next is the code for the Default.aspx.cs:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

// use the namespace of the ADO.NET SQL Server Data Provider
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page{
private string connectionString = "Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True";
private string[] employeeFields = new string[3];
private char[] splitChar = new char[1] { ',' };

protected void Page_Load(object sender, EventArgs e){
    if (!IsPostBack){
      PopulateList();
    }
}

protected void ListBox1_SelectedIndexChanged(object sender,
EventArgs e){
    employeeFields = ListBox1.SelectedItem.Value.Split
(splitChar);
    EmployeeIDTextBox.Text = employeeFields[0];
    LastNameTextBox.Text = employeeFields[1];
    FirstNameTextBox.Text = employeeFields[2];
}

protected void GetEmployeesRecords(){
    try{
      using (SqlConnection connection = new SqlConnection
(connectionString)){
        string commandText = "SELECT EmployeeID, LastName,
FirstName FROM Employees";
        SqlCommand command = new SqlCommand(commandText,
connection);

        connection.Open();
        using (SqlDataReader dataReader = command.ExecuteReader
()){
          while (dataReader.Read()){
            ListBox1.Items.Add(dataReader["EmployeeID"] + "," +
dataReader["LastName"] + "," + dataReader["FirstName"]);
          }
        }
      }
    }
    catch (Exception ex){
      MessageLabel.Text = ex.Message;
    }
}
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;
    }
}
protected void TextBoxes_TextChanged(object sender, EventArgs
e){
    UpdateEmployeeButton.Enabled = true;
}

private void PopulateList(){
    GetEmployeesRecords();
    ListBox1.Items[0].Selected = true;
    ListBox1_SelectedIndexChanged(this, EventArgs.Empty);
}
}

Now when you run the page you will be able to select any employee's item from the ListBox control, change its last name or first name through the TextBoxes and click the Update Employee button to submit the changes to the Employees database table. The following two screen shots are captured before and after clicking the update button. I just changed the first name of the first employee from Nancy to Nanci.

Explaining the modified version of the example

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.