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>
Output.
The below screenshot shows the GridView control and performing select, update and delete operation on database.
1. On page load
2. Select record
3. Update record
4. Delete record
5. Record after delete
