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.
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:
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:
- 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
"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."
12 comments
Click here for commentshi sir,
Replyam 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.
Hello..I am creating article as per your requirement.Please check by tomorrow morning..
ReplyRead the article as per your requirement on
ReplyHow 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
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.
ReplyHello shanawaj..read the article:
ReplyHow 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..
That was simple and beautiful. Thank you for taking the time to help us out.
Replyit 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..
Replythanks for help....
Replyyour welcome manyu.i am glad to know that my post helped you. Keep reading and stay connected for more useful updates..
Replyplease publish some MVC articles on development related
ReplyHello Kootes balu..i will post articles on MVC very soon for beginners..so keep reading and stay connected to get updates :)
Replyhi ,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.....
ReplyIf 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..