ASP.NET GridView: How to edit and delete data reco

Question

Hi I have used gridview to create a table. Is there a way to implement edit and delete. I have done it in PHP before. The method I would like to use is create two more columns in the table with edit and delete buttons on each row. Then when the buttons are click it passes the 'id' through the URL and able to edit or delete. Not really sure how to do this in asp.net webforms. Below is my code for the table. Thank you.

 <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

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 2

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>

Answer 3

The GridView supports those operations. You can add a CommandField which will contain the command buttons or LinkButtons (you can choose the type of button and assign the text of each button). The patientID field should be included in the DataKeyNames property of the GridView, in order to retrieve it when the time comes to update or delete the 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>

You will then need to handle a few events in code-behind:

// 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 those event handlers, you can do it in a BindData utility function, 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 4

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