Introduction: In this article I
am going to share how to bind dropdownlist from sql server database and insert
an item at top or append an item at bottom in dropdownlist along with data from the database in
asp.net using both C# and VB.
In previous articles i explained how to Bind categories and sub categories in single dropdownlist and jQuery to Show Hide Div content based on dropdownlist selected value and
Read from from XML file and Bind dropdownlist and Reset dropdownlist or HTML dropdown using jquery and Ajax CascadingDropDown to fill dropdownlist with Countries,states and cities
Description: Adding an item e.g. "-Select-" at first position in dropdownlist is very common requirement and easy but while
working on project I got the requirement to append an additional item e.g. "Other" in
databound dropdownlist at the end of all the other items populating from
database. It was as easy as inserting an
item at first. We just need to count the items present in dropdownlist and
insert the desired item at that location.
Implementation:
Let’s create a page to demonstrate the concept.
- First of all create a table and add dummy data into it using the following script:
CREATE TABLE tbCityMaster
(
CityId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
CityName VARCHAR(100)
)
INSERT INTO tbCityMaster VALUES
('Chandigarh'),('Delhi'),('Noida'),('Gurgaon')
- Now create a stored procedure to fetch all the departments from table to bind in dropdownlist
CREATE PROCEDURE spBindCities
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM tbCityMaster
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 City:
<asp:DropDownList ID="ddlCity" runat="server" Width="150px"></asp:DropDownList>
</fieldset>
</div>
</form>
</body>
</html>
Asp.Net
C# Code to add an item at first and last location in dropdownlist
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)
{
BindCities();
}
}
protected void BindCities()
{
try
{
using (sqlCon)
{
using (SqlCommand cmd = new SqlCommand("spBindCities", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
using
(SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
ddlCity.DataSource
= dr;
ddlCity.DataTextField = "CityName";
ddlCity.DataValueField = "CityId";
ddlCity.DataBind();
//Insert at the first location in
dropdownlist
ddlCity.Items.Insert(0, new ListItem("-Select-", "0"));
//Insert at the last location in
dropdownlist
ddlCity.Items.Insert(ddlCity.Items.Count, new ListItem("Other", "0"));
}
}
sqlCon.Close();
}
}
}
catch (Exception ex)
{
Response.Write("Error:" + ex.Message.ToString());
}
}
Asp.Net
VB Code
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
BindCities()
End If
End Sub
Protected Sub BindCities()
Try
Using sqlCon
Using cmd As New SqlCommand("spBindCities", sqlCon)
cmd.CommandType = CommandType.StoredProcedure
sqlCon.Open()
Using
dr As SqlDataReader = cmd.ExecuteReader()
If dr.HasRows Then
ddlCity.DataSource
= dr
ddlCity.DataTextField
= "CityName"
ddlCity.DataValueField = "CityId"
ddlCity.DataBind()
'Insert at the first location in
dropdownlist
ddlCity.Items.Insert(0,
New ListItem("-Select-", "0"))
'Insert at the last location in
dropdownlist
ddlCity.Items.Insert(ddlCity.Items.Count, New ListItem("Other", "-1"))
End If
End Using
sqlCon.Close()
End Using
End Using
Catch ex As Exception
Response.Write("Error:" + ex.Message.ToString())
End Try
End Sub
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, 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."
2 comments
Click here for commentsnice article it very useful to learn some thing new
ReplyThanks ashish for your valuable comment..Stay connected and keep reading more updates..
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..