ASP.NET fill Dropdownlist from MS SQL Server Database
Script for Database :
Create database [SimpleAsp]
Script for Table :
USE [SimpleAsp] GO /****** Object: Table [dbo].[tblCities] Script Date: 10/30/2016 19:19:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblCities]( [Id] [int] IDENTITY(1,1) NOT NULL, [Country] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Cities] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
Page design :
Create an empty ASP.NET website or you can also use your existing Website. Add a new page named
Default.aspx and design the page as below :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <h1>ASP.NET Dropdownlist from MS SQL Database tutorial</h1> <hr /> Select a City of your choice : <asp:DropDownList runat="server" ID="ddcity" AutoPostBack="true" OnSelectedIndexChanged="ddcity_SelectedIndexChanged"/> <br /> <asp:Label runat="server" ID="lblInfo" /> </div> </form> </body> </html>
ASP.NET fill Dropdownlist from MS SQL Server Database
It is a simple .net page design containing a Dropdownlist and a label in it. I have made the dropdownlist to AutoPostback and made a method to get the selected value from the user.
Code :
Default.aspx.cs :
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=VIBES\\SQLEXPRESS;Initial Catalog=SimpleAsp;User ID=hitesh;Password=789;");
SqlCommand cmd;
SqlDataAdapter adapter;
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
FillCities();
// call this method only if the page is loaded for the first time
}
public void FillCities()
{
try
{
cmd = new SqlCommand("Select distinct cities from tblcities",con);
if(con.State == ConnectionState.Closed) con.Open();
adapter = new SqlDataAdapter(cmd);
ds = new DataSet();
adapter.Fill(ds,"tblcitites");
ddcity.DataSource = ds.Tables[0];
ddcity.DataTextField = "Cities";
ddcity.DataValueField = "Cities";
ddcity.DataBind();
cmd.Dispose();
adapter.Dispose();
ds = null;
con.Close();
}catch(Exception ex)
{
if(con.State == ConnectionState.Open) con.Close();
lblInfo.Text = ex.Message.ToString();
}
}
protected void ddcity_SelectedIndexChanged(object sender, EventArgs e)
{
lblInfo.Text = "You selected <b> "+ ddcity.Text + ".</b>";
}
}
In the page load method, I have called fillCities() method. Be careful with this, or else you will be filling the dropdownlist again and again. And in the selectecIndexChanged method I’m catching the value selected by the user on the page and displaying it on a label control.