How to fill DropDownList from Sql server database in asp.net

Introduction: In previous articles i explained how to Fill checkboxlist based on dropdownlist selection and Fill cascading dropdownlist i.e. Country,State,Cities in the dropdownlist and Validate dropdownlist using jQuery and How to open Pop up window on Drop down selection in Asp.net  and How to fill dropdownlist with days, month and year  and Fill ListBox and RadioButtonList and CheckBoxList from database in asp.net.

Bind DropDownList from sql server in asp.net

Description: One of the common requirement while working on asp.net application is to dynamically Fill/ Bind/ Load DropDownList from Sql server database table. Suppose we want to fill DropDownList on page load from department table then here is the way:

Implementation: Let's create an asp.net application to understand.
  • First of all create a table and add dummy data into it using the following script: 

CREATE TABLE tbDepartmentMaster
(
                DepartmentId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                DepartmentName          VARCHAR(100)              
)

INSERT INTO tbDepartmentMaster VALUES
('Administration'),('HR'),('IT'),('Sales'),('Inventory'),('Accounts') 

  • Now create a stored procedure to fetch all the departments from table to bind in dropdownlist

CREATE PROCEDURE spBindDepartments
AS
BEGIN
                SET NOCOUNT ON;
                SELECT * FROM tbDepartmentMaster
END

  • Now In the <configuration> tag of web.config file add your connectionstring as:<connectionStrings>

    <add name="conStr" connectionString="Data Source=LALIT-PC;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>

Note: Replace Data Source and Catalogue i.e. Database name as per your application


HTML Source Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <fieldset style="height: 130px; width: 280px;">
                <legend>Bind dropdownlist from database</legend>
                Select Department:
                <asp:DropDownList ID="ddlDepartment" runat="server" Width="150px"></asp:DropDownList>
            </fieldset>
        </div>
    </form>
</body>
</html>

Asp.Net C# Code to bind dropdownlist from sql server database table

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

//Create Connection object and get connection string defined in web.config file
    SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindDepartments();
        }
    }

    protected void BindDepartments()
    {
        try
        {
            using (sqlCon)
            {
                using (SqlCommand cmd = new SqlCommand("spBindDepartments", sqlCon))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    sqlCon.Open();
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr.HasRows)
                        {
                            ddlDepartment.DataSource = dr;
                            ddlDepartment.DataTextField = "DepartmentName";
                            ddlDepartment.DataValueField = "DepartmentId";
                            ddlDepartment.DataBind();
                            ddlDepartment.Items.Insert(0, new ListItem("-Select-", "0"));
                        }
                    }
                    sqlCon.Close();
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write("Error:" + ex.Message.ToString());
        }
    }

Asp.Net VB Code to bind dropdownlist from sql server database table

Imports System.Data.SqlClient
Imports System.Data

'Create Connection object and get connection string defined in web.config file
    Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            BindDepartments()
        End If
    End Sub

    Protected Sub BindDepartments()
        Try
            Using sqlCon
                Using cmd As New SqlCommand("spBindDepartments", sqlCon)
                    cmd.CommandType = CommandType.StoredProcedure
                    sqlCon.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader()
                        If dr.HasRows Then
                            ddlDepartment.DataSource = dr
                            ddlDepartment.DataTextField = "DepartmentName"
                            ddlDepartment.DataValueField = "DepartmentId"
                            ddlDepartment.DataBind()
                            ddlDepartment.Items.Insert(0, New ListItem("-Select-", "0"))
                        End If
                    End Using
                    sqlCon.Close()
                End Using
            End Using
        Catch ex As Exception
            Response.Write("Error:" + ex.Message.ToString())
        End Try
    End Sub

 Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin 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 »

12 comments

Click here for comments
Anonymous
admin
August 11, 2013 ×

hi sir,

am new to asp.net,and I want to use a dropdown list bind the data from backend for example,I have 4 columns in back end emp id,emp name,city,sal.I will choose datatext field is emp id I will bind it to my dropdown list after selecting emp no I want show related data in below labels or gridview how to do this one please help me.

Reply
avatar
August 12, 2013 ×

Hello..I am creating article as per your requirement.Please check by tomorrow morning..

Reply
avatar
August 13, 2013 ×

Read the article as per your requirement on
How to Get DropDownList selected value and Fill details in GridView and labels based on that in asp.net
http://www.webcodeexpert.com/2013/08/how-to-get-dropdownlist-selected-value.html

Reply
avatar
Unknown
admin
August 14, 2013 ×

Hi sir i am new to asp.net and i want to store data in sql server2008 with c# and i want to store data using radiobutton, dropdownlist,Please help me.

Reply
avatar
August 14, 2013 ×

Hello shanawaj..read the article:
How to Bind/Fill RadioButtonList from Sql server table in asp.net(C#, VB)

http://www.webcodeexpert.com/2013/05/how-to-bindfill-radiobuttonlist-from.html

and please more clarify your exact requirement so that i can create a article resolving your problem..

Reply
avatar
August 22, 2013 ×

That was simple and beautiful. Thank you for taking the time to help us out.

Reply
avatar
August 22, 2013 ×

it is always nice to hear that my posts helped anyone..thanks Peter Campbell for appreciating my work..stay tuned and stay connected for more useful updates..

Reply
avatar
September 07, 2013 ×

your welcome manyu.i am glad to know that my post helped you. Keep reading and stay connected for more useful updates..

Reply
avatar
Unknown
admin
October 05, 2013 ×

please publish some MVC articles on development related

Reply
avatar
October 05, 2013 ×

Hello Kootes balu..i will post articles on MVC very soon for beginners..so keep reading and stay connected to get updates :)

Reply
avatar
Anonymous
admin
February 19, 2015 ×

hi ,i want to know how to dynamically and statically retrieve the data from database sql server for the drop down and list boxes and checked boxed when i chhecked or selected certain item i want required item/field/row details.... can u please tell me briefly.....

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..