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.

bind asp.net listbox control from Sql server database table


    bind asp.net listbox control from Sql server database table
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."
Previous
Next Post »

2 comments

Click here for comments
king
admin
October 14, 2013 ×

good thanks sir.

Reply
avatar
October 14, 2013 ×

Your welcome King..stay connected and keep reading :)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..