Thursday, June 20, 2013

Gridview Edit, Update, Delete sample in ASP.NET

Here is a simple and complete source code to get an idea about how to Edit, Delete, Update data in Gridview using Gridview events like RowEditing, RowDeleting, RowUpdating. To run this application you need to create a table like shown below in SQL Table section. Updating data is just a straight forward method but to delete a record you need to set the DataKeyNames properties to the column name you using as a key in table. So that in the c# code you can retrieve the Id value when you click the Delete link in Gridview. You cannot use the method like i used in the RowUpdating event to the RowDeleting event to retrieve the Id value. It will only return null. Also don't forget to use IsPostBack in the Page_Load event.
SQL Table

Id               int                      
Name         nvarchar(MAX)
Address     nvarchar(MAX)

C# Code
string constr = @"Data Source=VENKAT;Initial Catalog=Test;Integrated Security=true";  
 protected void Page_Load(object sender, EventArgs e)  
 {  
   if (!IsPostBack)  
   {  
     bindGridView();  
   }  
 }  

 protected void bindGridView()  
 {  
   try  
   {  
     string query = "SELECT * FROM Grid ";  
     SqlConnection con = new SqlConnection(constr);  
     con.Open();  
     SqlDataAdapter da = new SqlDataAdapter(query, con);  
     DataSet ds = new DataSet();  
     da.Fill(ds);  
     GridView1.DataSource = ds;  
     GridView1.DataBind();  
   }  
   catch (Exception ex)  
   {  
   }  
 }  

 protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)  
 {  
   GridView1.EditIndex = e.NewEditIndex;  
   HiddenField1.Value = e.NewEditIndex.ToString();  
   bindGridView();  
 }  

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

 protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)  
 {  
   GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];  
   int index = Convert.ToInt32(row.RowIndex);  
   TextBox tId = (TextBox)row.FindControl("txtId");  
   TextBox tName = (TextBox)row.FindControl("txtName");  
   TextBox tAddress = (TextBox)row.FindControl("txtAddress");  
   GridView1.EditIndex = -1;  
   SqlConnection conn = new SqlConnection(constr);  
   conn.Open();  
   SqlCommand cmd = new SqlCommand("UPDATE Grid set Name='" + tName.Text + "',Address='" + tAddress.Text + "' WHERE Id=" + tId.Text + "", conn);  
   cmd.ExecuteNonQuery();  
   bindGridView();  
   conn.Close();  
 }  

 protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)  
 {  
   string id = GridView1.DataKeys[e.RowIndex].Value.ToString();  
   SqlConnection conn = new SqlConnection(constr);  
   conn.Open();  
   SqlCommand cmd = new SqlCommand("delete from Grid where Id=" + id + "", conn);  
   cmd.ExecuteNonQuery();  
   conn.Close();  
   bindGridView();  
 }  

Design Code:
<asp:GridView ID="GridView1" runat="server" DataKeyNames="Id"  
       onrowcancelingedit="GridView1_RowCancelingEdit"  
       onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"  
       onrowupdating="GridView1_RowUpdating" RowStyle-VerticalAlign="Top"  
       AutoGenerateColumns="False">  
       <RowStyle VerticalAlign="Top" />  
       <Columns>  
         <asp:TemplateField ShowHeader="False">  
           <EditItemTemplate>  
             <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True"  
               CommandName="Update" Text="Update"></asp:LinkButton>  
             &nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False"  
               CommandName="Cancel" Text="Cancel"></asp:LinkButton>  
           </EditItemTemplate>  
           <ItemTemplate>  
             <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False"  
               CommandName="Edit" Text="Edit"></asp:LinkButton>  
           </ItemTemplate>  
         </asp:TemplateField>  
         <asp:TemplateField ShowHeader="False">  
           <ItemTemplate>  
             <asp:LinkButton ID="LinkButton3" runat="server" CausesValidation="False"  
               CommandName="Delete" Text="Delete"></asp:LinkButton>  
           </ItemTemplate>  
         </asp:TemplateField>  
         <asp:TemplateField HeaderText="Id">  
             <ItemTemplate>  
               <%# Eval("Id")%>  
             </ItemTemplate>  
             <EditItemTemplate>  
               <asp:TextBox ID="txtId" runat="Server" Text='<%# Eval("Id") %>' Columns="30"></asp:TextBox>  
             </EditItemTemplate>  
         </asp:TemplateField>  
         <asp:TemplateField HeaderText="Name">  
             <ItemTemplate>  
               <%# Eval("Name")%>  
             </ItemTemplate>  
             <EditItemTemplate>  
               <asp:TextBox ID="txtName" runat="Server" Text='<%# Eval("Name") %>' Columns="30"></asp:TextBox>  
             </EditItemTemplate>  
         </asp:TemplateField>  
         <asp:TemplateField HeaderText="Address">  
             <ItemTemplate>  
               <%# Eval("Address")%>  
             </ItemTemplate>  
             <EditItemTemplate>  
               <asp:TextBox ID="txtAddress" runat="Server" Text='<%# Eval("Address") %>' Columns="30"></asp:TextBox>  
             </EditItemTemplate>  
         </asp:TemplateField>  
       </Columns>  
</asp:GridView>