How to Bind/Fill/Populate DropDownList in Asp.Net using Jquery Ajax and Json in C# and VB.Net

 
In this article I will explain how to bind/fill/populate dropdownlist in Asp.Net using Jquery Ajax and Json in C# and VB.Net.

Step1:

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>

Step2:

Now, you need to add jquery library for ajax call. So, I will add jquery latest version cdn links to the header.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

Step3:

Here we will call the ajax method. In html page we need to add some javascript code to call the web method to html file. Here we will get the dropdown values as json format. Here the function calls in page load event in javascript.

 <script type="text/javascript">
        $(function () {
            $.ajax({
                type: "POST",
                url: "Default2.aspx/BindDropdownList",
                data: '{}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (r) {
                    var ddlCountry = $("#ddlCountry");
                    ddlCountry.empty().append('<option selected="selected" value="0">Please select</option>');
                    $.each(r.d, function () {
                        ddlCountry.append($("<option></option>").val(this['Value']).html(this['Text']));
                    });
                }
            });
        });
    </script>

Step4:

Here we need to add some namespace into the .cs file.

C#

using System.Web.Services;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System;

VB.Net

Imports System.Web.Services
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System

Now, we need to create a web method function in code behind file. Here we will get the dropdown list values from ms sql database.

C#

[WebMethod]
public static List<ListItem> BindDropdownList() // this is a static list function
{
    var CountryList = new List<ListItem>(); // initialize list
    string constr = ConfigurationManager.ConnectionStrings("cons").ConnectionString; // connection string
    using (var con = new SqlConnection(constr))
    {
        using (var cmd = new SqlCommand("SELECT ID, Country FROM tblCountry")) // query to get data from ms sql database
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                    CountryList.Add(new ListItem()
                    { // here, we will bind the list
                        Value = sdr("Id").ToString(),
                        Text = sdr("Country").ToString()
                    });
            }
            con.Close();
        }
    }
    return CountryList; // here, we will return the list to ajax function
}

VB.Net

<WebMethod>
Public Shared Function BindDropdownList() As List(Of ListItem) ' this is a static list function
    Dim CountryList As List(Of ListItem) = New List(Of ListItem)() 'initialize list
    Dim constr As String = ConfigurationManager.ConnectionStrings("cons").ConnectionString 'connection string
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT ID, Country FROM tblCountry") 'query to get data from ms sql database
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    CountryList.Add(New ListItem With { ' here, we will bind the list
.Value = sdr(CStr("Id")).ToString(),
.Text = sdr(CStr("Country")).ToString()
})
                End While
            End Using
            con.Close()
        End Using
    End Using
    Return CountryList 'here, we will return the list to ajax function
End Function

Full Code:

HTML

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default2.aspx.cs" Inherits="Demo.Default2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
              <asp:DropDownList runat="server" ID="ddlCountry"></asp:DropDownList>
        </div>
    </form>
    <script type="text/javascript">
        $(function () {
            $.ajax({
                type: "POST",
                url: "Default2.aspx/BindDropdownList",
                data: '{}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (r) {
                    var ddlCountry = $("#ddlCountry");
                    ddlCountry.empty().append('<option selected="selected" value="0">Please select</option>');
                    $.each(r.d, function () {
                        ddlCountry.append($("<option></option>").val(this['Value']).html(this['Text']));
                    });
                }
            });
        });
    </script>
</body>
</html>

C#

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
    public partial class Default2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
           
        }
        [WebMethod]
        public static List<ListItem> BindDropdownList() // this is a static list function
        {
            List<ListItem> CountryList = new List<ListItem>();//initialize list
            string constr = ConfigurationManager.ConnectionStrings["cons"].ConnectionString;//connection string
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT ID, Country FROM tblCountry"))//query to get data from ms sql database
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            CountryList.Add(new ListItem // here, we will bind the list
                            {
                                Value = sdr["Id"].ToString(),
                                Text = sdr["Country"].ToString()
                            });
                        }
                    }
                    con.Close();
                }
            }
            return CountryList; //here, we will return the list to ajax function
        }
    }
}

VB.Net

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
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)

        End Sub
        <WebMethod>
        Public Shared Function BindDropdownList() As List(Of ListItem) ' this is a static list function
            Dim CountryList As List(Of ListItem) = New List(Of ListItem)() 'initialize list
            Dim constr As String = ConfigurationManager.ConnectionStrings("cons").ConnectionString 'connection string
            Using con As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand("SELECT ID, Country FROM tblCountry") 'query to get data from ms sql database
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = con
                    con.Open()
                    Using sdr As SqlDataReader = cmd.ExecuteReader()
                        While sdr.Read()
                            CountryList.Add(New ListItem With { ' here, we will bind the list
    .Value = sdr(CStr("Id")).ToString(),
    .Text = sdr(CStr("Country")).ToString()
})
                        End While
                    End Using
                    con.Close()
                End Using
            End Using
            Return CountryList 'here, we will return the list to ajax function
        End Function
    End Class
End Namespace

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

Thank You 😊 

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