Please disable your adblock and script blockers to view this page
 

jQuery AJAX to bind Asp.Net DropDownList dynamically from Sql server database

jQuery AJAX to bind Asp.Net DropDownList dynamically from Sql server database Introduction:  In this article I am going to explain with example How to fill/populate asp.net dropdown list dynamically from sql server database by calling C# or VB server side code from client side using jquery AJAX and json.


Implementation: Let’s create a sample web page to check the concept in action.

But first of all create a table (tbDepartment) with the columns and their data type as shown below
Column Name
Data Type
DeptId
Int (primary key. Set is identity=true)
DeptName
varchar(50)
  
Then insert some dummy data into this table.

Now in web.config file create the connection string as:

<connectionStrings>
    <add name="conStr" connectionString="Data Source=Lalit;Initial Catalog=DbEmployee;Integrated Security=True"/>
  </connectionStrings>

Note: Change Data Source and Initial Catalog as per your database.

HTML Source Code:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Populate DropDownList dynamically from Sql server database using jquery ajax json</title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $.ajax({
                type: "POST",
                contentType: "application/json; charset=utf-8",
                //url is the path of our web method (Page name/function name)
                url: "Default.aspx/PopulateDropDownList",
                data: "{}",
                dataType: "json",
                //called on jquery ajax call success
                success: function (result) {
                    $('#ddlDepartments').empty();
                    $('#ddlDepartments').append("<option value='0'>--Select--</option>");
                    $.each(result.d, function (key, value) {
                        $("#ddlDepartments").append($("<option></option>").val(value.DeptId).html(value.DeptName));
                    });
                },
                //called on jquery ajax call failure
                error: function ajaxError(result) {
                    alert(result.status + ' : ' + result.statusText);
                }
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <fieldset style="width:310px;">
                <legend>Populate DropDownList using jQuery AJAX</legend>
Select Department: <asp:DropDownList ID="ddlDepartments" runat="server" Width="160px" />               
            </fieldset>           
        </div>
    </form>
</body>
</html>

Asp.Net C# Code to fill dropdownlist dynamically using jquery ajax and json

In code file (Default.aspx.cs) write the code:

First of all include required namespaces
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

then write the code as:
      
protected void Page_Load(object sender, EventArgs e)
        {
        }

public class Department
        {
            public int DeptId { get; set; }
            public string DeptName { get; set; }
        }


[WebMethod]
        public static List<Department> PopulateDropDownList()
        {
            DataTable dt = new DataTable();
            List<Department> objDept = new List<Department>();

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT DeptId,DeptName FROM tbDepartment", con))
                {
                    con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptId = Convert.ToInt32(dt.Rows[i]["DeptId"]),
                                DeptName = dt.Rows[i]["DeptName"].ToString()
                            });
                        }
                    }
                    return objDept;                
                }
            }
        }
        

Asp.Net VB Code to fill dropdownlist dynamically using jquery ajax and json

In code file (Default.aspx.vb) write the code:

First of all import required namespaces
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Then write the code as:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Public Class Department
        Public Property DeptId() As Integer
            Get
                Return m_DeptId
            End Get
            Set(value As Integer)
                m_DeptId = Value
            End Set
        End Property
        Private m_DeptId As Integer
        Public Property DeptName() As String
            Get
                Return m_DeptName
            End Get
            Set(value As String)
                m_DeptName = Value
            End Set
        End Property
        Private m_DeptName As String
    End Class

    <WebMethod> _
    Public Shared Function PopulateDropDownList() As List(Of Department)
        Dim dt As New DataTable()
        Dim objDept As New List(Of Department)()

        Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
            Using cmd As New SqlCommand("SELECT DeptId,DeptName FROM tbDepartment", con)
                con.Open()
                Dim da As New SqlDataAdapter(cmd)
                da.Fill(dt)
                If dt.Rows.Count > 0 Then
                    For i As Integer = 0 To dt.Rows.Count - 1
                        objDept.Add(New Department() With { _
                            .DeptId = Convert.ToInt32(dt.Rows(i)("DeptId")), _
                            .DeptName = dt.Rows(i)("DeptName").ToString() _
                        })
                    Next
                End If
                Return objDept
            End Using
        End Using
    End Function


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, 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 »

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