More Ways to Update Databases using ASP.NET 2.0 SqlDataSource - Using a ListBox and a SqlDataSource control for Default.aspx page
(Page 2 of 5 )
First of all, create a new website and add a Configuration file to it. Then locate the <connectionStrings /> element in the Configuration file and replace it with the following:
<connectionStrings>
<add name="NorthwindConnection"
connectionString="Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Now open the Default.aspx page and drop a ListBox control along with a SqlDataSource control on the page. Configure the SqlDataSource control to retrieve the EmployeeID, FirstName and LastName columns from the Employees table of the Northwind database. Here is the code that you need to put as the markup of 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 runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True"
Height="193px"
OnSelectedIndexChanged="ListBox1_SelectedIndexChanged"
Width="171px">
</asp:ListBox>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName] FROM
[Employees]">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
Actually, the SqlDataSource control we added to the page is not bound to the ListBox control because we are going to do it in the code behind file. This is the code for the Default.aspx.cs file:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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;
using System.Data.Common;
public partial class _Default : System.Web.UI.Page
{
private string[] employeeFields = new string[3];
private char[] splitChar = new char[1] { ',' };
protected void Page_Load(object sender, EventArgs e)
{
SqlDataSource1.DataSourceMode = SqlDataSourceMode.DataReader;
IEnumerable iterator = SqlDataSource1.Select
(DataSourceSelectArguments.Empty);
foreach (DbDataRecord record in iterator)
{
ListItem item = new ListItem();
item.Text = record["EmployeeID"].ToString() + ", " +
record["FirstName"].ToString() + ", " +
record["LastName"].ToString();
item.Value = record["EmployeeID"].ToString();
this.ListBox1.Items.Add(item);
}
}
protected void ListBox1_SelectedIndexChanged(object sender,
EventArgs e)
{
employeeFields = ListBox1.SelectedItem.Text.Split(splitChar);
string employeeID = employeeFields[0].Trim();
string firstName = employeeFields[1].Trim();
string lastName = employeeFields[2].Trim();
Response.Redirect("Default2.aspx?EmpID=" + employeeID +
"&FirstName=" + firstName +
"&LastName=" + lastName);
}
}
In this code, we set the SqlDataSource.DataSourceMode property to return a data reader object through assigning the enumeration value SqlDataSourceMode.DataReader. We call the Select() method to return an IEnumerable object, of type IDataReader, so we can iterate through the returned data. In each of the foreach iterations we create a ListItem and set its Text property to a string value that represents the EmployeeID concatenated with the FirstName and LastName fields. We also assign the EmployeeID field's value to the ListItem.Value property. Finally, we add this ListItem object to our ListBox control.
We have set the AutoPostBack attribute to true, so when the user changes a selection in the ListBox, the page posts back to the server and the SelectedIndexChanged of the ListBox control fires. In the event handler method ListBox1_SelectedIndexChanged() we use the Split() method of the Text of the ListBox.SelectedItem to separate the fields into 3 string variables. We pass these variables to another page, called Default2.aspx, through the Query String. We have redirected the user to the the Default2.aspx page through the Response.Redirect() method as you can see in the above code.
Let's move to the Default2.aspx where we will do the update of the Employee's record.
Next: Using a SqlDataSource control in the Default2.aspx page >>
More ASP.NET Articles
More By Michael Youssef
