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

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?

Guest Author: 30 Jul 2012

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?

Submit Article

Return to Article Index