Click here to Skip to main content
12,448,563 members (55,847 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET
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
Updated 22-Aug-11 20:23pm
v3
Comments
walterhevedeich 23-Aug-11 2:16am
   
What kind of help exactly do you need?
Mani@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.
Mani@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?
Mani@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?
Mani@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();
}
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Aspx
<%@ 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();
        }
 
    }
}
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  }
}
  Permalink  
v2
Comments
Mani@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
Mani@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
Mani@4289 23-Aug-11 3:14am
   
Why exception is occurred there?
Please clarify my doubt
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

SELECT EMPLOYEE.EID,EMPLOYEE.ENAME,DESIGNATION.DESIGNAME 
FROM EMPLOYEE 
  INNER JOIN DESIGNATION ON EMPLOYEE.DESIGID=DESIGNATION.DESIGID
  Permalink  
v2
Comments
Mani@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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160811.3 | Last Updated 14 Oct 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100