ASP DOT NET

Tuesday, July 3, 2018

Q. How to Bind Data to a DropDownList inside GridView using asp.net c#?

Step 1 Create Table 

 CREATE TABLE dbo.Qualification
    (QualificationCode int NOT NULL, 
    Qualification VARCHAR(20) NULL,
CONSTRAINT PK_Master_Qualification PRIMARY KEY CLUSTERED
(QualificationCode ASC)) ON[PRIMARY]
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')
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

 CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[TitleOfCourtesy] [nvarchar](25) NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[HomePhone] [nvarchar](24) NULL,
[Extension] [nvarchar](4) NULL,
[Photo] [image] NULL,
[Notes] [ntext] NULL,
[ReportsTo] [int] NULL,
[PhotoPath] [nvarchar](255) NULL,
[Qualification] [varchar](50) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Photo], [Notes], [ReportsTo], [PhotoPath], [Qualification]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative', N'Ms.', CAST(N'1948-12-08 00:00:00.000' AS DateTime), 
Now Add aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default7.aspx.cs" Inherits="Default7" %>

 <!DOCTYPE html>

 <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 Arial; 
            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 style="font:12px/0.8 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("EmployeeID") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    
                    <asp:TemplateField HeaderText ="Employee Name">
                        <ItemTemplate >
                            <asp:Label ID="lblEmpName" runat ="server" Text ='<%#Eval("FirstName")%>' ></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="#989898" 
                    BorderColor="Gray" 
                    Font-Bold="True" 
                    ForeColor="White" 
                    Height="20px" />
                    
                <RowStyle HorizontalAlign="Center" Height="20px" />
            </asp:GridView>
        </div>
    </form>
</body>
</html>


aspx.cs

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 Default7 : 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["constr"].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();
    }
    private void ShowEmpDetails()
    {
        string sQuery = "SELECT EmpDet.EmployeeID, EmpDet.FirstName, EmpDet.Qualification, Qual.QualificationCode FROM dbo.Employees 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;
        }
    }
    // 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 SqlDataReader GetDataReader(string sQuery)
    {
        SqlDataReader functionReturnValue = default(SqlDataReader);
        sqComm.CommandText = sQuery;
        sqComm.ExecuteNonQuery();
        functionReturnValue = sqComm.ExecuteReader();
        sqComm.Dispose();
        return functionReturnValue;
    }
}






at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

How to to select duplicate rows from sql server?

 SELECT * FROM Recruitment WHERE Email IN (SELECT Email FROM Recruitment GROUP BY Email HAVING COUNT(*) > 1); WITH CTE AS (     SELECT   ...