Using Parameters with ADO.NET to Update Data in ASP.NET 2.0 Pages
(Page 1 of 4 )
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.
The code that we introduce in this article is divided into two steps. The first step involves using a ListBox control to retrieve data from the Employees table in the Northwind database and displaying each record's fields in textboxes to give the user the ability to change the values of those fields. The second step provides the update feature in the web page with the new values and submits them to the database. This process involves using the SqlParameter class with the SqlCommand class as we are going to see. In the next article we are going to add the insert and delete operations to the web page.
So let's see the code first. The following is the code that you need to have in the Default.aspx file:
<%@ 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 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"></asp:TextBox><br />
<asp:Label ID="FirstNameLabel" runat="server" Text="First
Name"></asp:Label>
<asp:TextBox ID="FirstNameTextBox"
runat="server"></asp:TextBox><br /><br />
<asp:Button ID="UpdateEmployeeButton" runat="server"
Text="Update Employee" OnClick="UpdateEmployeeButton_Click" />
<br />
<br />
<asp:Label ID="MessageLabel" runat="server"
ForeColor="Red"></asp:Label>
</div>
</form>
</body>
</html>
Place the following code in place of the auto-generated code of the Default.aspx.cs file:
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){
GetEmployeesRecords();
ListBox1.Items[0].Selected = true;
ListBox1_SelectedIndexChanged(this, EventArgs.Empty);
}
}
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){ }
}
When you run the web page you will find that the ListBox control has been populated with items representing a concatenated string of the EmployeeID, Last Name and First Name from the Employees table. When you select another item in the ListBox control, the textbox controls will reflect the new selected record, giving the user the ability to change the Last Name and the First Name values of any employee that is selected in the ListBox control. Note that we don't allow the user to change the EmployeeID field by assigning false to the TextBox.Enable property.
When you first run the page you will note that the first item in the ListBox is selected and the textboxes are populated with the values of that list item. Select another item and the textboxes will reflect that item as shown in the next screen shot.
The update button's click event handler is not operational yet, so for now let's talk about the code that makes this example work.
Next: Explaining the code in the example >>
More ASP.NET Articles
More By Michael Youssef
