How to Bind/Load/Fill ListBox with Sql Server Database in asp.net

We can bind or load Listbox control from database to show dynamic values. In this article I am going to explain how to bind or fill ListBox control from back end database e.g. Sql Server and How to get selected Item and selected Value from ListBox control.

Implementation:  

First create Sql Server DataBase E.g. "Test " and create a table and name it "DEPT_TABLE" as:

Now create a connection string in WebConfig file:

<connectionStrings>

                                <add name="conStr" connectionString="Data Source=localhost;Initial Catalog=Test;Integrated Security=True"/>

                </connectionStrings>

Source Code:

<fieldset style="width:300px;">

    <legend>Bind ListBox from Sql server database in asp.net</legend>

    <table>

    <tr>

    <td width="40%">Select Department:</td>

    <td align="center"><asp:ListBox ID="lsbDept" runat="server" AutoPostBack="True"

            onselectedindexchanged="lsbDept_SelectedIndexChanged"></asp:ListBox>         

       </td></tr>

        <tr><td colspan="2"> <asp:Label ID="lblStatus" runat="server" Text="" style="color: #009933"></asp:Label></td></tr>

    </table>

          </fieldset>

C#.Net Code to Bind ListBox with Sql Server Database

Include Following Namespces,

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

then write the code:

  protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            FillDeptListBox();

        }

    }

    protected void FillDeptListBox()

    {

        try

        {

            SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

            SqlCommand cmd = new SqlCommand("Select * from DEPT_TABLE", con);

            SqlDataAdapter adp = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();

            adp.Fill(dt);

            lsbDept.DataSource = dt;

            lsbDept.DataTextField = "Dept_Name";

            lsbDept.DataValueField = "Dept_Id_Pk";

            lsbDept.DataBind();

        }

        catch(Exception ex)

        {

            Response.Write("Error occured: " + ex.Message.ToString());

        }         

    }

    protected void lsbDept_SelectedIndexChanged(object sender, EventArgs e)

    {

        lblStatus.Text = "Selected department: " + lsbDept.SelectedItem.Text + " & " + "Selected value : " + lsbDept.SelectedValue;

    }

VB.Net Code to Bind ListBox with Sql Server Database

Design the web page as in C#.net section but replace the line 

<asp:ListBox ID="lsbDept"runat="server" AutoPostBack="True"      onselectedindexchanged="lsbDept_SelectedIndexChanged"></asp:ListBox>  

with the line 

<asp:ListBox ID="lsbDept" runat="server" AutoPostBack="True" ></asp:ListBox>                  

First include the following namespaces:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Then write the code :

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            FillDeptListBox()

        End If

    End Sub

    Protected Sub FillDeptListBox()

        Try

            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

            Dim cmd As New SqlCommand("Select * from DEPT_TABLE", con)

            Dim adp As New SqlDataAdapter(cmd)

            Dim dt As New DataTable()

            adp.Fill(dt)

            lsbDept.DataSource = dt

            lsbDept.DataTextField = "Dept_Name"

            lsbDept.DataValueField = "Dept_Id_Pk"

            lsbDept.DataBind()

        Catch ex As Exception

            Response.Write("Error occured: " & ex.Message.ToString())

        End Try

    End Sub

    Protected Sub lsbDept_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Handles lsbDept.SelectedIndexChanged

        lblStatus.Text = "Selected department: " & lsbDept.SelectedItem.Text & " & " & "Selected value : " & lsbDept.SelectedValue

    End Sub