Click here to Skip to main content
15,901,666 members
Please Sign up or sign in to vote.
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
Updated 22-Aug-11 20:23pm
v3
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..

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();
        }

    }
}
 
Share this answer
 
Comments
akhter86 31-Jan-19 21:46pm    
thanks for providing soultion
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
  }
}
 
Share this answer
 
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
SQL
SELECT EMPLOYEE.EID,EMPLOYEE.ENAME,DESIGNATION.DESIGNAME 
FROM EMPLOYEE 
  INNER JOIN DESIGNATION ON EMPLOYEE.DESIGID=DESIGNATION.DESIGID
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900