Dropdownlist in Gridview -Insert,Edit,Update and Delete

Question

5.00/5 (2 votes)

See more:

I have two tables.

Table 1 :EMPLOYEE
EID - INT PRIMARY KEY IDENTITY
ENAME - VARCHAR(20)
DESIGID - INT FOREIGN KEY REFERENCES DESIGNATION(DESIGID)


Table 2 : DESIGNATION
DESIGID - INT PRIMARY KEY
DESIGNAME - VARCHAR(20)

The following are the inputs for the 2nd table

DESIGID       DESIGNAME
   1         Project manager
   2         Team Leader
   3         Software Engineer
   4             Tester

I want to use a gridview to display the columns (EID,ENAME,DESIGNAME) using join query

EID -Label
ENAME - Text Box
DESIGNAME - Dropdownlist (should populate the DESIGNAME from the table 2)

I have to insert , edit ,update and delete the records from the gridview

Can anyone please help me out in coding????

Posted 22-Aug-11 20:14pm

mibi@4289

Updated 22-Aug-11 20:23pm

v3

Add a Solution

Comments

walterhevedeich 23-Aug-11 2:16am

What kind of help exactly do you need?

mibi@4289 23-Aug-11 2:27am

i got this exercise in my interview. i couldn't do it using dropdownlist.
i am trying it but not getting.. Please help in this.

mibi@4289 23-Aug-11 2:40am

i want the coding for the above mentioned exercise. please help me out

Prerak Patel 23-Aug-11 2:19am

What have you tried? and where you are stuck?

mibi@4289 23-Aug-11 2:26am

i couldn't bind the dropdownlist on click of Edit link. Please help me out..

Prerak Patel 23-Aug-11 2:30am

How about sharing some code?

mibi@4289 23-Aug-11 2:37am

string conn = ConfigurationManager.ConnectionStrings["connect"].ToString();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillGrid();
}

}

protected void grv1_RowEditing(object sender, GridViewEditEventArgs e)
{
grv1.EditIndex = e.NewEditIndex;
FillGrid();

void FillGrid()
{
SqlConnection oconn = new SqlConnection(conn);
oconn.Open();
SqlDataAdapter oda = new SqlDataAdapter("select E.EID,E.ENAME,D.DESIGNAME from Employee E Inner join Designation D on D.DesigID =E.DesigID", oconn);
DataSet ds = new DataSet ();
oda.Fill(ds,"temp");
grv1.DataSource = ds.Tables[0];
grv1.DataBind();
oconn.Close();
}

protected void grv1_RowDataBound(object sender, GridViewRowEventArgs e)
{

SqlDataReader ddr = null;
SqlConnection oconn = new SqlConnection(conn);

SqlCommand ocmd = new SqlCommand("select*from designation", oconn);
oconn.Open();
ddr = ocmd.ExecuteReader(CommandBehavior.CloseConnection);
DropDownList ddl = new DropDownList();
if (grv1.EditIndex == e.Row.RowIndex)
{
ddl = (DropDownList)e.Row.Cells[2].FindControl("ddldesignation");
ddl.DataSource = ddr;
ddl.DataTextField = "DesigName";
ddl.DataValueField = "DesigId";
ddl.DataBind();
}

3 solutions

Solution 1

C#

protected void myGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
  if (myGridView.EditIndex == e.Row.RowIndex && e.Row.RowType==DataControlRowType.DataRow)
  {
    DropDownList myDDL = (DropDownList)e.Row.Cells[2].FindControl("myDropDownList");
    //databind here
  }
}

Posted 22-Aug-11 20:34pm

Prerak Patel

Updated 22-Aug-11 22:04pm

v2

Comments

mibi@4289 23-Aug-11 2:55am

i am getting an exception as Object reference not set to an instance of an object. Please help me
protected void grv1_RowDataBound(object sender, GridViewRowEventArgs e)
{

if (grv1 .EditIndex == e.Row.RowIndex)
{
SqlConnection oconn = new SqlConnection(conn);
oconn.Open();
SqlDataAdapter oda = new SqlDataAdapter ("select*from Designation", oconn);
DropDownList ddl = (DropDownList)e.Row.Cells[2].FindControl("ddldesignation");
DataSet ds = new DataSet();
oda.Fill(ds,"temp");
ddl.DataSource = ds.Tables[0].DefaultView ;
ddl.DataTextField = "DESIGNAME";
ddl.DataValueField = "DESIGID";
ddl.DataBind();
//databind here
}

Prerak Patel 23-Aug-11 3:01am

You should put the name of your dropdownlist instead of myDropDownList

Member 16136226 9-Nov-23 5:59am

you dont need to add datasource codebehind, you do it on html properties and if its working you can retrieve the table data, dont double the parameter, and you might have problem to find any rows if you dont have defined the datatable information and stored/ filled into gridview before executing the query. (eg. use session to define table datas into them on page load. also add a
command.Parameter.Add("@UserID", Session["UserID"] ?? "asd") /* or optionally DBNull.Value*/)
/*this will use 1 as field information if your query table requires a input on column, and you dont have it already, it tries now first use the stores information then if not found, it adds value you want to "asd" change it to what u want so it lets you use when you use @UserID in query, or final option if your code dont have the data, you can use this to use NULL -> /* or optionally DBNull.Value*/)
Dataset ds = new Dataset();
d.fill(ds, "table");
int affectrows = command.executenonquery();

command.executenonquery();

if (affectrows.executenonqueryU()>0) // or if(ds.tables["table"].rows[0].ToString == "something")
{
}
else
{
}
}
}

*** also consider of using datareader, to more safe.
using (sqlcommand command("UPDATE TOP(1) Table Set UserID = @UserID Where UserID = @UserID"), con))
{
Command.Parameter.ADD("", blaa);
using (DataAdapter command = new DataAdapter))
{
Dataset ds = new Dataset
command.Fill(ds, "table");
if (..)
{
//do this and do that if all ok.

}
else
{if u do not meet conditions, you do this.
}

mibi@4289 23-Aug-11 3:06am

I have done that also but again the same

ddl.DataSource = ds.Tables[0].DefaultView ;

In this line its shOWing exception

Prerak Patel 23-Aug-11 4:04am

You need to debug and find the reason.
Meanwhile try && e.Row.RowType==DataControlRowType.DataRow

mibi@4289 23-Aug-11 3:14am

Why exception is occurred there?
Please clarify my doubt

Solution 2

SQL

SELECT EMPLOYEE.EID,EMPLOYEE.ENAME,DESIGNATION.DESIGNAME 
FROM EMPLOYEE 
  INNER JOIN DESIGNATION ON EMPLOYEE.DESIGID=DESIGNATION.DESIGID

Posted 22-Aug-11 20:40pm

hitech_s

Updated 22-Aug-11 21:02pm

v2

Comments

mibi@4289 23-Aug-11 2:43am

Thank you,i got the join query, but i need the coding for binding dropdownlist while editing

thirupathi2011 13-Sep-11 6:44am

http://www.codeproject.com/Questions/244233/Dropdownlist-in-Gridview-Insert-Edit-Update-and-De/

Member 8916936 26-Sep-12 9:32am

Does anyone know why the code
DropDownList myDDL = (DropDownList)e.Row.Cells[2].FindControl("myDropDownList"); would fail by not being able to find the dropdown control in the grid view? I am pretty sure that i am using it as described. ??

Member 8916936 26-Sep-12 9:33am

Also this fails to work. I keep getting a null on the dropdown.
DropDownList ddl = (DropDownList)e.Row.FindControl("ddldesignation");
ddl is always null yet the grid is bring back a value for each cell.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mibi@4289 · Accepted Answer · 2011-08-31T05:11:00

Aspx

XML

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="grid2.aspx.cs" Inherits="Finalgrid.grid2" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:GridView ID="gv1" runat="server" AutoGenerateColumns ="False"
            onrowdatabound="gv1_RowDataBound" onrowdeleting="gv1_RowDeleting"
            onrowediting="gv1_RowEditing" DataKeyNames="EID" ShowFooter="True"
            onrowcommand="gv1_RowCommand" onrowupdating="gv1_RowUpdating"
            BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px"
            CellPadding="3" GridLines="None" CellSpacing="1"
            onrowcancelingedit="gv1_RowCancelingEdit" >
        <Columns >
        <asp:TemplateField HeaderText="EID">

        <ItemTemplate>
        <%# Eval("EID") %>
        </ItemTemplate>
        <EditItemTemplate >
            <asp:Label ID="lbleid" runat="server" Text='<%#Eval("EID") %>'></asp:Label>
            </EditItemTemplate>
            <FooterTemplate >
                <asp:LinkButton ID="lnkinsert" runat="server" Text= "Insert" CommandName="Insert" ></asp:LinkButton>
            </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="EMPLOYEE NAME">
            <ItemTemplate>
            <%#Eval("ENAME") %>
            </ItemTemplate>
            <EditItemTemplate >
                <asp:TextBox ID="txtename" runat="server" Text='<%#Eval("ENAME") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate >
                <asp:TextBox ID="txtaddname" runat="server"></asp:TextBox>
            </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="DESIGNATION">
            <ItemTemplate>
            <%#Eval("DESIGNAME") %>
            </ItemTemplate>
            <EditItemTemplate >
                <asp:DropDownList ID="ddldesignation" runat="server" width ="150px">
                </asp:DropDownList>
            </EditItemTemplate>
            <FooterTemplate >
                <asp:DropDownList ID="ddladddesig" runat="server" Width="150px">
                </asp:DropDownList>
            </FooterTemplate>
            </asp:TemplateField>
            <asp:CommandField ShowEditButton ="true" ControlStyle-ForeColor ="Blue " >
<ControlStyle ForeColor="Blue"></ControlStyle>
            </asp:CommandField>
            <asp:TemplateField>
            <ItemTemplate>
            <asp:LinkButton CommandName ="Delete" CommandArgument ='<%#Eval("EID") %>' runat="server" ID ="lnkdelete" Text="Delete" ></asp:LinkButton>
            </ItemTemplate>
            </asp:TemplateField>
            </Columns>

            <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
            <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
            <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
            <RowStyle BackColor="#DEDFDE" ForeColor="Black" />
            <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#594B9C" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#33276A" />

        </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;
using System.Configuration;
using System.Data.SqlClient;

namespace Finalgrid
{
    public partial class grid2 : System.Web.UI.Page
    {
        string sqlconn = ConfigurationManager.ConnectionStrings["connect"].ToString();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }

        }
        public void BindGrid()
        {
            SqlConnection oconn = new SqlConnection(sqlconn);
            oconn.Open();
            SqlCommand ocmd = new SqlCommand("SELECT E.EID,E.ENAME,D.DESIGNAME FROM EMPLOYEE E INNER JOIN DESIGNATION D ON E.DESIGID=D.DESIGID", oconn);
            SqlDataAdapter oda = new SqlDataAdapter(ocmd );
            SqlCommandBuilder builder = new SqlCommandBuilder(oda);
            DataSet ds = new DataSet();
            oda.Fill(ds);
            gv1.DataSource = ds;
            gv1.DataBind();

        }
      
        protected void gv1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            SqlConnection oconn = new SqlConnection(sqlconn);
            oconn.Open();
            SqlCommand ocmd = new SqlCommand("SELECT* FROM DESIGNATION", oconn);
            SqlDataAdapter oda = new SqlDataAdapter(ocmd);
            SqlCommandBuilder builder = new SqlCommandBuilder(oda);
            DataSet ds = new DataSet();
            oda.Fill(ds);
            DropDownList ddl = (DropDownList)e.Row.FindControl("ddldesignation");
            if (ddl != null)
            {
                ddl.DataSource = ds;
                ddl.DataValueField = "DESIGID";
                ddl.DataTextField = "DESIGNAME";
                ddl.DataBind();
            }
            if (e.Row.RowType == DataControlRowType.Footer)
            {
                DropDownList ddldesig = (DropDownList)e.Row.FindControl("ddladddesig");
                ddldesig.DataSource = ds;
                ddldesig.DataValueField = "DESIGID";
                ddldesig.DataTextField = "DESIGNAME";
                ddldesig.DataBind();

            }
        }

        protected void gv1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int EID = Convert.ToInt32(gv1.DataKeys[e.RowIndex].Value);
            SqlConnection oconn = new SqlConnection(sqlconn);
            oconn.Open();
            SqlCommand ocmd = new SqlCommand();
            ocmd.CommandText= "DELETE FROM EMPLOYEE WHERE EID=@EID";
            ocmd.Parameters.AddWithValue("@EID", EID);
            ocmd.Connection = oconn;
            ocmd.ExecuteNonQuery();
            oconn.Close();
            BindGrid();

        }

        protected void gv1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gv1.EditIndex = e.NewEditIndex;
            BindGrid();
        }

        protected void gv1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if(e.CommandName.Equals ("Insert"))
            {
                TextBox txtnewname = (TextBox)gv1.FooterRow.FindControl("txtaddname");
                DropDownList ddlnew = (DropDownList)gv1.FooterRow.FindControl("ddladddesig");
                SqlConnection oconn = new SqlConnection(sqlconn);
                oconn.Open();
                SqlCommand ocmd = new SqlCommand();
                ocmd.CommandText = "INSERT INTO EMPLOYEE VALUES ('"+ txtnewname .Text +"'," + ddlnew.SelectedValue +")";
                ocmd.Connection = oconn;
                ocmd.ExecuteNonQuery();
                BindGrid();
            }

        }

        protected void gv1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int EID = int.Parse (((Label)(gv1.Rows[e.RowIndex].Cells[1].FindControl ("lbleid"))).Text);
            string ENAME = ((TextBox)(gv1.Rows[e.RowIndex].Cells[1].FindControl("txtename"))).Text;
            int DESIGID = int.Parse  (((DropDownList )(gv1.Rows[e.RowIndex].Cells[1].FindControl ("ddldesignation"))).SelectedValue);
            SqlConnection oconn = new SqlConnection(sqlconn);
            oconn.Open();
            SqlCommand ocmd = new SqlCommand();
            ocmd.CommandText= "UPDATE EMPLOYEE SET ENAME=@ENAME ,DESIGID=@DESIGID WHERE EID=@EID ";
            ocmd.Parameters.AddWithValue ("@EID",EID);
            ocmd.Parameters.AddWithValue ("@ENAME",ENAME );
            ocmd.Parameters.AddWithValue  ("@DESIGID",DESIGID);
            ocmd.Connection = oconn;
            ocmd.ExecuteNonQuery();
            gv1.EditIndex =-1;
             BindGrid();


        }

        protected void gv1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            gv1.EditIndex = -1;
            BindGrid();
        }

    }
}

Dropdownlist in Gridview -Insert,Edit,Update and Delete

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us