Insert item at first or last position in databound dropdownlist in Asp.Net C#,VB

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.
Bind dropdownlist from sql server database  and insert item at first or last position in asp.net

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 to add an item at first and last location in dropdownlist

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." 
Previous
Next Post »

2 comments

Click here for comments
Shourya
admin
August 01, 2015 ×

nice article it very useful to learn some thing new

Reply
avatar
August 07, 2015 ×

Thanks ashish for your valuable comment..Stay connected and keep reading more updates..

Reply
avatar

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