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

Introduction: In previous articles i explained How to Create,Read,Sort Xml file and Bind to ListBox in asp.net and Bind RadioButtonList from Sql server table in asp.net and Fill CheckBoxList from Sql Server table and How to fill DropDownList from Sql server database in asp.net. and How to create and consume WCF Services in asp.net  and Get age in years,months,days,hours and seconds from DOB and Count number of times website visited and online users
In this article I am going to explain how to Bind / Load  / 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. "Dept_DB" and create a table and name it "DEPT_TABLE" as shown in figure

• Note: Dept_Id_Pk column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1. Insert some data in this table that you  want to show in the ListBox.
  
  
  Now in web.config file add the connection string under <configuration> tag as: 
  
  
  <connectionStrings>
                                  <add name="conStr" connectionString="Data Source=LocalServer;Initial Catalog=Dept_Db;Integrated Security=True"/>
                  </connectionStrings>
  
  Note: Replace the Data Source and Initial Catalog as per your application.

• In the design page (.aspx) place a ListBox control and a label control as:

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

• In the code behind file (.aspx.cs) write the code as:

First include the following namespaces:

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 = new SqlConnection(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>                  

• In the code behind file (.aspx.vb) write the code as:

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

Now over to you:

"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post