Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Data bind asp.net dropdownlist with data from the database - Part 17

Suggested Videos
Part 14 - Button, LinkButton and ImageButton Controls
Part 15 - Command Event of an asp.net button control
Part 16 - DropDownList in asp.net

In this video, we will learn about, binding data from a database table, to a dropdownlist. We will be using tblCity table for this demo. Please find the script below, to create and populate the table.

Create table tblCity
(
CityId int primary key,
CityName nvarchar(50),
Country nvarchar(50)
)

Insert into tblCity values(101, 'Delhi', 'India')
Insert into tblCity values(102, 'London', 'UK')
Insert into tblCity values(103, 'New York', 'US')
Insert into tblCity values(104, 'Tokyo', 'Japan')



Create an ASP.NET web application. Drag and drop a DropDownList control onto the webform. Copy and paste the following code in the code behind page. 
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("Select CityId, CityName, Country from tblCity", con);
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            DropDownList1.DataSource = rdr;
            DropDownList1.DataBind();
        }
    }
}



Run the application. Notice that, the DropDownList displays, System.Data.Common.DataRecordInternal instead of the City names. This is because, we haven't specified the DataTextField and DataValueField properties of the DropDownList. The code below specifes both the properties.
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("Select CityId, CityName, Country from tblCity", con);
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            DropDownList1.DataTextField = "CityName";
            DropDownList1.DataValueField = "CityId";
            DropDownList1.DataSource = rdr;
            DropDownList1.DataBind();
        }
    }
}

Run the application now. The city names are displayed as expected. But make sure to set the properties(DataTextField, DataValueField) before calling DataBind() method. Also, note that, these properties can be set in the HTML of the aspx page as well.
<asp:DropDownList ID="DropDownList1" DataTextField="CityName" 
    DataValueField="CityId" runat="server">
</asp:DropDownList>

9 comments:

  1. Fabbbb...........lous...for easy learning

    ReplyDelete
  2. in the previous video we have seen that if we do not use "(!IsPostBack)"
    then its duplicating the value after clicking the button. but in this case why it is not happening ?

    ReplyDelete
    Replies
    1. Because all code getting executed when the page is first time load

      Delete
  3. Sir really i cant explain my happiness my crazyness on u ...really make more interesting video sir plz

    ReplyDelete
  4. I am not getting same output sir pls tell me why?
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data.SqlClient;
    using System.Data;
    using System.Configuration;

    namespace InventeryManagement
    {
    public partial class WebForm1 : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {

    SqlConnection dp1 = new SqlConnection(ConfigurationManager.ConnectionStrings["invent"].ConnectionString);
    using (dp1)
    {
    dp1.Open();
    SqlCommand cmd = new SqlCommand("select id,StateName from IndStates", dp1);
    SqlDataReader rdr = cmd.ExecuteReader();
    DropDownList1.DataTextField = "StateName";
    DropDownList1.DataValueField = "id";
    DropDownList1.DataSource = rdr;
    DropDownList1.DataBind();

    }

    }
    }
    }

    ReplyDelete
  5. DropDownList1.DataSource =rdr ;
    can you put this line after SqlDataReader rdr = cmd.ExecuteReader();

    ReplyDelete
  6. please help me to bind a table in MS SQl with id and name to a KEDO dropdownlist in MVC

    ReplyDelete

It would be great if you can help share these free resources