Asp.net - Dynamically bind data from database to DropDownList (C# & Vb.net)

Mar 20, 2015 By Mohammed Imtiyaz

Download

Introduction

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 :

  • SQL Server
  • Visual Studio (Asp.net Web Form)

First we will create a table "tbl_course" in SQL Server Database "db_sample"

NOTE: To create a database in SQL Server, go to the Database Folder of your downloaded file and open Database Script file and execute it using SQL Query Analyzer, the database will be created with tables.

Once you run the database script, the database table will be created as follows:

Demo

Aspx Page

<%@ 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">

    <h1>

        Asp.net - Dynamically bind data from database to DropDownList (C# & Vb.net)</h1>

    <br />

    <br />

    <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

C#

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"));

        }

    }

}

VB.Net

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


Post your comment