Post
Ask Question

Update and Delete Database Table Using GridView in ASP.NET

First create a table with the following two fields:

  1. id int (auto increment)
  2. name nvarchar(100)

Save as Test

table design

Insert some values into this new table.

The table will looks as in this:

table

Then go to Visual Studio then go to New > Website then choose Empty Website.

Provide your website a name, if you want to change the location of your website then you can change your location.

The next step is to add a webform with the following code.

Defult.aspx

  1. <!DOCTYPE html>  
  2.   
  3. <html xmlns="http://www.w3.org/1999/xhtml">  
  4. <head runat="server">  
  5.     <title></title>  
  6. </head>  
  7. <body>  
  8.     <form id="form1" runat="server">  
  9.     <div>  
  10.       
  11.         <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">  
  12.             <Columns>  
  13.                     <asp:TemplateField>  
  14.                         <HeaderTemplate>  
  15.                             ID  
  16.                         </HeaderTemplate>  
  17.                         <ItemTemplate>  
  18.                             <%# Eval("id") %>  
  19.                         </ItemTemplate>  
  20.                         <EditItemTemplate>  
  21.                             <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("id") %>' ReadOnly="true"></asp:TextBox>  
  22.                         </EditItemTemplate>  
  23.                     </asp:TemplateField>  
  24.                 <asp:TemplateField>  
  25.                         <HeaderTemplate>  
  26.                             Name  
  27.                         </HeaderTemplate>  
  28.                         <ItemTemplate>  
  29.                             <%# Eval("name") %>  
  30.                         </ItemTemplate>  
  31.                         <EditItemTemplate>  
  32.                             <asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("name") %>' ></asp:TextBox>  
  33.                         </EditItemTemplate>  
  34.                     </asp:TemplateField>  
  35.                 <asp:TemplateField>  
  36.                         <HeaderTemplate>  
  37.                             Edit  
  38.                         </HeaderTemplate>  
  39.                         <ItemTemplate>  
  40.                              <asp:Button ID="edit" runat="server"  CommandName="Edit" formnovalidate Height="28" Text="Edit" />  
  41.                         </ItemTemplate>  
  42.                         <EditItemTemplate>  
  43.                              <asp:Button ID="update" runat="server"  CommandName="Update" formnovalidate Text="Update" />   
  44.                              <asp:Button ID="cancel" runat="server"  CommandName="Cancel" formnovalidate  Text="Cancel"/>  
  45.                         </EditItemTemplate>  
  46.                     </asp:TemplateField>  
  47.                     <asp:TemplateField>  
  48.                         <HeaderTemplate>  
  49.                             Delete  
  50.                         </HeaderTemplate>  
  51.                         <ItemTemplate>  
  52.                             <asp:Button ID="delete" runat="server"   CommandName="Delete" formnovalidate Text="Delete"/>  
  53.                         </ItemTemplate>  
  54.                          
  55.                     </asp:TemplateField>  
  56.                 </Columns>  
  57.         </asp:GridView>  
  58.       
  59.         <asp:Label ID="Label1" runat="server"></asp:Label>  
  60.       
  61.     </div>  
  62.     </form>  
  63. </body>  
  64. </html>  
Then in the code behind for defult.asp.cs:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data.SqlClient;  
  8. using System.Data;  
  9.   
  10. public partial class _Default : System.Web.UI.Page  
  11. {  
  12.     protected void Page_Load(object sender, EventArgs e)  
  13.     {  
  14.         if(!IsPostBack)  
  15.         {  
  16.             ShowTest();  
  17.         }  
  18.     }  
  19.     protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)  
  20.     {  
  21.         string index = GridView1.DataKeys[e.RowIndex].Value.ToString();  
  22.         int id = int.Parse(index);  
  23.         TextBox name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");  
  24.         SqlConnection con = new SqlConnection(connection());  
  25.         SqlCommand cmd = new SqlCommand("update Test set name='"+name.Text+"' where id=" + id + "", con);  
  26.         con.Open();  
  27.         int i = cmd.ExecuteNonQuery();  
  28.         con.Close();  
  29.         if (i > 0)  
  30.         {  
  31.             Label1.Text = "data updated sucessfully";  
  32.         }  
  33.         else  
  34.         {  
  35.             Label1.Text = "data not updated";  
  36.         }  
  37.         GridView1.EditIndex = -1;  
  38.         ShowTest();  
  39.     }  
  40.      
  41.     protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)  
  42.     {  
  43.         GridView1.EditIndex = e.NewEditIndex;  
  44.         ShowTest();  
  45.     }  
  46.     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)  
  47.     {  
  48.         string index = GridView1.DataKeys[e.RowIndex].Value.ToString();  
  49.         int id=int.Parse(index);  
  50.         SqlConnection con = new SqlConnection(connection());  
  51.         SqlCommand cmd = new SqlCommand("delete from Test where id="+id+"",con);  
  52.         con.Open();  
  53.         int i=cmd.ExecuteNonQuery();  
  54.         con.Close();  
  55.         if(i>0)  
  56.         {  
  57.             Label1.Text = "data deleted sucessfully";  
  58.         }  
  59.         else  
  60.         {  
  61.             Label1.Text = "data not deleted";  
  62.         }  
  63.         GridView1.EditIndex = -1;  
  64.         ShowTest();  
  65.     }  
  66.     protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)  
  67.     {  
  68.         GridView1.EditIndex = -1;  
  69.         ShowTest();  
  70.     }  
  71.     public void ShowTest()  
  72.     {  
  73.         SqlConnection con = new SqlConnection(connection());  
  74.         SqlDataAdapter da = new SqlDataAdapter("select * from Test", con);  
  75.         DataTable dt = new DataTable();  
  76.         da.Fill(dt);  
  77.         GridView1.DataSource = dt;  
  78.         GridView1.DataBind();  
  79.   
  80.     }  
  81.     public string connection()  
  82.     {  
  83.         string con = "Data Source=localhost;Initial Catalog=UsersDB;Integrated Security=True;";  
  84.         return con;  
  85.     }  
  86. }  
Note: UsersDB is my database name. You can (I did) use Windows Authentication.

The final way it looks in the browser:

output

 


Similar Articles