ASP.NET GridView: how to edit and delete data records

Question

Hi, I used gridview to create a table. Is there any way to implement editing and deleting. I have already done this in PHP. The method I would like to use is to create two more columns in the table with edit and delete buttons on each row. Then, when the buttons are pressed, it passes the “identifier” through the URL and can edit or delete. Not quite sure how to do this in asp.net web forms. Below is my code for the table. Thanks.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"> <Columns> <asp:BoundField HeaderText="Surgery" DataField="surgery" /> <asp:BoundField HeaderText="PatientID" DataField="patientID" /> <asp:BoundField HeaderText="Location" DataField="location" /> </Columns> 

 SqlCommand cmd = new SqlCommand("select surgery, patientID, location from details", conn); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); conn.Close(); GridView1.DataSource = dt; GridView1.DataBind(); 

Answer 1

GridView supports these operations. You can add a CommandField that will contain command buttons or LinkButtons buttons (you can choose the type of button and assign the text to each button). The patientID field must be included in the DataKeyNames GridView property in order to receive it when the time comes to update or delete a record in the database.

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="patientID" OnRowEditing="GridView1_RowEditing" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowUpdating="GridView1_RowUpdating" OnRowDeleting="GridView1_RowDeleting" > <Columns> <asp:CommandField ShowEditButton="true" ShowCancelButton="true" ShowDeleteButton="true" /> <asp:BoundField HeaderText="Surgery" DataField="surgery" /> ... </Columns> 

Then you need to handle several events in the code:

 // The RowEditing event is called when data editing has been requested by the user // The EditIndex property should be set to the row index to enter edit mode protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { GridView1.EditIndex = e.NewEditIndex; BindData(); } // The RowCancelingEdit event is called when editing is canceled by the user // The EditIndex property should be set to -1 to exit edit mode protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { GridView1.EditIndex = -1; BindData(); } // The RowUpdating event is called when the Update command is selected by the user // The EditIndex property should be set to -1 to exit edit mode protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { int patientID = (int)e.Keys["patientID"] string surgery = (string)e.NewValues["surgery"]; string location = (string)e.NewValues["location"]; // Update here the database record for the selected patientID GridView1.EditIndex = -1; BindData(); } // The RowDeleting event is called when the Delete command is selected by the user protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { int patientID = (int)e.Keys["patientID"] // Delete here the database record for the selected patientID BindData(); } 

Since the data must be bound to the GridView at the end of each of these event handlers, you can do this in the function of the BindData utility, which should also be called when the page loads initially:

 private void BindData() { SqlCommand cmd = new SqlCommand("select surgery, patientID, location from details", conn); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); conn.Close(); GridView1.DataSource = dt; GridView1.DataBind(); } protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindData(); } } 

Answer 2

 using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Configuration; using System.Data.SqlClient; namespace FinalYearProject { public partial class MBooking : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { this.BindGrid(); } } private void BindGrid() { string constr = ConfigurationManager.ConnectionStrings["cmt"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("Customers_CRUD")) { cmd.Parameters.AddWithValue("@Action", "SELECT"); using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; sda.SelectCommand = cmd; using (DataTable dt = new DataTable()) { sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } } } } } protected void Insert(object sender, EventArgs e) { string Username = txtUsername.Text; string Provincename = txtProvinceName.Text; string Cityname = txtCityname.Text; string Number = txtNumber.Text; string Name = txtName.Text; string ContentType = txtContentType.Text; string Data = txtData.Text; string constr = ConfigurationManager.ConnectionStrings["cmt"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("Customers_CRUD")) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Action", "INSERT"); cmd.Parameters.AddWithValue("@Username", Username); cmd.Parameters.AddWithValue("@Provincename ", Provincename); cmd.Parameters.AddWithValue("@Cityname", Cityname); cmd.Parameters.AddWithValue("@Number", Number); cmd.Parameters.AddWithValue("@Name", Name); cmd.Parameters.AddWithValue("@ContentType", ContentType); //cmd.Parameters.AddWithValue("@Data", Data); cmd.Parameters.AddWithValue("@Data", SqlDbType.VarBinary).Value = new Byte[] { 0xDE, 0xAD, 0xBE, 0xEF }; cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } this.BindGrid(); } protected void OnRowEditing(object sender, GridViewEditEventArgs e) { GridView1.EditIndex = e.NewEditIndex; this.BindGrid(); } protected void OnRowCancelingEdit(object sender, EventArgs e) { GridView1.EditIndex = -1; this.BindGrid(); } protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e) { GridViewRow row = GridView1.Rows[e.RowIndex]; int BId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]); string Username = (row.FindControl("txtUserName") as TextBox).Text; string Provincename = (row.FindControl("txtProvincename") as TextBox).Text; string Cityname = (row.FindControl("txtCityname") as TextBox).Text; string Number = (row.FindControl("txtNumber") as TextBox).Text; string Name = (row.FindControl("txtName") as TextBox).Text; string ContentType = (row.FindControl("txtContentType") as TextBox).Text; string Data = (row.FindControl("txtData") as TextBox).Text; string constr = ConfigurationManager.ConnectionStrings["cmt"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("Customers_CRUD")) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Action", "UPDATE"); cmd.Parameters.AddWithValue("@BId", BId); cmd.Parameters.AddWithValue("@Username", Username); cmd.Parameters.AddWithValue("@Provincename ", Provincename); cmd.Parameters.AddWithValue("@Cityname", Cityname); cmd.Parameters.AddWithValue("@Number", Number); cmd.Parameters.AddWithValue("@Name", Name); cmd.Parameters.AddWithValue("@ContentType",ContentType) ; cmd.Parameters.AddWithValue("@Data", SqlDbType.VarBinary).Value = new Byte[] { 0xDE, 0xAD, 0xBE, 0xEF }; //cmd.Parameters.AddWithValue("@ContentType", SqlDbType.VarBinary, -1); //cmd.Parameters.AddWithValue("@Data", SqlDbType.VarBinary, -1); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } GridView1.EditIndex = -1; this.BindGrid(); } protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; this.BindGrid(); } protected void OnRowDataBound(object sender, GridViewRowEventArgs e) { //if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != GridView1.EditIndex) //{ // (e.Row.Cells[2].Controls[2] as LinkButton).Attributes["onclick"] = "return confirm('Do you want to delete this row?');"; //} } protected void DownloadFile(object sender, EventArgs e) { int id = int.Parse((sender as LinkButton).CommandArgument); byte[] bytes; string fileName, contentType; string constr = ConfigurationManager.ConnectionStrings["cmt"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = "select Name, Data, ContentType from tblbooking where BId=@BId "; cmd.Parameters.AddWithValue("@BId",id); cmd.Connection = con; con.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { sdr.Read(); bytes = (byte[])sdr["Data"]; contentType = sdr["ContentType"].ToString(); fileName = sdr["Name"].ToString(); } con.Close(); } } Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = contentType; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(bytes); Response.Flush(); Response.End(); } protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e) { int BId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]); string constr = ConfigurationManager.ConnectionStrings["cmt"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("Customers_CRUD")) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Action", "DELETE"); cmd.Parameters.AddWithValue("@BId", BId); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } this.BindGrid(); } } } 

Answer 3

 And Store Procedure is: USE [DemoProjet] GO /****** Object: StoredProcedure [dbo].[Customers_CRUD] Script Date: 11-Jan-17 2:57:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Customers_CRUD] @Action VARCHAR(10) ,@BId INT = NULL ,@Username VARCHAR(50) = NULL ,@Provincename VARCHAR(50) = NULL ,@Cityname VARCHAR(50) = NULL ,@Number VARCHAR(50) = NULL ,@Name VARCHAR(50) = NULL ,@ContentType VARCHAR(50) = NULL ,@Data VARBINARY(MAX) = NULL AS BEGIN SET NOCOUNT ON; --SELECT IF @Action = 'SELECT' BEGIN SELECT BId , Username,Provincename,Cityname,Number,Name,ContentType, Data FROM tblbooking END --INSERT IF @Action = 'INSERT' BEGIN INSERT INTO tblbooking(Username,Provincename,Cityname,Number,Name,ContentType, Data) VALUES (@Username ,@Provincename ,@Cityname ,@Number ,@Name ,@ContentType ,@Data) END --UPDATE IF @Action = 'UPDATE' BEGIN UPDATE tblbooking SET Username = @Username,Provincename = @Provincename,Cityname = @Cityname,Number = @Number,Name = @Name,ContentType = @ContentType,Data = @Data WHERE BId = @BId END --DELETE IF @Action = 'DELETE' BEGIN DELETE FROM tblbooking WHERE BId = @BId END END GO 

Answer 4

 And Aspx page is: <%@ Page Title="" Language="C#" MasterPageFile="~/admin.Master" AutoEventWireup="true" CodeBehind="MBooking.aspx.cs" Inherits="FinalYearProject.MBooking" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <style type="text/css"> <%-- body { font-family: Arial; font-size: 10pt; } table { border: 1px solid #ccc; border-collapse: collapse; background-color: #fff; } table th { background-color: #B8DBFD; color: #333; font-weight: bold; } table th, table td { background-color: #B8DBFD; padding: 5px; border: 1px solid #ccc; } table, table table td { border: 3px solid #ccc; } --%> .style1 { width: 184px; } </style> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="True" OnPageIndexChanging="OnPageIndexChanging" PageSize="6" DataKeyNames="BId" OnRowDataBound="OnRowDataBound" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit" OnRowUpdating="OnRowUpdating" OnRowDeleting="OnRowDeleting" EmptyDataText="No records has been added." Style="margin:20px 0px 0px 25px;" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" Height="250px" Width="1035px" > <Columns> <asp:TemplateField HeaderText="Username" ItemStyle-Width="120"> <ItemTemplate> <asp:Label ID="lblUsername" runat="server" Text='<%# Eval("Username") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtUsername" style = "Width:100px;" runat="server" Text='<%# Eval("Username") %>'></asp:TextBox> </EditItemTemplate> <ItemStyle Width="120px"></ItemStyle> </asp:TemplateField> <asp:TemplateField HeaderText="ProvinceName" ItemStyle-Width="120"> <ItemTemplate> <asp:Label ID="lblProvinceName" runat="server" Text='<%# Eval("Provincename") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtProvinceName" style = "Width:100px;" runat="server" Text='<%# Eval("Provincename") %>'></asp:TextBox> </EditItemTemplate> <ItemStyle Width="120px"></ItemStyle> </asp:TemplateField> <asp:TemplateField HeaderText="CityName" ItemStyle-Width="120"> <ItemTemplate> <asp:Label ID="lblCityname" runat="server" Text='<%# Eval("Cityname") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtCityname" style = "Width:100px;" runat="server" Text='<%# Eval("Cityname") %>'></asp:TextBox> </EditItemTemplate> <ItemStyle Width="120px"></ItemStyle> </asp:TemplateField><asp:TemplateField HeaderText="Number" ItemStyle-Width="120"> <ItemTemplate> <asp:Label ID="lblNumber" runat="server" Text='<%# Eval("Number") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtNumber" style = "Width:100px;" runat="server" Text='<%# Eval("Number") %>'></asp:TextBox> </EditItemTemplate> <ItemStyle Width="120px"></ItemStyle> </asp:TemplateField><asp:TemplateField HeaderText="Name" ItemStyle-Width="120"> <ItemTemplate> <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtName" style = "Width:100px;" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox> </EditItemTemplate> <ItemStyle Width="120px"></ItemStyle> </asp:TemplateField><asp:TemplateField HeaderText="ContentType" ItemStyle-Width="120"> <ItemTemplate> <asp:Label ID="lblContentType" runat="server" Text='<%# Eval("ContentType") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtContentType" style = "Width:100px;" runat="server" Text='<%# Eval("ContentType") %>'></asp:TextBox> </EditItemTemplate> <ItemStyle Width="120px"></ItemStyle> </asp:TemplateField><asp:TemplateField HeaderText="Data" ItemStyle-Width="120"> <ItemTemplate> <asp:Label ID="lblData" runat="server" Text='<%# Eval("Data") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtData" style = "Width:100px;" runat="server" Text='<%# Eval("Data") %>'></asp:TextBox> </EditItemTemplate> <ItemStyle Width="120px"></ItemStyle> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile" CommandArgument='<%# Eval("BId") %>'></asp:LinkButton> </ItemTemplate></asp:TemplateField> <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="100" > <ItemStyle Width="100px"></ItemStyle> </asp:CommandField> </Columns> <FooterStyle BackColor="#99CCCC" ForeColor="#003399" /> <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" /> <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Center" Font-Bold="True" Font-Italic="True" Font-Underline="True" Width="20px" /> <RowStyle BackColor="White" ForeColor="#003399" /> <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" /> <SortedAscendingCellStyle BackColor="#EDF6F6" /> <SortedAscendingHeaderStyle BackColor="#0D4AC4" /> <SortedDescendingCellStyle BackColor="#D6DFDF" /> <SortedDescendingHeaderStyle BackColor="#002876" /> </asp:GridView> <br /> <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; margin:10px 0px 0px 25px;"> <tr> <td style="width: 100px; background-color:#003399; color:#CCCCFF;"> <b> Username:</b><br /> <asp:TextBox ID="txtUsername" runat="server" Width="120" /> </td> <td style="width: 100px;background-color:#003399; color:#CCCCFF;"> <b> Provincename:</b><br /> <asp:TextBox ID="txtProvinceName" runat="server" Width="120" /> </td> <td style="width: 100px;background-color:#003399; color:#CCCCFF;"> <b>Cityname:</b><br /> <asp:TextBox ID="txtCityname" runat="server" Width="120" /> </td> <td style="width: 100px;background-color:#003399; color:#CCCCFF;"> <b> Number:</b><br /> <asp:TextBox ID="txtNumber" runat="server" Width="120" /> </td> <td style="width: 100px;background-color:#003399; color:#CCCCFF;"> <b> Name:</b><br /> <asp:TextBox ID="txtName" runat="server" Width="120" /> </td> <td style="width: 100px;background-color:#003399; color:#CCCCFF;"> <b> ContentType:</b><br /> <asp:TextBox ID="txtContentType" runat="server" Width="120" /> </td> <td style="width: 100px;background-color:#003399; color:#CCCCFF;"> <b>Data:</b><br /> <asp:TextBox ID="txtData" runat="server" Width="120" /> </td> <td style="background-color:#003399; color:#CCCCFF;" class="style1"> <asp:Button ID="btnAdd" runat="server" CssClass="btn btn-info" Text="Add" OnClick="Insert" Width="187px" /> </td> </tr> </table> </asp:Content>