Update, Delete & Select operations in GridView

Take a GridView data control. Populate it with data from database. Perform Update, Delete, and Select operation within GridView.

Answer:

In this example, we will show how to use GridView control to Select, Update and Delete data in a SQL Server database.

Name of table StudentMaster (studID, studName, DOB, Percentage, courseName, Address), but we will show only studName and Address from the table.

Steps to create the above application.

1. Create a table “StudentMaster” in SQL Server Database

create table StudentMaster (studID int, studName varchar(50), DOB date, Percentage float, courseName varchar(20), Address varchar(100))

2. Develop the ASP web page in VB.Net

Crud-gridview.aspx

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Student Records</title>
</head>

<body>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System" %>
    <%@ Import Namespace=" System.Configuration" %>

    <script language="VB" runat="server"  Debug="true">

        Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                Me.BindData()
            End If
        End Sub

        Sub BindData()
            Dim constr As String = ConfigurationSettings.AppSettings("ConnectionString")
            Using oConn As New SqlConnection(constr)
                Dim cmd As New SqlCommand("select * from StudentMaster")
                Dim sda As New SqlDataAdapter()
                cmd.Connection = oConn
                sda.SelectCommand = cmd
                Dim dt As New DataTable()
                sda.Fill(dt)
                GridView1.DataSource = dt
                GridView1.DataBind()
            End Using
        End Sub

        Protected Sub GridView1_OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
            GridView1.PageIndex = e.NewPageIndex
            BindData()
        End Sub

        Protected Sub GridView1_RowDeleting(sender As Object, e As GridViewDeleteEventArgs)
            Dim constr As String = ConfigurationSettings.AppSettings("ConnectionString")
            Dim cmd As New SqlCommand
            Using oConn As New SqlConnection(constr)
                Dim lblstid As Label = DirectCast(GridView1.Rows(e.RowIndex).FindControl("lblstId"), Label)
                cmd.Connection = oConn
                cmd.CommandText = "Delete from StudentMaster where studID='" + lblstid.Text + "'"
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                GridView1.EditIndex = -1
                ClientScript.RegisterClientScriptBlock(Me.GetType, "alert", "alert('Record deleted Successfully')", True)
                BindData()
                oConn.Close()
            End Using
        End Sub

        Protected Sub GridView1_RowEditing(sender As Object, e As GridViewEditEventArgs)
            GridView1.EditIndex = e.NewEditIndex
            BindData()
        End Sub

        Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs)
            Dim constr As String = ConfigurationSettings.AppSettings("ConnectionString")
            Dim cmd As New SqlCommand
            Using oConn As New SqlConnection(constr)
                Dim lblstid As Label = DirectCast(GridView1.Rows(e.RowIndex).FindControl("lblstId"), Label)
                Dim txtstudName As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtstudName"), TextBox)
                Dim txtdob As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtdob"), TextBox)
                Dim txtpercetage As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtpercetage"), TextBox)
                Dim txtcourse As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtcourse"), TextBox)
                Dim txtaddress As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtaddress"), TextBox)
                cmd.Connection = oConn
                cmd.CommandText = "Update StudentMaster set studName='" + txtstudName.Text + "',DOB='" + txtdob.Text + "',Percentage='" + txtpercetage.Text + "',courseName='" + txtcourse.Text + "',Address='" + txtaddress.Text + "' where studID='" + lblstid.Text + "'"
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                GridView1.EditIndex = -1
                ClientScript.RegisterClientScriptBlock(Me.GetType, "alert", "alert('Record Updated Successfully')", True)
                BindData()
                oConn.Close()
            End Using
        End Sub

        Protected Sub GridView1_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs)
            GridView1.EditIndex = -1
            BindData()
        End Sub

    </script>
<body>
  <form id="form1" runat="server">
   <div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" BackColor="White" BorderColor="#CC9966" BorderStyle="None"  BorderWidth="1px"
    OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnPageIndexChanging="GridView1_OnPageIndexChanging" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" PageSize="10">
    <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
    <RowStyle BackColor="White" ForeColor="#330099" />
    <SelectedRowStyle BackColor="#ffcccc" Font-Bold="True" ForeColor="#663399" /> <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center"/>
    <HeaderStyle BackColor="#a366ff" Font-Bold="True" ForeColor="#FFFFCC" />
      <Columns>

        <asp:TemplateField HeaderText="StudId" Visible="false">
            <ItemTemplate>
               <asp:Label ID="lblstId" runat="server" Text='<%#Eval("studID")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Name">
           <ItemTemplate>
              <asp:TextBox ID="txtstudName" runat="server" Text='<%#Eval("studName")%>'> </asp:TextBox>
           </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText ="DOB" Visible="false">
            <ItemTemplate>
                <asp:TextBox ID="txtdob" runat="server" Text='<%#Eval("DOB", "{0:MM/dd/yyyy}") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Percentage" Visible="false">
            <ItemTemplate>
                <asp:TextBox ID="txtpercetage" runat="server" Text='<%#Eval("Percentage") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Course" Visible="false">
            <ItemTemplate>
               <asp:TextBox ID="txtcourse" runat="server" Text='<%#Eval("courseName")%>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>

       <asp:TemplateField HeaderText="Address">
         <ItemTemplate>
            <asp:TextBox ID="txtaddress" runat="server" Text='<%#Eval("Address")%>'></asp:TextBox>
         </ItemTemplate>
       </asp:TemplateField>

        <asp:TemplateField HeaderText="Edit" ShowHeader="false">
          <EditItemTemplate>
               <asp:LinkButton ID="lnkbtnUpdate" runat="server" CausesValidation="true" Text="Update" CommandName="Update"></asp:LinkButton>
              <asp:LinkButton ID="lnkbtnCancel" runat="server" CausesValidation="false" Text="Cancel" CommandName="Cancel"></asp:LinkButton>
         </EditItemTemplate>

         <ItemTemplate>
               <asp:LinkButton ID="btnEdit" runat="server" CausesValidation="false" CommandName="Edit" Text="Edit"></asp:LinkButton>
         </ItemTemplate>

      </asp:TemplateField>
         <asp:CommandField HeaderText="Delete" ShowDeleteButton="true" ShowHeader="true" />
         <asp:CommandField HeaderText="Select" ShowSelectButton="true" ShowHeader="true" />
     </Columns>
   </asp:GridView>
  </div>
</form>
</body>
</html>