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


Example displaying a databound Label, TextBox and DropDownList

You can download the source in C# and VB.Net using the link below

DataBinding-DDL-Label-TextBox.zip (4.03 kb)