This tutorial will explain you how to dynamically bind data from database to DropDownList in ASP.Net using C# and VB.Net
The following tools are used:
First we will create a table "tbl_course" in SQL Server Database "db_sample"
Once you run the database script, the database table will be created as follows:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="CS" %> <html> <head> <title>ASP.Net - Dynamically bind data from database to DropDownList (C# & VB.Net)</title> </head> <body> <form id="form1" runat="server"> <asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList> </form> </body> </html>
Database and Web Form are ready, now we need to bind the data from the database table to DropDownList in our Web Form. To do this copy the script below in codebehind file
using System; using System.Data; // Dataset using System.Data.SqlClient; // SqlDataAdapter using System.Web.UI.WebControls; // ListItem public partial class CS: System.Web.UI.Page { // Create a new SqlConnection. SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=db_sample;Integrated Security=True"); protected void Page_Load(object sender, EventArgs e) { string strQuery = "SELECT course_id, course_name FROM tbl_course"; SqlDataAdapter da = new SqlDataAdapter(strQuery, conn); DataSet ds = new DataSet(); // SqlDataAdapter acts as a bridge between DataSource (eg: SQL Database) and Dataset. da.Fill(ds); if (ds.Tables[0].Rows.Count > 0) { //Dispose the Dataset first. ds.Dispose(); // Remove all the items displayed in DropDownList before adding new data. DropDownList1.Items.Clear(); DropDownList1.DataSource = ds; DropDownList1.DataTextField = "course_name"; DropDownList1.DataValueField = "course_id"; DropDownList1.DataBind(); DropDownList1.Items.Insert(0, new ListItem("-- Select any course --", "0")); } } }
Imports System.Data 'Dataset Imports System.Data.SQLClient 'SqlDataAdapter Imports System.Web.UI.WebControls 'ListItem Partial Class VB Inherits System.Web.UI.Page 'Create a new SqlConnection. Dim conn As New SqlConnection("Data Source=.;Initial Catalog=db_sample;Integrated Security=True") Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load Dim strQuery As String = "SELECT course_id, course_name FROM tbl_course" Dim da As New SqlDataAdapter(strQuery, conn) Dim ds As New DataSet() 'SqlDataAdapter acts as a bridge between DataSource (eg: SQL Database) and Dataset. da.Fill(ds) If ds.Tables(0).Rows.Count > 0 Then 'Dispose the Dataset first. ds.Dispose() 'Remove all the items displayed in DropDownList before adding new data. DropDownList1.Items.Clear() DropDownList1.DataSource = ds DropDownList1.DataTextField = "course_name" DropDownList1.DataValueField = "course_id" DropDownList1.DataBind() DropDownList1.Items.Insert(0, New ListItem("-- Select any course --", "0")) End If End Sub End Class