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.
In previous similar articles I explained How to call server side function without any post back using jQuery AJAX JSON and jQuery AJAX JSON example to insert data into sql server database without postback and Ajax CascadingDropDown example to Fill DropDownList with Countries,states and cities and Bind state categories and cities sub categories in single dropdownlist and Call server side function using Javascript and AJAX PageMethods without any PostBack
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."
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..