How to Populate a SELECT element with Data from SQL Server using Asp.Net C#

There are various ways you can populate data dynamically to an HTML Select element, either by calling a web method or a Web API method in Asp.Net. Here in this post I am sharing simple example on how to populate a Select element with data extracted from an SQL Server table using Asp.Net Code behind procedure. The codes are written in both C# and Vb.Net.

The SQL Server Table

Here’s the sample SQL Server table that I am using in my example. Create the table and add few data to it.

The Markup

In the markup section, I have added an HTML <select> element, with few attributes. The onchange event will show the selected values in dropdown list.

Note: You can call a JavaScript function from the onchange event.

<select id="ddlBooks" runat="server" 
    onchange="
        if(this.selectedIndex !=0) 
            document.getElementById('pValue').innerHTML = 'You choose: ' + this.value; 
        else 
            document.getElementById('pValue').innerHTML = '';">
</select>

<%--SHOW SELECT VALUE.--%>
<p id="pValue"></p>

Now let’s populate the <select> element with data from a database table. Don’t forget to add the runat attribute with the value as server.

Related: How to Bind a DropDownList to a Database table in GridView using C# and VB.Net

Code Behind Procedure (C#)

I have written the data binding procedure inside the page load event. I am data binding the <select> element using a DataSet object.

The procedure is very similar to binding a GridView control in Asp.Net using a DataSet. You must check the link as I have explained about DataSet and its benefits.

using System;
using System.Data;
using System.Data.SqlClient;

public partial class SiteMaster : System.Web.UI.MasterPage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // SET THE CONNECTION STRING.
        string sCon = "Data Source=DNA;Persist Security Info=False;Integrated Security=SSPI;" +
            "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;";

        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT BookName FROM dbo.Books"))
            {
                SqlDataAdapter sda = new SqlDataAdapter();
                try
                {
                    cmd.Connection = con;
                    con.Open();
                    sda.SelectCommand = cmd;

                    // INITIALIZE DATASET OBJECT.
                    DataSet ds = new DataSet();
                    sda.Fill(ds);

                    // BIND DATABASE TO SELECT.
                    ddlBooks.DataSource = ds;
                    ddlBooks.DataTextField = "BookName";
                    ddlBooks.DataValueField = "BookName";
                    ddlBooks.DataBind();

                    // SET THE DEFAULT VALUE.
                    ddlBooks.Items.Insert(0, "- SELECT -");
                }
                catch (Exception ex)
                { // }
            }
        }
    }
}

Code behind Procedure (VB)

Option Explicit On
Imports System.Data                        ' FOR "DataSet".
Imports System.Data.SqlClient

Partial Class Site
    Inherits System.Web.UI.MasterPage

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        ' SET THE CONNECTION STRING.
        Dim sCon As String = "Data Source=DNA;Persist Security Info=False;Integrated Security=SSPI;" & _
            "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;"

        Using con As SqlConnection = New SqlConnection(sCon)
            Using cmd As SqlCommand = New SqlCommand("SELECT BookName FROM dbo.Books")

                Dim sda As SqlDataAdapter = New SqlDataAdapter
                Try
                    cmd.Connection = con : con.Open()
                    sda.SelectCommand = cmd

                    Dim ds As DataSet = New DataSet
                    sda.Fill(ds, "dbo.Books")

                    ' BIND DATABASE TO SELECT.
                    ddlBooks.DataSource = ds
                    ddlBooks.DataTextField = "BookName"
                    ddlBooks.DataValueField = "BookName"
                    ddlBooks.DataBind()

                    ' SET THE DEFAULT VALUE.
                    ddlBooks.Items.Insert(0, "- SELECT -")	
                Catch ex As Exception
                    '
                End Try
            End Using
        End Using
    End Sub
End Class

← PreviousNext →