encodedna - gridview

Asp.Net - How to Bind a DropDownList to a Database table in GridView?

February 12th, 2013
gridview asp.net
An Asp.Net DropDownList control holds a collection of data which allows the user to select a single data from a drop down list. This control can either be used as standalone control on a web page or can be embedded in another control, like a GridView control.

The data in the DropDownList can be inserted either at design time (manually) or you can bind this control with a database table from code behind.

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

In this article we will show you how data binding can be done to a DropDownList, which will then be embedded in a GridView control. The GridView (in the demo) will display Employee details fetched from a database table. The user wants to alter the “Qualification” of an Employee by selecting a qualification from the drop down list. So we will add a column named “Qualification” to the GridView which will have a DropDownList. The DropDownList will get its data from a master table in the database.

Master Table “Qualification”

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

--INSERT 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')

SELECT *FROM Qualification

Qualification Master

The DropDownList control will be shown when the user clicks the “Edit” button on a GridView row. If the selected row already has a qualification, it will be shown as the default (selected) value in the drop down list.

Image 1)

Employee Details

Image 2)

DropDownList in GridView

The above images will give you a clue about what we are trying to explain. In the first image, Employee DEWANE PAUL’s qualification is “MATHEMATICS”. Clicking the “Edit” button will show the DropDownList with the default value as “MATHEMATICS” (See image 2).

Surely the best way to understand this will be to try the real demo.

Note: Add a connection string in the “Web.Config” file inside the “<configuration>” tag. The Web.Config file will automatically created when you create a “New Web Site” using the Visual Studio.

<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>
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Bind Data to a DropDownList in GridView</title>
    <style type="text/css">
        .dropdown { font:12px/0.8 tahoma,Arial; border:solid 1px #6FA602; border-radius:4px; 
            -moz-border-radius:4px; -webkit-border-radius:4px; cursor:pointer; width:auto }
        .gridv th,td { padding:3px }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div style="font:12px/0.8 tahoma,Arial">
            <asp:GridView ID="GridView" runat="server" AutoGenerateColumns="False" 
                AutoGenerateEditButton="True" OnRowDataBound="GridView_RowDataBound" 
                OnRowEditing="GridView_RowEditing" OnRowCancelingEdit="GridView_RowCancelingEdit" 
                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>
                            <asp:DropDownList id="ddlQualification" CssClass="dropdown" runat="server">
                                </asp:DropDownList>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="lblqual" runat="server" Text='<% #Bind("Qualification") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                <HeaderStyle BackColor="BurlyWood" BorderColor="Gray" Font-Bold="True" 
                    ForeColor="WhiteSmoke" Height="20px" />
                <RowStyle BackColor="Wheat" BorderColor="GrayText" ForeColor="#000" 
                    HorizontalAlign="Center" Height="20px" />
            </asp:GridView>
        </div>
    </form>
</body>
</html>
C#
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())
        {
            PopulateDS();       // FILL DATASET WITH MASTER DATA.
            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;
    }

    // CANCEL 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 FILLED 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 SELECTED ROW VALUE ("QUALIFICATION 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();
    }

    // MASTER DATA IN A DATASET.
    private void PopulateDS()
    {
	    ds.Clear();
	    SqlAdapter = new System.Data.SqlClient.SqlDataAdapter
	        ("SELECT QualificationCode, Qualification FROM dbo.Qualification", myConn);

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

    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;
    }
}
VB
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
            PopulateDS()        ' FILL DATASET WITH MASTER DATA.
            ShowEmpDetails()    ' SHOW EMPLOYEE DETAILS IN THE GRIDVIEW.
        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 FILLED 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 SELECTED ROW VALUE ("QUALIFICATION 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

    ' 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

    Private Sub PopulateDS()
        ds.Clear()

        ' MASTER DATA IN A DATASET.
        SqlAdapter = New System.Data.SqlClient.SqlDataAdapter( _
            "SELECT QualificationCode, Qualification FROM dbo.Qualification", myConn)
        SqlAdapter.Fill(ds, "qual")

        SqlAdapter.Dispose()
    End Sub

    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)

                GridView.DataSource = dt : GridView.DataBind()      ' BIND DATABASE TABLE WITH THE GRIDVIEW.
            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

You may also like this article about a Asp.Net GridView Example with Row Edit and Delete Option.

gridview asp.net