How to Bind/Fill/Populate DropDownList from MS SQL Database in Asp.Net using C# and VB.Net

 

In this article I will explain how to bind/fill/populate dropdownlist from a Ms Sql Database in Asp.Net using C# and VB.Net.

You need to add html dropdown component in aspx page to bind/fill/populate the dropdown.

HTML

<asp:DropDownList runat="server" ID="ddlCountry"></asp:DropDownList>

You need to add a namespace in code behind file.

C#

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

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Now, I will create BindDropdownList() function in code behind file, then I will call the function on page load event.

C#

public void BindDropdownList()
{
    string constr = ConfigurationManager.ConnectionStrings("cons").ConnectionString;
    using (var con = new SqlConnection(constr))
    {
        using (var cmd = new SqlCommand("SELECT ID, Country FROM tblCountry"))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            ddlCountry.DataSource = cmd.ExecuteReader();
            ddlCountry.DataTextField = "Country";
            ddlCountry.DataValueField = "Id";
            ddlCountry.DataBind();
            con.Close();
        }
    }
    ddlCountry.Items.Insert(0, new ListItem("--Select Country--", "0"));
}

VB.Net

Public Sub BindDropdownList()
    Dim constr As String = ConfigurationManager.ConnectionStrings("cons").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT ID, Country FROM tblCountry")
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            ddlCountry.DataSource = cmd.ExecuteReader()
            ddlCountry.DataTextField = "Country"
            ddlCountry.DataValueField = "Id"
            ddlCountry.DataBind()
            con.Close()
        End Using
    End Using
    ddlCountry.Items.Insert(0, New ListItem("--Select Country--", "0"))
End Sub

Full Code:

<asp:DropDownList runat="server" ID="ddlCountry"></asp:DropDownList>

C#

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

namespace Demo
{
    public partial class Default2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                BindDropdownList();
            }
        }
        public void BindDropdownList()
        {
            string constr = ConfigurationManager.ConnectionStrings("cons").ConnectionString;
            using (var con = new SqlConnection(constr))
            {
                using (var cmd = new SqlCommand("SELECT ID, Country FROM tblCountry"))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    ddlCountry.DataSource = cmd.ExecuteReader();
                    ddlCountry.DataTextField = "Country";
                    ddlCountry.DataValueField = "Id";
                    ddlCountry.DataBind();
                    con.Close();
                }
            }
            ddlCountry.Items.Insert(0, new ListItem("--Select Country--", "0"));
        }
    }
}

VB.Net

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI
Imports System.Web.UI.WebControls

Namespace Demo
    Public Partial Class Default2
        Inherits Web.UI.Page
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If Not Me.IsPostBack Then
                BindDropdownList()
            End If
        End Sub
        Public Sub BindDropdownList()
            Dim constr As String = ConfigurationManager.ConnectionStrings("cons").ConnectionString
            Using con As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand("SELECT ID, Country FROM tblCountry")
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = con
                    con.Open()
                    ddlCountry.DataSource = cmd.ExecuteReader()
                    ddlCountry.DataTextField = "Country"
                    ddlCountry.DataValueField = "Id"
                    ddlCountry.DataBind()
                    con.Close()
                End Using
            End Using
            ddlCountry.Items.Insert(0, New ListItem("--Select Country--", "0"))
        End Sub
    End Class
End Namespace

Demo Screenshot:

Please mail me at hemanta@webcodespider.net if you want any code solution from me. 

Thank You 😊 

How to Bind/Fill/Populate DropDownList from DataTable in Asp.Net using C# and VB.Net