How to bind gridview using datatable with edit delete option : The Official Microsoft ASP.NET Forums

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewInsertEditUpdateDelete.aspx.cs" Inherits="GridViewInsertEditUpdateDelete" %>

<!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>Grid View Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
     DataKeyNames="Code, Type" OnRowCancelingEdit="GridView1_RowCancelingEdit"
      OnRowDataBound="GridView1_RowDataBound" OnRowEditing="GridView1_RowEditing"
       OnRowUpdating="GridView1_RowUpdating" OnRowCommand="GridView1_RowCommand"
        ShowFooter="True" OnRowDeleting="GridView1_RowDeleting">
<Columns>

<asp:TemplateField HeaderText="Name" SortExpression="Name"> <EditItemTemplate>
  <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
  <asp:TextBox ID="txtNewName" runat="server"></asp:TextBox> </FooterTemplate>
<ItemTemplate>
  <asp:Label ID="Label2" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Gender">
<EditItemTemplate>
  <asp:DropDownList ID="cmbGender" runat="server" SelectedValue='<%# Eval("Gender") %>'>
    <asp:ListItem Value="M" Text="Male"></asp:ListItem>
    <asp:ListItem Value="F" Text="Female"></asp:ListItem>
  </asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
  <asp:Label ID="lbGender" runat="server" Text='<%# Eval("Gender") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
  <asp:DropDownList ID="cmbNewGender" runat="server" >
    <asp:ListItem Selected="True" Text="Male" Value="M"></asp:ListItem>
    <asp:ListItem Text="Female" Value="F"></asp:ListItem> </asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="City">
<EditItemTemplate>
  <asp:TextBox ID="txtCity" runat="server" Text='<%# Bind("City") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
  <asp:TextBox ID="txtNewCity" runat="server" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
  <asp:Label ID="Label3" runat="server" Text='<%# Bind("City") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="State" SortExpression="State">
<EditItemTemplate>
  <asp:Label ID="Label1" runat="server" Text='<%# Eval("State") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
  <asp:TextBox ID="txtNewState" runat="server" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
  <asp:Label ID="Label4" runat="server" Text='<%# Bind("State") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Type">
<EditItemTemplate>
  <asp:DropDownList ID="cmbType" runat="server" DataTextField="Type" DataValueField="Type"> </asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
  <asp:Label ID="Label5" runat="server" Text='<%# Eval("Type") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
  <asp:DropDownList ID="cmbNewType" runat="server" DataTextField="Type" DataValueField="Type"> </asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Edit" ShowHeader="False">
<EditItemTemplate>
  <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Update"></asp:LinkButton>
  <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
  <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="AddNew" Text="Add New"></asp:LinkButton>
</FooterTemplate>
<ItemTemplate>
  <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" ShowHeader="True" />

</Columns>
</asp:GridView>
        <br />
        <br />
        
        <asp:Button ID="BtnSave" runat="server" Text="Save" OnClick="BtnSave_Click" />
        <br />
        </div>
    </form>
</body>
</html>

My Cs file;-

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class GridViewInsertEditUpdateDelete : System.Web.UI.Page
{

    CustomersCls customer = new CustomersCls();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            FillCustomerInGrid();
    }

    private void FillCustomerInGrid()
    {
        DataTable dtCustomer = customer.Fetch();

        if (dtCustomer.Rows.Count > 0)
        {
            GridView1.DataSource = dtCustomer;
            GridView1.DataBind();
        }
        else
        {
            dtCustomer.Rows.Add(dtCustomer.NewRow());
            GridView1.DataSource = dtCustomer;
            GridView1.DataBind();

            int TotalColumns = GridView1.Rows[0].Cells.Count;
            GridView1.Rows[0].Cells.Clear();
            GridView1.Rows[0].Cells.Add(new TableCell());
            GridView1.Rows[0].Cells[0].ColumnSpan = TotalColumns;
            GridView1.Rows[0].Cells[0].Text = "No Record Found";
        }
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {
            TextBox txtNewName = (TextBox)GridView1.FooterRow.FindControl("txtNewName");
            DropDownList cmbNewGender = (DropDownList)GridView1.FooterRow.FindControl("cmbNewGender");
            TextBox txtNewCity = (TextBox)GridView1.FooterRow.FindControl("txtNewCity");
            TextBox txtNewState = (TextBox)GridView1.FooterRow.FindControl("txtNewState");
            DropDownList cmbNewType = (DropDownList)GridView1.FooterRow.FindControl("cmbNewType");

            customer.Insert(txtNewName.Text, cmbNewGender.SelectedValue, txtNewCity.Text, txtNewState.Text, cmbNewType.SelectedValue);
            FillCustomerInGrid();
        } 
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DropDownList cmbType = (DropDownList)e.Row.FindControl("cmbType");

            if (cmbType != null)
            {
                cmbType.DataSource = customer.FetchCustomerType();
                cmbType.DataBind();
                cmbType.SelectedValue = GridView1.DataKeys[e.Row.RowIndex].Values[1].ToString();
            }
        }

        if (e.Row.RowType == DataControlRowType.Footer)
        {
            DropDownList cmbNewType = (DropDownList)e.Row.FindControl("cmbNewType");
            cmbNewType.DataSource = customer.FetchCustomerType();
            cmbNewType.DataBind();
        } 
    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        FillCustomerInGrid(); 
    }
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        customer.Delete(Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0].ToString()));
        FillCustomerInGrid(); 
    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        FillCustomerInGrid(); 
    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        TextBox txtName = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtName");
        DropDownList cmbGender = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("cmbGender");
        TextBox txtCity = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtCity");
        DropDownList cmbType = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("cmbType");

        customer.Update(Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0].ToString()), txtName.Text, cmbGender.SelectedValue, txtCity.Text, cmbType.SelectedValue);
        GridView1.EditIndex = -1;
        FillCustomerInGrid(); 
    }
    protected void BtnSave_Click(object sender, EventArgs e)
    {

    }
}

My Class file:-

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ClsCustomer
/// </summary>
public class CustomersCls
{

    private string cnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    private string sql;

    public CustomersCls()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public void Insert(string CustomerName, string Gender, string City, string State, string CustomerType)
    {
        string sql = "Insert Into Customer (Name, Gender, City, State, Type) Values ('" + CustomerName
            + "' , '" + Gender + "', '" + City + "', '" + State + "', '" + CustomerType + "')";
        SqlConnection conn = new SqlConnection(cnstr);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
    }

    public DataTable Fetch()
    {
        string sql = "Select * From Customer";
        SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

   

    public void Update(int CustomerCode, string CustomerName, string Gender, string City, string CustomerType)
    {
        string sql = "UPDATE Customer SET Name='" + CustomerName + "', Gender = '" + Gender
            + "', City='" + City + "', Type = '" + CustomerType + "' Where Code=" + CustomerCode;
        SqlConnection conn = new SqlConnection(cnstr);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
    }

    public void Delete(int CustomerCode)
    {
        string sql = "Delete Customer Where Code=" + CustomerCode;
        SqlConnection conn = new SqlConnection(cnstr);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
    }

    public DataTable FetchCustomerType()
    {
        string sql = "Select Distinct Type From Customer";
        SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }


}

My db table;-

This is the table in db:-

CREATE TABLE [dbo].[SampleCustomer](
    [Code] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [Gender] [varchar](10) NULL,
    [City] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [Type] [varchar](50) NULL
) ON [PRIMARY]

What i need is, user can manipulate with data's like add new,edit,delete which shouldn't affect the database table directly. What i need is i have save button on clicking this button the acutal data should be get affected in database.
llet me know how t do so?

Thanks In Advance ....

Reply

Resumepod

Member

56 Points

97 Posts

Re: How to bind gridview using datatable with edit delete option

Jan 15, 2013 11:33 AM|LINK

HI

Check this code. may be this code will be halpful for you.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:SQLConnectionStrings1 %>"
    SelectCommand="SELECT * FROM [table]"
    DeleteCommand="DELETE FROM [table] WHERE [column] = @ID">
  <DeleteParameters>
                <asp:Parameter Name="ID" Type="Int32" />
            </DeleteParameters>
    </asp:SqlDataSource>
                        <asp:ListView ID="GridView1" runat="server" DataSourceID="pics" DataKeyNames="ID">
   <ItemTemplate>
                      <asp:LinkButton ID="DeleteButton" runat="server" CommandName="Delete" ForeColor="Red" Text="Delete"
OnClientClick="return confirm('Are you certain you want to delete?');" />
                 
</ItemTemplate>
</asp:Gridview>

Reply

NadeemZee

Participant

942 Points

178 Posts

Re: How to bind gridview using datatable with edit delete option

Jan 15, 2013 12:32 PM|LINK

Hi Rasiq,

Please check below links you will find the delete update

http://www.codeproject.com/Articles/467788/Dynamically-adding-and-deleting-rows-from-ASP-NET

http://www.codeproject.com/Questions/407859/Edit-update-and-delete-from-grid-view-in-asp-net

http://www.codeproject.com/Questions/433279/edit-delete-update-cancel-in-gridview-asp-net

http://www.aspsnippets.com/Articles/Simple-Insert-Select-Edit-Update-and-Delete-in-ASPNet-GridView-control.aspx

http://code.msdn.microsoft.com/Insert-Update-Delete-rows-b0a2d4e2

http://www.aspdotnet-suresh.com/2011/02/how-to-inserteditupdate-and-delete-data.html

Please check the links and update your code according if you find your problem else reply me(Along with your problem) i will look into and give you solution.

Good Luck
Do FEAR (Face Everything And Rise)
Please mark as Answer if my post helps you..!

How to bind gridview using datatable with edit delete option RSS

14 replies

Ashraf007