How to bind a DropDownList in GridView to a database table using C# and VB

Last updated: 26th April 2024

An Asp.Net DropDownList control holds a list of data, which allows users to select a single data from the list. We either use this as a standalone control or embed it in another control, like a GridView control. Here in this tutorial I'll show you how to bind a DropDownList (in a GridView control) to a database table dynamically.

Updated: I have added code in C# and Vb to save the selected Dropdownlist Data to a database table.

There are two ways you can insert values in a DropDownList.

1) Insert the values at design time (that is manually) or
2) you can dynamically bind a DropDownList with a database table, using code behind procedure.

Insert values in a DropDownList at design time (Manually)

Just a brief explaination on how to add values to a dropdown list manually at design time.

<asp:DropDownList runat="server">
    <asp:ListItem>ADVANCE PHYSICS</asp:ListItem>
    <asp:ListItem>GRADUATE</asp:ListItem>
</asp:DropDownList>

Now, lets see how to bind DropDown list to a database table dynamically at runtime.

Scenario

Here is a scenario.

I have a GridView control, which is populated with Employee details. The details also has the employee's "Qualification".

The user wants to update or change the Qualification of an Employee by selecting a qualification from the drop down list. Therefore, I'll add a column named Qualification to the GridView.

image 1 Showing Employee Details in Asp.Net DropDownList Control

The DropDownList will show up when the user clicks the Edit button on a GridView row. See the below image.

image 2 Bind DropDownList in a GridView Control

See this demo

I am assuming you have SQL Server installed in your computer.

Here's the employee table. The data will be populated in the GridView

Next, we'll create a table named "dbo.Qualification" in our SQL Server database and add few rows of data in it.

This table data will be used to populate the DropDown list using a code behind procedure.

CREATE TABLE dbo.Qualification
    (QualificationCode int NOT NULL, 
    Qualification VARCHAR(20) NULL,
	CONSTRAINT PK_Master_Qualification PRIMARY KEY CLUSTERED
	(QualificationCode ASC)) ON[PRIMARY]

-- ADD FEW ROWS IN THE TABLE.
INSERT INTO Qualification (QualificationCode, Qualification) 
    VALUES (1, 'GRADUATE')
INSERT INTO Qualification (QualificationCode, Qualification) 
    VALUES (2, 'ADVANCE PHYSICS')
INSERT INTO Qualification (QualificationCode, Qualification) 
    VALUES (3, 'DIPLOMA IN FINANCE')
INSERT INTO Qualification (QualificationCode, Qualification) 
    VALUES (4, 'MATHEMATICS')
INSERT INTO Qualification (QualificationCode, Qualification) 
    VALUES (5, 'ACCOUNTS')
INSERT INTO Qualification (QualificationCode, Qualification) 
    VALUES (6, 'MANAGEMENT')

Add a connection string in the Web.Config file within the <configuration> tag.

<connectionStrings>
    <add name="DNA_DB" connectionString="Data Source=DNA;Persist Security Info=False;
        Integrated Security=SSPI; Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=dna;
        Connect Timeout=30;"/>
</connectionStrings>

The Markup

In the markup, add a GridView control with columns. It is populated using code behing procedures. The last column has a DropDown list.

<!DOCTYPE html>
<html>
<head>
    <style type="text/css">
        .dropdown { 
            border:solid 1px #6FA602; 
            border-radius:4px; 
            -moz-border-radius:4px; 
            -webkit-border-radius:4px; 
            cursor:pointer; 
            width:auto;
        }
        .gridv th,td { padding:5px }
    </style>
</head>

<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView
                ID = "GridView" 
                runat = "server" 
                AutoGenerateColumns = "False" 
                AutoGenerateEditButton = "True" 
                OnRowDataBound = "GridView_RowDataBound" 
                OnRowEditing = "GridView_RowEditing" 
                OnRowCancelingEdit = "GridView_RowCancelingEdit"
                OnRowUpdating = "GridView_RowUpdating"
                CssClass= "gridv">
                
                <Columns>
                    <asp:TemplateField HeaderText="Employee ID"> 
                        <ItemTemplate>
                            <asp:Label ID="lblEmpID" runat="server" Text='<% #Eval("EmpID") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    
                    <asp:TemplateField HeaderText ="Employee Name">
                        <ItemTemplate >
                            <asp:Label ID="lblEmpName" runat ="server" Text='<%#Eval("EmpName")%>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    
                    <asp:TemplateField HeaderText="Qualification">
                        <EditItemTemplate>
                            <%--Set AutoPostBack as true, to do a post back 
                                after selecting a new value from dropdown.--%>
                            <asp:DropDownList
                                id="ddlQualification" 
                                CssClass="dropdown" 
                                AutoPostBack="true" 
                                EnableViewState="true"
                                OnSelectedIndexChanged="getNewItem" 
                                runat="server">
                            </asp:DropDownList>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="lblqual" runat="server" Text='<% #Bind("Qualification") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                
                <HeaderStyle
                    BackColor="#989898" 
                    BorderColor="Gray" 
                    Font-Bold="True" 
                    ForeColor="White" 
                    Height="20px" />
                    
                <RowStyle HorizontalAlign="Center" Height="20px" />

            </asp:GridView>
        </div>
    </form>
</body>
</html>

C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;                  // For DataTable.
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page 
{
    SqlConnection myConn = default(SqlConnection);
    SqlCommand sqComm = default(SqlCommand);

    System.Data.DataSet ds = new System.Data.DataSet();
    System.Data.SqlClient.SqlDataAdapter SqlAdapter;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (setConn())
        {
            PopulateDataSet();       // Fill DataSet with master data.

            if (!IsPostBack)
            {
                ShowEmpDetails();   // Show employee details in the GridView.
            }
        }
    }

    private bool setConn()
    {
        // Set database connection.
        try
        {
            myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DNA_DB"].ConnectionString);
            myConn.Open();

            sqComm = new SqlCommand();
            sqComm.Connection = myConn;
        }
        catch (Exception ex) { return false; }
        return true;
    }

    // Cancle row editing.
    protected void GridView_RowCancelingEdit(object sender, _
        System.Web.UI.WebControls.GridViewCancelEditEventArgs e)
    {
        GridView.EditIndex = -1;
        ShowEmpDetails();
    }

    protected void GridView_RowDataBound(object sender, 
        System.Web.UI.WebControls.GridViewRowEventArgs e)
    {
        if ((e.Row.RowState & DataControlRowState.Edit) > 0)
        {
            // Bind the DropDownList with the DataSet field with "Qualification" details.
            DropDownList ddlQual = new DropDownList();
            ddlQual = (DropDownList)e.Row.FindControl("ddlQualification");

            if (ddlQual != null)
            {
                ddlQual.DataSource = ds.Tables["qual"];
                ddlQual.DataTextField = ds.Tables["qual"].Columns["Qualification"].ColumnName.ToString();
                ddlQual.DataValueField = ds.Tables["qual"].Columns["QualificationCode"].ColumnName.ToString();
                ddlQual.DataBind();

                // Assign the seleted row value (Qalification Code) to the DropDownList selected value.
                ((DropDownList)e.Row.FindControl("ddlQualification")).SelectedValue = 
                    DataBinder.Eval(e.Row.DataItem, "QualificationCode").ToString();
            }
        }
    }

    protected void GridView_RowEditing(object sender System.Web.UI.WebControls.GridViewEditEventArgs e)
    {
        GridView.EditIndex = e.NewEditIndex;
        ShowEmpDetails();
    }

    // Save dropdown value in database table.
    protected void GridView_RowUpdating(object sender, System.Web.UI.WebControls.GridViewUpdateEventArgs e)
    {
        // Get the Employee id from the active GridView row.
        Label iEmpId = (Label)(GridView.Rows[e.RowIndex].Cells[1].Controls[1]);

        string sQuery;
        sQuery = "UPDATE EmployeeDetails SET Qualification = '" + ViewState["newQualification"] + "' " + 
            "WHERE EmpID = " + iEmpId.Text;

        using (SqlCommand cmd = new SqlCommand(sQuery))
        {
            {
                var withBlock = cmd;
                withBlock.Connection = myConn;
                withBlock.ExecuteNonQuery();
            }
        }

        GridView.EditIndex = -1;     // Set EditIndex = -1 to come out of Edit mode after update.
        ShowEmpDetails();
    }

     // Master data in a DataSet.
    private void PopulateDataSet()
    {
        ds.Clear();
        SqlAdapter = new System.Data.SqlClient.SqlDataAdapter
            ("SELECT QualificationCode, Qualification FROM dbo.Qualification", myConn);

        SqlAdapter.Fill(ds, "qual");
        SqlAdapter.Dispose();
    }

    // Get employee details.
    private void ShowEmpDetails()
    {
        string sQuery = "SELECT EmpDet.EmpID, EmpDet.EmpName, 
            EmpDet.Qualification, Qual.QualificationCode " + 
            "FROM dbo.EmployeeDetails EmpDet " + 
            "LEFT OUTER JOIN Qualification Qual ON EmpDet.Qualification = Qual.Qualification";
        
        SqlDataReader sdrEmp = GetDataReader(sQuery);
        try 
        {
            if (sdrEmp.HasRows) {
                DataTable dt = new DataTable();
                dt.Load(sdrEmp);

                GridView.DataSource = dt;
                GridView.DataBind();    // Bind database table with the GridView.
            }
        } 
        catch (Exception ex) { }
        finally 
        {
            sdrEmp.Close();
            sdrEmp = null;
        }
    }
    private SqlDataReader GetDataReader(string sQuery)
    {
        SqlDataReader functionReturnValue = default(SqlDataReader);
        sqComm.CommandText = sQuery;
        sqComm.ExecuteNonQuery();
        functionReturnValue = sqComm.ExecuteReader();
        sqComm.Dispose();
        return functionReturnValue;
    }
}

See this demo

VB Code

Option Explicit On
Imports System.Data             ' For DataTable.
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Dim myConn As SqlConnection
    Dim sqComm As SqlCommand

    Dim ds As New Data.DataSet
    Dim SqlAdapter As System.Data.SqlClient.SqlDataAdapter

    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
        If setConn() Then
            PopulateDataSet()           ' Fill DataSet with master data.

            If Not IsPostBack Then
                ShowEmpDetails()        ' Show employee details in the GridView.
            End If
        End If
    End Sub

    Private Function setConn() As Boolean
        ' Set database connection.
        Try
            myConn = New SqlConnection("Data Source=DNA;
                Persist Security Info=False;Integrated Security=SSPI;" & _
                "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;")

            myConn.Open()

            sqComm = New SqlCommand
            sqComm.Connection = myConn
        Catch ex As Exception
            Return False
        End Try
        Return True
    End Function

    ' Cancel row editing.
    Protected Sub GridView_RowCancelingEdit(ByVal sender As Object, _
        ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) _
        Handles GridView.RowCancelingEdit
            
        GridView.EditIndex = -1
        ShowEmpDetails()
    End Sub

    Protected Sub GridView_RowDataBound(ByVal sender As Object, _
        ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) _
        Handles GridView.RowDataBound

        If (e.Row.RowState And DataControlRowState.Edit) > 0 Then

            ' Bind the DropDownList with the DataSet field with "Qualification" details.
            Dim ddlQual As New DropDownList
            ddlQual = e.Row.FindControl("ddlQualification")

            If Not IsDBNull(ddlQual) Then
                With ddlQual
                    .DataSource = ds.Tables("qual")
                    .DataTextField = ds.Tables("qual").Columns("Qualification").ColumnName.ToString()
                    .DataValueField = ds.Tables("qual").Columns("QualificationCode").ColumnName.ToString()
                    .DataBind()

                    ' Assign the seleted row value (Qalification Code) to the DropDownList selected value.
                    CType(e.Row.FindControl("ddlQualification"), DropDownList).SelectedValue = _
                        DataBinder.Eval(e.Row.DataItem, "QualificationCode").ToString()
                End With
            End If
        End If
    End Sub

    ' Save dropdown value in database table.
    Protected Sub GridView_RowUpdating(sender As Object, _
        e As System.Web.UI.WebControls.GridViewUpdateEventArgs) _
        Handles GridView.RowUpdating

        ' Get the Employee id from the active GridView row.
        Dim iEmpId As Integer = TryCast(GridView.Rows(e.RowIndex).Cells(1).Controls.Item(1), Label).Text

        Dim sQuery As String
        sQuery = "UPDATE EmployeeDetails SET Qualification = '" & Trim(ViewState("newQualification")) & "' " & _
            "WHERE EmpID = " & iEmpId

        Using cmd As SqlCommand = New SqlCommand(sQuery)
            With cmd
                .Connection = myConn
                .ExecuteNonQuery()
            End With
        End Using

        GridView.EditIndex = -1     ' Set EditIndex = -1 to come out of Edit mode after update.
        ShowEmpDetails()
    End Sub

    ' Row editing.
    Protected Sub GridView_RowEditing(ByVal sender As Object, _
        ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView.RowEditing
            
        GridView.EditIndex = e.NewEditIndex
        ShowEmpDetails()
    End Sub

    ' Master data in a DataSet.
    Private Sub PopulateDataSet()
        ds.Clear()

        SqlAdapter = New System.Data.SqlClient.SqlDataAdapter( _
            "SELECT QualificationCode, Qualification FROM dbo.Qualification", myConn)
        SqlAdapter.Fill(ds, "qual")

        SqlAdapter.Dispose()
    End Sub

    ' Get employee details.
    Private Sub ShowEmpDetails()
        Dim sQuery As String = "SELECT EmpDet.EmpID, EmpDet.EmpName, EmpDet.Qualification, " & _
            "Qual.QualificationCode FROM dbo.EmployeeDetails EmpDet " & _
            "LEFT OUTER JOIN Qualification Qual ON EmpDet.Qualification = Qual.Qualification"
        Dim sdrEmp As SqlDataReader = GetDataReader(sQuery)
        Try
            If sdrEmp.HasRows Then
                Dim dt As New DataTable
                dt.Load(sdrEmp)

                ' Bind database table with the GridView.
                GridView.DataSource = dt : GridView.DataBind()
            End If
        Catch ex As Exception
        Finally
            sdrEmp.Close() : sdrEmp = Nothing
        End Try
    End Sub

    Private Function GetDataReader(Optional ByVal sQuery As String = "") As SqlDataReader
        sqComm.CommandText = sQuery
        sqComm.ExecuteNonQuery()
        GetDataReader = sqComm.ExecuteReader
        sqComm.Dispose()
    End Function
End Class

See this demo

← PreviousNext →