A common question asked on forums is how to DataBind ASP.Net Labels, TextBoxes or DropDownList hence I decided to write on the same. This article will explain Data Binding all the three controls in ASP.Net.
To make it easy I will explain with a small application that gets the customer details based on their customer id. For this article I have used NorthWind SQL Server Database which can be downloaded from here. NorthWind Database
Connection String
<connectionStrings>
<add name="conString" connectionString="Data Source=.\SQLEXPRESS;
database=Northwind;Integrated Security=true"/>
</connectionStrings >
Namespaces
You will need to import the following namespaces in order to run this application
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Front End Design
In the front end I have place three ASP.Net controls
1. DropDownList
2. Label
3. TextBox
Below is the HTML markup
<form id="form1" runat="server">
<div>
Select Customer ID
<asp:DropDownList ID="ddlCustomers" runat="server" AutoPostBack = "true"
OnSelectedIndexChanged="ddlCustomers_SelectedIndexChanged">
</asp:DropDownList>
<br />
<br />
<br />
<p>---Customer Details---</p>
<hr />
City :
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
<br />
Country :
<asp:Label ID="lblCountry" runat="server" Text=""></asp:Label>
</div>
</form>
You will notice above I have added a OnSelectedIndexChanged event for the DropDownList and also set AutoPostBack = "true" which is the most important step which people forget since if you do not add that the OnSelectedIndexChanged event will not fire
Data binding a DropDownList
DropDownList is a Databound Control hence a data source can be directly assigned to it. The data source can be a DataReader or a DataTable or DataSet using ADO.Net. Once the data source is assigned you need to specify the Text and the Value Fields for a DropDownList. In the below code snippet I am binding a DropDownList is page load event of web page using a SQL DataReader.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlCustomers.Items.Add(new ListItem("--Select Customer--", ""));
ddlCustomers.AppendDataBoundItems = true;
String strConnString = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
String strQuery = "select CustomerID, ContactName from Customers";
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
try
{
con.Open();
ddlCustomers.DataSource = cmd.ExecuteReader();
ddlCustomers.DataTextField = "ContactName";
ddlCustomers.DataValueField = "CustomerID";
ddlCustomers.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
ddlCustomers.Items.Add(New ListItem("--Select Customer--", ""))
ddlCustomers.AppendDataBoundItems = True
Dim strConnString As String = ConfigurationManager _
.ConnectionStrings("conString").ConnectionString
Dim strQuery As String = "select CustomerID, ContactName from Customers"
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = strQuery
cmd.Connection = con
Try
con.Open()
ddlCustomers.DataSource = cmd.ExecuteReader()
ddlCustomers.DataTextField = "ContactName"
ddlCustomers.DataValueField = "CustomerID"
ddlCustomers.DataBind()
Catch ex As Exception
Throw ex
Finally
con.Close()
con.Dispose()
End Try
End If
End Sub
Above I am firing a select query on the Customers table of the NorthWind Database and retrieving the CustomerID and ContactName of all Customers and binding the same to the ASP.Net DropDownList control. I have set the DataTextField as ContactName and DataValueField as CustomerID.
Data binding Label and TextBox
ASP.Net Label and TextBox are not databound controls hence we cannot directly assign data source to them hence we need to take help of ADO.Net DataReader. On the OnSelectedIndexChanged event of the DropDownList I am getting the related details of the Customer User has selected by passing the CustomerID to the Query. Refer the code below
C#
protected void ddlCustomers_SelectedIndexChanged(object sender, EventArgs e)
{
String strConnString = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
String strQuery = "select City, Country from Customers where" +
" CustomerID = @CustomerID";
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@CustomerID", ddlCustomers.SelectedItem.Value);
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
try
{
con.Open();
SqlDataReader sdr= cmd.ExecuteReader();
while (sdr.Read())
{
txtCity.Text = sdr[0].ToString();
lblCountry.Text = sdr["Country"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
VB.Net
Protected Sub ddlCustomers_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim strConnString As String = ConfigurationManager _
.ConnectionStrings("conString").ConnectionString
Dim strQuery As String = "select City, Country from Customers where" _
& " CustomerID = @CustomerID"
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.Parameters.AddWithValue("@CustomerID", ddlCustomers.SelectedItem.Value)
cmd.CommandType = CommandType.Text
cmd.CommandText = strQuery
cmd.Connection = con
Try
con.Open()
Dim sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
txtCity.Text = sdr(0).ToString()
lblCountry.Text = sdr("Country").ToString()
End While
Catch ex As Exception
Throw ex
Finally
con.Close()
con.Dispose()
End Try
End Sub
You will notice above I am binding the TextBox and Label using the following code
C#
txtCity.Text = sdr[0].ToString();
lblCountry.Text = sdr["Country"].ToString();
VB.Net
txtCity.Text = sdr(0).ToString()
lblCountry.Text = sdr("Country").ToString()
There are two ways you can retrieve value from DataReader one using Column Index as I have done for the TextBox and other using Column Name as I have done for the Label.
The figure below displays the sample application
You can download the source in C# and VB.Net using the link below
DataBinding-DDL-Label-TextBox.zip (4.03 kb)