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

Well, that’s it. Thank for reading.

Previous - Create Multiple Textboxes Programmatically in Asp.Net and Save with jQuery Ajax WebMethodNext - How to Read Data from a CSV File in Asp.Net using StreamReader Class in C# and VB.Net



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!