Using Parameters with ADO.NET to Update Data in ASP.NET 2.0 Pages - Modifying the example to provide the update feature
(Page 3 of 4 )
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.
Next: Explaining the modified version of the example >>
More ASP.NET Articles
More By Michael Youssef
/ 11 
