Grid view save edit and update


This is a simple code for inserting,updating,and deleting the data in Gridview .

this is a simple code for editing,updating and deleting in gridview

Establish the Connection



This is the procedure for establishing the sql connection


string connection = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlDataAdapter DA;
DataSet ds = new DataSet();


ToBind Data



this is the format of binding the data to the grid
Here a Connection 'con' is instantiated and the connecction 'con' is opened
and closed inbetween the open and close lies the code for binding grid.


public void Bindgrid()
{
SqlConnection con = new SqlConnection(connection);
con.Open();
DA = new SqlDataAdapter("Select * from Category", con);
DA.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();
}


in your gridView property set show footer to 'TRUE"

To Save


to Save data the code is as follows

protected void BtnSave(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd = new SqlCommand("insert into Category values('" + TextBox1.Text + "','" + TextBox2.Text + "')", con);
//Bindgrid();
cmd.ExecuteNonQuery();

con.Close();
Bindgrid();
}


TO Update



protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];

TextBox textcategory = (TextBox)row.Cells[2].FindControl("TextBox4");

TextBox textDescription = (TextBox)row.Cells[3].FindControl("TextBox6");

SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd = new SqlCommand("update Category set Description='" + textDescription.Text + "' where categoryName='" + textcategory.Text + "'", con);
cmd.ExecuteNonQuery();

con.Close();
GridView1.EditIndex = -1;
Bindgrid();

}

Canceling Edit


this is nothing but to cancel the edit


protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
Bindgrid();
}


the procedure for editing is as follows


protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
Bindgrid();
}



TO Delete



here is the snippet for deleting the data in the gridview


protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
//TextBox textcategory = (TextBox)row.FindControl("TextBox4");
Label lbldeleteID = (Label)row.FindControl("Label1");
SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd = new SqlCommand("delete from Category where categoryName='" + lbldeleteID.Text + "'", con);
cmd.ExecuteNonQuery();
Bindgrid();
con.Close();

}


To insert Element using RowCommand



this is an alternate method to insert a data using through the gridview row
and it is as follows


protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
TextBox txtcategoryName = (TextBox)GridView1.FooterRow.FindControl("TextBox8");
TextBox txtDescription = (TextBox)GridView1.FooterRow.FindControl("TextBox7");
//Label lblDescription = (Label)GridView1.FooterRow.FindControl("Label2");
SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd = new SqlCommand("insert into Category values ('"+txtcategoryName.Text+"','"+txtDescription.Text+"')",con );
cmd.ExecuteNonQuery();
con.Close();
Bindgrid();
}


The sample Code is as follows

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;


public partial class Default2 : System.Web.UI.Page
{
string connection = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlDataAdapter DA;
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{

Bindgrid();
}
}
protected void BtnSave(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd = new SqlCommand("insert into Category values('" + TextBox1.Text + "','" + TextBox2.Text + "')", con);
//Bindgrid();
cmd.ExecuteNonQuery();

con.Close();
Bindgrid();
}
public void Bindgrid()
{
SqlConnection con = new SqlConnection(connection);
con.Open();
DA = new SqlDataAdapter("Select * from Category", con);
DA.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{

}
//protected void Button1_Click(object sender, EventArgs e)
//{
// SqlConnection con = new SqlConnection();
// con.Open();
// SqlCommand cmd = new SqlCommand("delete * from Category where Category='"+TextBox1.Text+"',Description='"+TextBox2.Text+"')",con);
// cmd.ExecuteNonQuery();
// con.Close();

//}
//protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
//{
// GridView1.EditIndex = e.NewEditIndex;
// Bindgrid();
//}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];

TextBox textcategory = (TextBox)row.Cells[2].FindControl("TextBox4");

TextBox textDescription = (TextBox)row.Cells[3].FindControl("TextBox6");

SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd = new SqlCommand("update Category set Description='" + textDescription.Text + "' where categoryName='" + textcategory.Text + "'", con);
cmd.ExecuteNonQuery();

con.Close();
GridView1.EditIndex = -1;
Bindgrid();

}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
Bindgrid();
}
protected void TextBox6_TextChanged(object sender, EventArgs e)
{

}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
Bindgrid();
}
protected void Button7_Click(object sender, EventArgs e)
{

}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
//TextBox textcategory = (TextBox)row.FindControl("TextBox4");
Label lbldeleteID = (Label)row.FindControl("Label1");
SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd = new SqlCommand("delete from Category where categoryName='" + lbldeleteID.Text + "'", con);
cmd.ExecuteNonQuery();
Bindgrid();
con.Close();

}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
TextBox txtcategoryName = (TextBox)GridView1.FooterRow.FindControl("TextBox8");
TextBox txtDescription = (TextBox)GridView1.FooterRow.FindControl("TextBox7");
//Label lblDescription = (Label)GridView1.FooterRow.FindControl("Label2");
SqlConnection con = new SqlConnection(connection);
con.Open();
SqlCommand cmd = new SqlCommand("insert into Category values ('"+txtcategoryName.Text+"','"+txtDescription.Text+"')",con );
cmd.ExecuteNonQuery();
con.Close();
Bindgrid();
}
}


do check the attachment for gridview reference


Attachments

  • insert delete update (40129-1166-grid.txt)
  • Comments

    Author: Kapil Verma25 Feb 2011 Member Level: Silver   Points : 0

    nice code friend

    Author: ghjjfgnj26 Apr 2011 Member Level: Silver   Points : 1

    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;

    public partial class _Default : System.Web.UI.Page
    {
    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter adp;
    DataSet ds;
    SqlDataReader dr;
    protected void Page_Load(object sender, EventArgs e)
    {
    Panel1.Visible = false;
    Panel2.Visible = false;
    Panel3.Visible = false;
    if (!IsPostBack)
    {
    bind();
    }
    }
    void bind()
    {

    con = new SqlConnection("server=localhost;uid=sa;pwd=student;database=master;");
    con.Open();
    adp = new SqlDataAdapter("exec deptselct ",con);

    ds = new DataSet();
    adp.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();

    con.Close();

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
    Panel2.Visible = true;

    }
    protected void Button4_Click(object sender, EventArgs e)
    {
    con = new SqlConnection("server=localhost;uid=sa;pwd=student;database=master;");

    con.Open();
    cmd = new SqlCommand();
    cmd.CommandText = "deptins";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = con;
    cmd.Parameters.Add("@name", SqlDbType.VarChar, 20).Value = TextBox1.Text;
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = TextBox2.Text;
    dr = cmd.ExecuteReader();
    bind();
    con.Close();
    }

    protected void Button5_Click(object sender, EventArgs e)
    {
    con = new SqlConnection("server=localhost;uid=sa;pwd=student;database=master;");

    con.Open();
    cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "deptdl";
    cmd.Connection = con;


    cmd.Parameters.Add("@id", SqlDbType.Int).Value = TextBox3.Text;
    dr = cmd.ExecuteReader();
    bind();
    con.Close();


    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
    GridView1.PageIndex = e.NewPageIndex;
    bind();
    }
    protected void Button1_Click1(object sender, EventArgs e)
    {
    Panel1.Visible = true;

    }
    protected void Button3_Click(object sender, EventArgs e)
    {
    Panel3.Visible = true;
    }
    protected void Button6_Click(object sender, EventArgs e)
    {
    con = new SqlConnection("server=localhost;database=master;uid=sa;pwd=student;");
    con.Open();
    cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "deptup";
    cmd.Parameters.Add("@name",SqlDbType.VarChar,20).Value=TextBox4.Text;
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = TextBox5.Text;
    dr = cmd.ExecuteReader();
    bind();
    con.Close();
    }
    }

    Author: Gaurav Balyan24 Oct 2011 Member Level: Bronze   Points : 0

    very nice tutorial really helped me

    Guest Author: Satish MB28 Dec 2011

    Good Source
    I have to ask some suggestion from you. Here is my some VB codes
    here i have used the code
    DataGridView1.Rows(i).Cells(1).Value = dr("Name")
    Is it possible to use the Column name instead of cells index num ie, Cells(1)

    pls help in this matter.

    Public Function intilization2()

    Try
    If con.State = ConnectionState.Open Then con.Close()
    con.Open()
    Dim adp As New OleDbDataAdapter("select * from Admission where Activated=Yes", con)
    Dim dr As DataRow
    Dim dt As DataTable
    Dim ds As New DataSet
    adp.Fill(ds)
    dt = ds.Tables(0)
    Dim rowscount As Integer
    rowscount = ds.Tables(0).Rows.Count
    Dim i As Integer = 0
    For Each dr In dt.Rows
    i = DataGridView1.Rows.Add()
    DataGridView1.Rows(i).Cells(0).Value = dr("ID_NO")
    DataGridView1.Rows(i).Cells(1).Value = dr("Name")
    DataGridView1.Rows(i).Cells(2).Value = dr("Batch")
    DataGridView1.Rows(i).Cells(3).Value = dr("Monthly_Fee")
    DataGridView1.Rows(i).Cells(4).Value = Txt_Date.Text
    DataGridView1.Rows(i).Cells(5).Value = dr("Monthly_Fee")
    Next
    con.Close()
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try
    End Function

    Guest Author: devarajan31 Dec 2011

    pls send some ifelse programmes with output in asp.net

    Guest Author: Puran19 Jan 2012

    This post has cleared all my doubts..
    thanks

    Guest Author: Jivan20 Jan 2012

    I'm dynamically adding BoundFields and TemplateFields to a GridView. This involves expensive (database-)operations, so I can't do it on each postback. Is there a possible way to persist the final state across postbacks?

    Author: Parthiban30 Jul 2012 Member Level: Bronze   Points : 0

    really helpful ..thanq..

    Guest Author: suresh01 Nov 2012

    it's simply ow-some.....thanks a lot...

    Author: Phagu Mahato01 Oct 2013 Member Level: Gold   Points : 1

    You can use below code snippet for Grid view save edit and update
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing
    SqlConnection con = new SqlConnection("Data Source=DNSExample;Integrated Security=true;Initial Catalog=MyExampleDB");
    protected void Page_Load(object sender, EventArgs ex)
    {
    if (!IsPostBack)
    {
    BindMembersdetails();
    }
    }
    protected void BindMembersdetails()
    {
    con.Open();
    SqlCommand cmd = new SqlCommand("Select * from Members_details", con);
    SqlDataAdapter data1 = new SqlDataAdapter(cmd);
    DataSet dset = new DataSet();
    data1.Fill(dset);
    con.Close();
    if (dset.Tables[0].Rows.Count > 0)
    {
    GridViewDetails.DataSource = dset;
    GridViewDetails.DataBind();
    }
    else
    {
    dset.Tables[0].Rows.Add(dset.Tables[0].NewRow());
    GridViewDetails.DataSource = dset;
    GridViewDetails.DataBind();
    int columncount = GridViewDetails.Rows[0].Cells.Count;
    GridViewDetails.Rows[0].Cells.Clear();
    GridViewDetails.Rows[0].Cells.Add(new TableCell());
    GridViewDetails.Rows[0].Cells[0].ColumnSpan = col_count;
    GridViewDetails.Rows[0].Cells[0].Text = "No Recordset Found";
    }
    }
    protected void GridViewDetails_RowEditing(object sender, GridViewEditEventArgs ex)
    {
    GridViewDetails.EditIndex = ex.NewEditIndex;
    BindMembersdetails();
    }
    protected void GridViewDetails_RowUpdate(object sender, GridViewUpdateEventArgs ex)
    {
    int memberid = Convert.ToInt32(GridViewDetails.DataKeys[e.RowIndex].Value.ToString());
    string membername = GridViewDetails.DataKeys[e.RowIndex].Values["memberName"].ToString();
    TextBox txtcity = (TextBox)GridViewDetails.Rows[e.RowIndex].FindControl("txtcity");
    TextBox txtDesignation = (TextBox)GridViewDetails.Rows[e.RowIndex].FindControl("txtDesg");
    con.Open();
    SqlCommand cmd = new SqlCommand("update Employee_Details set MCity='" + txtcity.Text + "',MDesignation='" + txtDesignation.Text + "' where memberId=" + memberid, con);
    cmd.ExecuteNonQuery();
    con.Close();
    labelresult.ForeColor = Color.Green;
    labelresult.Text = membername + " Member Details Updated successfully";
    GridViewDetails.EditIndex = -1;
    BindMembersdetails();
    }
    protected void GridViewDetails_RowCancelEdit(object sender, GridViewCancelEditEventArgs ex)
    {
    GridViewDetails.EditIndex = -1;
    BindMembersdetails();
    }
    protected void GridViewDetails_RowDelete(object sender, GridViewDeleteEventArgs ex)
    {
    int memberid = Convert.ToInt32(GridViewDetails.DataKeys[ex.RowIndex].Values["memberId"].ToString());
    string membername = GridViewDetails.DataKeys[e.RowIndex].Values["memberName"].ToString();
    con.Open();
    SqlCommand cmd = new SqlCommand("delete from Member_Details where memberId=" + memberid, con);
    int result = cmd.ExecuteNonQuery();
    con.Close();
    if (result == 1)
    {
    BindMembersdetails();
    labelresult.ForeColor = Color.Red;
    labelresult.Text = membername + " Members details deleted successfully";
    }
    }
    protected void GridViewDetails_RowCommand(object sender, GridViewCommandEventArgs ex)
    {
    if(e.CommandName.Equals("AddNew"))
    {
    TextBox txtUsrname = (TextBox)GridViewDetails.FooterRow.FindControl("txtftrusrname");
    TextBox txtCity = (TextBox)GridViewDetails.FooterRow.FindControl("txtftrcity");
    TextBox txtDesgnation = (TextBox) GridViewDetails.FooterRow.FindControl("txtftrDesignation");
    con.Open();
    SqlCommand cmd =
    new SqlCommand(
    "insert into Member_Details(memberName,MCity,MDesignation) values('" + txtUsrname.Text + "','" +
    txtCity.Text + "','" + txtDesgnation.Text + "')", con);
    int result= cmd.ExecuteNonQuery();
    con.Close();
    if(result==1)
    {
    BindMembersdetails();
    labelresult.ForeColor = Color.Green;
    labelresult.Text = txtUsrname.Text + "Members Details inserted successfully";
    }
    else
    {
    labelresult.ForeColor = Color.Red;
    labelresult.Text = txtUsrname.Text + " Members Details not inserted";
    }
    }
    }

    Guest Author: Ajay05 Dec 2013

    Where is the Gridview[dot]aspx page attachment?



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: