Grid View Inline Editing with DropDownList
23:26
Connection String in Web.config
<connectionStrings>
<add name="strConnection" connectionString="Data Source=.;uid=sa;pwd=Surajit007;Initial Catalog=SurajitDB" providerName="System.Data.SqlClient" />
</connectionStrings>
SQL Scripts
USE [SurajitDB] GO CREATE TABLE [dbo].[Employee_Details_New] ( [UserId] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [UserName] [nvarchar](100) NULL, [City] [nvarchar](100) NULL, [Designation] [nvarchar](100) NULL, [CountryId] [int] NULL ) GO alter proc sp_getBindGridView AS Begin Select e.UserId ,e.UserName ,e.City ,e.Designation ,e.CountryId ,c.id ,c.[name] from Employee_Details_New e left join Countries c on c.id = e.CountryId; End GO alter proc sp_getCountries AS Begin select id,[name] from Countries; End GO alter proc sp_add_Employee_Details_New @UserName nvarchar(100), @City nvarchar(100), @Designation nvarchar(100), @CountryId nvarchar(100) AS Begin insert into Employee_Details_New(UserName,City,Designation,CountryId) values(@UserName,@City,@Designation,@CountryId); End GO alter proc sp_update_Employee_Details_New @City nvarchar(100), @Designation nvarchar(100), @CountryId nvarchar(100), @UserId nvarchar(100) AS Begin update Employee_Details_New set City = @City ,Designation = @Designation ,CountryId = @CountryId where UserId = @UserId; End GO alter proc sp_delete_Employee_Details_New @UserId nvarchar(100) AS Begin delete from Employee_Details_New where UserId = @UserId; End GO
GridVew_InlineEditing.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridVew_InlineEditing.aspx.cs" Inherits="MVC_Application.GridVew_InlineEditing" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Inline Editing</title>
<style type="text/css">
body{
background-color:#efefef;
}
.stydiv{
margin-left:20%;
}
.Gridview {
font-family: Verdana;
font-size: 10pt;
font-weight: normal;
color: black;
width:800px;
}
h2{
color:green;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="stydiv">
<h2>Grid View Inline Editing with DropDownList</h2>
<asp:Label ID="lblresult" runat="server"></asp:Label>
<asp:GridView
ID="gvDetails"
DataKeyNames="UserId,UserName"
runat="server"
AutoGenerateColumns="false"
CssClass="Gridview"
HeaderStyle-BackColor="#61A6F8"
ShowFooter="true"
HeaderStyle-Font-Bold="true"
HeaderStyle-ForeColor="White"
OnRowCancelingEdit="gvDetails_RowCancelingEdit"
OnRowDeleting="gvDetails_RowDeleting"
OnRowEditing="gvDetails_RowEditing"
OnRowUpdating="gvDetails_RowUpdating"
OnRowCommand="gvDetails_RowCommand"
OnRowDataBound="gvDetails_RowDataBound"
OnPageIndexChanging="gvDetails_PageIndexChanging"
OnSelectedIndexChanging="gvDetails_SelectedIndexChanging"
PageSize="5"
AllowPaging="true"
AllowSorting="true">
<Columns>
<asp:TemplateField>
<EditItemTemplate>
<asp:ImageButton ID="imgbtnUpdate" CommandName="Update" runat="server" ValidationGroup="validaitonEdit" ImageUrl="~/Images/update.jpg" ToolTip="Update" Height="20px" Width="20px" />
<asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg" ToolTip="Cancel" Height="20px" Width="20px" />
</EditItemTemplate>
<ItemTemplate>
<asp:ImageButton ID="imgbtnEdit" CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg" ToolTip="Edit" Height="20px" Width="20px" />
<asp:ImageButton ID="imgbtnDelete" CommandName="Delete" Text="Edit" runat="server" OnClientClick="return confirm('Are you sure?');" ImageUrl="~/Images/delete.jpg" ToolTip="Delete" Height="20px" Width="20px" />
</ItemTemplate>
<FooterTemplate>
<asp:ImageButton ID="imgbtnAdd" runat="server" ImageUrl="~/Images/AddNewitem.jpg" CommandName="AddNew" Width="30px" Height="30px" ToolTip="Add new User" ValidationGroup="validaiton" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="User Name">
<EditItemTemplate>
<asp:Label ID="lbleditusr" runat="server" Text='<%#Eval("Username") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblitemUsr" runat="server" Text='<%#Eval("UserName") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrusrname" runat="server" />
<asp:RequiredFieldValidator ID="rfvusername" runat="server" ControlToValidate="txtftrusrname" Text="*" ValidationGroup="validaiton" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="txtcity" runat="server" Text='<%#Eval("City") %>' />
<asp:RequiredFieldValidator ID="rfvcityEdit" runat="server" ControlToValidate="txtcity" Text="*" ValidationGroup="validaitonEdit" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblcity" runat="server" Text='<%#Eval("City") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrcity" runat="server" />
<asp:RequiredFieldValidator ID="rfvcity" runat="server" ControlToValidate="txtftrcity" Text="*" ValidationGroup="validaiton" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Designation">
<EditItemTemplate>
<asp:TextBox ID="txtDesg" runat="server" Text='<%#Eval("Designation") %>' />
<asp:RequiredFieldValidator ID="rfvdesignationEdit" runat="server" ControlToValidate="txtDesg" Text="*" ValidationGroup="validaitonEdit" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblDesg" runat="server" Text='<%#Eval("Designation") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrDesignation" runat="server" />
<asp:RequiredFieldValidator ID="rfvdesignation" runat="server" ControlToValidate="txtftrDesignation" Text="*" ValidationGroup="validaiton" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Location">
<EditItemTemplate>
<asp:DropDownList ID="ddlCountryEdit" runat="server" Width="100px" />
<asp:HiddenField ID="hdnCountry" runat="server" Value='<%#Eval("id") %>' />
<asp:RequiredFieldValidator ID="rfvCountryEdit" runat="server" ControlToValidate="ddlCountryEdit" InitialValue="0" Text="*" ValidationGroup="validaitonEdit" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblCountryName" runat="server" Text='<%#Eval("name") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlCountryAdd" runat="server" Width="100px" />
<asp:RequiredFieldValidator ID="rfvCountry" runat="server" ControlToValidate="ddlCountryAdd" InitialValue="0" Text="*" ValidationGroup="validaiton" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
GridVew_InlineEditing.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.Data.SqlClient;
using System.Drawing;
using System.Configuration;
namespace MVC_Application
{
public partial class GridVew_InlineEditing : System.Web.UI.Page
{
#region Declaration
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
#endregion
#region PageLoad
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmployeeDetails();
}
}
#endregion
#region Method
protected void BindEmployeeDetails()
{
try
{
using (SqlCommand cmd = new SqlCommand("sp_getBindGridView", con))
{
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
using (DataSet ds = new DataSet())
{
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount = gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "No Records Found";
}
}
}
}
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
#endregion
#region Event
protected void gvDetails_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
try
{
gvDetails.SelectedIndex = e.NewSelectedIndex;
BindEmployeeDetails();
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
try
{
gvDetails.EditIndex = e.NewEditIndex;
BindEmployeeDetails();
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
try
{
gvDetails.PageIndex = e.NewPageIndex;
BindEmployeeDetails();
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
try
{
int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();
TextBox txtcity = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtcity");
TextBox txtDesignation = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtDesg");
DropDownList ddlCountryEdit = (DropDownList)gvDetails.Rows[e.RowIndex].FindControl("ddlCountryEdit");
using (SqlCommand cmd = new SqlCommand("sp_update_Employee_Details_New", con))
{
cmd.CommandType = CommandType.StoredProcedure;
//--Add Parameters with Stored Procedure
cmd.Parameters.AddWithValue("@City", txtcity.Text.Trim());
cmd.Parameters.AddWithValue("@Designation", txtDesignation.Text.Trim());
cmd.Parameters.AddWithValue("@CountryId", ddlCountryEdit.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@UserId", userid);
//--Check Connection State
if (con.State == ConnectionState.Closed)
con.Open();
//--Execute
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
lblresult.ForeColor = Color.Green;
lblresult.Text = username + " Details Updated successfully";
gvDetails.EditIndex = -1;
}
}
BindEmployeeDetails();
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
try
{
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["UserId"].ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();
using (SqlCommand cmd = new SqlCommand("sp_delete_Employee_Details_New", con))
{
cmd.CommandType = CommandType.StoredProcedure;
//--Add Parameters with Stored Procedure
cmd.Parameters.AddWithValue("@UserId", userid);
//--Check Connection State
if (con.State == ConnectionState.Closed)
con.Open();
//--Execute
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
BindEmployeeDetails();
lblresult.ForeColor = Color.Red;
lblresult.Text = username + " details deleted successfully";
}
}
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
try
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtUsrname = (TextBox)gvDetails.FooterRow.FindControl("txtftrusrname");
TextBox txtCity = (TextBox)gvDetails.FooterRow.FindControl("txtftrcity");
TextBox txtDesgnation = (TextBox)gvDetails.FooterRow.FindControl("txtftrDesignation");
DropDownList ddlCountryAdd = (DropDownList)gvDetails.FooterRow.FindControl("ddlCountryAdd");
using (SqlCommand cmd = new SqlCommand("sp_add_Employee_Details_New", con))
{
cmd.CommandType = CommandType.StoredProcedure;
//--Add Parameters with Stored Procedure
cmd.Parameters.AddWithValue("@UserName", txtUsrname.Text.Trim());
cmd.Parameters.AddWithValue("@City", txtCity.Text.Trim());
cmd.Parameters.AddWithValue("@Designation", txtDesgnation.Text.Trim());
cmd.Parameters.AddWithValue("@CountryId", ddlCountryAdd.SelectedItem.Value.ToString());
//--Check Connection State
if (con.State == ConnectionState.Closed)
con.Open();
//--Execute
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
BindEmployeeDetails();
lblresult.ForeColor = Color.Green;
lblresult.Text = txtUsrname.Text + " Details inserted successfully";
}
else
{
lblresult.ForeColor = Color.Red;
lblresult.Text = txtUsrname.Text + " Details not inserted";
}
}
}
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e)
{
try
{
using (SqlCommand cmd = new SqlCommand("sp_getCountries", con))
{
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
using (DataSet ds = new DataSet())
{
da.Fill(ds);
if (e.Row.RowType == DataControlRowType.DataRow && gvDetails.EditIndex == e.Row.RowIndex)
{
DropDownList ddlprod = (DropDownList)e.Row.FindControl("ddlCountryEdit");
HiddenField hdnCountry = (HiddenField)e.Row.FindControl("hdnCountry");
ddlprod.DataSource = ds;
ddlprod.DataTextField = "name";
ddlprod.DataValueField = "id";
ddlprod.DataBind();
ddlprod.Items.Insert(0, new ListItem("--Select--", "0"));
ddlprod.Items.FindByValue(hdnCountry.Value).Selected = true;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
DropDownList ddlCountryAdd = (DropDownList)e.Row.FindControl("ddlCountryAdd");
ddlCountryAdd.DataSource = ds;
ddlCountryAdd.DataTextField = "name";
ddlCountryAdd.DataValueField = "id";
ddlCountryAdd.DataBind();
ddlCountryAdd.Items.Insert(0, new ListItem("--Select--", "0"));
}
}
}
}
}
catch (Exception ex)
{
lblresult.ForeColor = Color.Red;
lblresult.Text = ex.Message.ToString();
}
}
#endregion
}
}
0 comments