Edit and Delete record from sql server in asp.net gridview using rowcommand


Article Author : Tauseef Ahmad, Article Posted on : 8/3/2017 , Article Category : Asp.net


Download full source code here: Edit and Delete record from sql server in asp.net gridview using rowcommand
please share this article to help others :

Here in this article i have explained with an example and attached code that how to edit and delete record from sql server in asp.net c# using rowCommand. Before this article i have briefly explained that how to  save (insert) data in database using asp.net c# and how to   Get (retrieve) data from database using asp.net c# .

See this article save (insert) data in database using asp.net c# for briefly explained Database Portion (create database and design table with fields and datatypes) and Visual studio Portion (create new website and aspx page with screenshot).


Database Table structure in Sql server and connection string in webconfig:

 

 

   <connectionStrings>
    <add name="conString" connectionString="Data Source=tauseef;database=TestDemoDB; Integrated Security=true"/>
  </connectionStrings >

 

Basic Concept:

The scenrio of edit record is when we click on edit button the record will show in form for update.

Add the below code to Bind Gridview in the page.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"  OnRowCommand="GridView1_RowCommand"
   OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing"  PageSize="10" CellPadding="4" ForeColor="#333333" GridLines="None">
            <AlternatingRowStyle BackColor="White"  />
    <Columns>
        <asp:BoundField ItemStyle-Width="150px" DataField="ID" HeaderText="Student ID" >
<ItemStyle Width="150px"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField ItemStyle-Width="150px" DataField="Name" HeaderText="Student Name" >
<ItemStyle Width="150px"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField ItemStyle-Width="150px" DataField="Degree" HeaderText="Degree" >
   
<ItemStyle Width="150px"></ItemStyle>
        </asp:BoundField>
  
        <asp:TemplateField HeaderText ="Edit"  >
   <ItemTemplate >
   
<asp:LinkButton ID="edit" CommandName ="edit" runat ="server"  CommandArgument='<%#Eval("ID") %>'  Text ="Edit"></asp:LinkButton>
<asp:LinkButton ID="delete" CommandName ="delete" runat ="server" CommandArgument='<%#Eval("ID") %>'  Text ="delete"  ></asp:LinkButton>
  </ItemTemplate>
     </asp:TemplateField>
    </Columns>          
</asp:GridView>

      

in the above code the gridview has 3 data columns

  1.  Student ID
  2.  Student Name
  3.  Degree

some events  OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing"  and  OnRowCommand="GridView1_RowCommand" event handler write all the code for edit and delete.

i have added two LinkButtons which will act as edit and delete action perform. on the basis of CommandName it will decide that what should do edit or delete.

while CommandArgument='<%#Eval("ID") %>' pass row (Record ID) ID and on basis of this ID it passess to query  edit and delete action will perform

 

import below namspaces:

using System.Data;
using System.Data.SqlClient;

 

Now Binding the gridview and get connection from webconfig.

 

 String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            btnupdate.Visible = false;
            this.BindGrid();
        }
    }

 private void BindGrid()
    {    
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT ID, Name, Degree  FROM student"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                }
            }
        }
    }

 

Below is the screenshot of the GridView being populated using the above code

 

 

Now add code for edit and delete in rowCommand event handler. add below code in code behind page.

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        btnupdate.Visible = true;
        btnsave.Visible = false;
        if (e.CommandName == "edit")
        {
         
            int ID = Convert.ToInt32(e.CommandArgument);         
            ViewState["ID"] = ID;
         SqlConnection con = new SqlConnection(strConnString);
         SqlCommand cmd = new SqlCommand("SELECT ID, Name, Degree  FROM student where ID = " + ID, con);
            con.Open();
            SqlDataReader sdr = cmd.ExecuteReader();
          
            while (sdr.Read())
            {
                txtname.Text = sdr["Name"].ToString();
                txtdegree.Text = sdr["Degree"].ToString();             }
        }
        else if (e.CommandName == "delete")
        {
            
            int ID = Convert.ToInt32(e.CommandArgument); 
            SqlConnection con = new SqlConnection(strConnString);
            con.Open();
            SqlCommand cmd = new SqlCommand("delete from student where ID=" + ID, con);
            cmd.ExecuteNonQuery();
            con.Close();
            this.BindGrid();
        }

 

here in the row command event when edit button click the save button will be hide and update will be show and the code will execute inside CommandName=edit and data will show in form which is shown below and in the downloaed code.

i define viewstate and assign ID this ID is used in update button event.

Add the below code in page.

       <asp:Label ID="lblmessage" runat="server" Text=""></asp:Label><br />
        Name:
        <asp:TextBox ID="txtname" runat="server"></asp:TextBox> <br />
        Degree:
        <asp:TextBox ID="txtdegree" runat="server"></asp:TextBox><br />
        <asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" />
        <asp:Button ID="btnupdate" runat="server" Text="update" OnClick="btnupdate_Click" />

 

in the above code 2 textboxes for show data for updating Name and Degree field, label for show success message and buttons for save ( discuss in  save (insert) data in database using asp.net c#  ) and update button for update data.

now add update code behind the update button event.

 protected void btnupdate_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(strConnString);
      
        SqlCommand cmd = new SqlCommand("update student set Name ='" + txtname.Text + "',Degree='" + txtdegree.Text + "'where ID =" + ViewState["ID"], con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();         // this code of line just show message and empty textboxes after submitting data..
        lblmessage.Text = "Record Successfully Updated:";
        txtname.Text = "";
        txtdegree.Text = "";
        this.BindGrid(); // bind grid after updation data.
        btnsave.Visible = true;
        btnupdate.Visible = false;
    }

 

Finally just add empty rowediting and rowdeleting events.

 protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

 {

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {

    }

 

That’s it. With this the article comes to an end, hope you liked it  Download the sample at the top of article and Run.



Related Article


Add your valuable Comments


Success! Comment added successfully.

Name is required

Email is a required field Invalid Email
Message is required

Comments


{{cmt.Item1}}


{{cmt.Item2}}

{{cmt.Item3.slice(0,9)}}


ADS

Tags:

edit delete gridview onrowcommand asp.net sql server queries onrowediting onrowdeleting