In this tutorial, I’ll explain how you can get the data from database and then bind asp.net dropdownlist dynamically in asp.net using c# as well as vb.net with example code.
In my previous tutorials, I’d explained country-state-city cascading for dropdownlist, how to bind asp.net gridview, main difference between executereader executenonquery and executescalar and other more cracking tutorials on DropDownList, GridView, Asp.net here.
To explain further about how to bind asp.net dropdownlist dynamically, we need to create database table to get data and bind retrieved resultset to dropdown list, so simply execute following script to sql query editor to create database table and then add few records manually.
Here is the script to create “SubjectDetails” table:
CREATE TABLE [dbo].[SubjectDetails] ( [SubjectId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [SubjectName] [nvarchar](100) NULL )
HTML Markup Code To Bind Asp.net DropDownList Dynamically – [.aspx]
Following is the complete HTML Markup code that I used to bind asp.net dropdownlist dynamically in my .aspx page:
<html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>Bind asp.net dropdownlist dynamically</title> </head> <body> <form id="form1" runat="server"> <div> <h4>Bind asp.net dropdownlist dynamically</h4> Select Subject: <asp:DropDownList ID="ddlSubjects" runat="server"> </asp:DropDownList> </div> </form> </body> </html>
Now check the below sample code snippet to bind asp.net dropdown list from code-behind.
Bind Asp.net DropDownList Dynamically In C# – [.cs]
Now add the following namespace that is required to connect with sql server:
using System.Data; using System.Data.SqlClient;
After that add the following code to .aspx.cs page:
//specify your connection string here.. public static string strConn = @"Data Source=datasource;Integrated Security=true;Initial Catalog=database"; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindDropDownList(); } } //bind subject names to dropdownlist private void BindDropDownList() { try { using (SqlConnection sqlConn = new SqlConnection(strConn)) { using (SqlCommand sqlCmd = new SqlCommand()) { sqlCmd.CommandText = "SELECT SubjectId,SubjectName FROM SubjectDetails"; sqlCmd.Connection = sqlConn; sqlConn.Open(); SqlDataAdapter da = new SqlDataAdapter(sqlCmd); DataTable dt = new DataTable(); da.Fill(dt); ddlSubjects.DataSource = dt; ddlSubjects.DataValueField = "SubjectId"; ddlSubjects.DataTextField = "SubjectName"; ddlSubjects.DataBind(); sqlConn.Close(); //Adding "Please select" option in dropdownlist for validation ddlSubjects.Items.Insert(0, new ListItem("Please select", "0")); } } } catch { } }
Bind Asp.net DropDownList Dynamically In Vb.net – [.vb]
Now add the following namespace that is required to connect with sql server:
Imports System.Data Imports System.Data.SqlClient
After that add the following code to .aspx.vb page:
'specify your connection string here..' Public Shared strConn As String = "Data Source=datasource;Integrated Security=true;Initial Catalog=database" Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then BindDropDownList() End If End Sub 'bind subject names to dropdownlist' Private Sub BindDropDownList() Try Using sqlConn As New SqlConnection(strConn) Using sqlCmd As New SqlCommand() sqlCmd.CommandText = "SELECT SubjectId,SubjectName FROM SubjectDetails" sqlCmd.Connection = sqlConn sqlConn.Open() Dim da As New SqlDataAdapter(sqlCmd) Dim dt As New DataTable() da.Fill(dt) ddlSubjects.DataSource = dt ddlSubjects.DataValueField = "SubjectId" ddlSubjects.DataTextField = "SubjectName" ddlSubjects.DataBind() sqlConn.Close() 'Adding "Please select" option in dropdownlist for validation' ddlSubjects.Items.Insert(0, New ListItem("Please select", "0")) End Using End Using Catch End Try End Sub
Example Result
