GridView Control in VB.NET
In this article, We will see how to use GridView control in VB.NET with Select, Update, Edit and Delete command.
In this article, We will see how to use GridView control in VB.NET with Select, Update, Edit and Delete command.
We will use SQL Client data provider to provide database connectivity.
Before you can use any classes related to SQL Client data adapter, we need to import the SqlClient namespace in your application by using the following using statement.
Imports System.Data.SqlClient
Next, we need to define the database connection string.
The below is my connection string which is stored in web.config file. You can change this connection string according to your SQL server database setting.
Connection String of Database :-
<appSettings>
<add key="connect" value="Initial Catalog=Data; Data Source=MCNDESKTOP10; uid=sa;pwd=wintellect"/>
</appSettings>
This is aspx code:-
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" PageSize="5" AutoGenerateColumns="false"
AllowPaging="true" BackColor="White" BorderColor="#CC9966" BorderStyle="None"
BorderWidth="1px" CellPadding="4" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnPageIndexChanging="GridView1_PageIndexChanging" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<RowStyle BackColor="White" ForeColor="#330099" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" /> <Columns>
<asp:TemplateField HeaderText="StId">
<ItemTemplate>
<asp:Label ID="lblstid" runat="server" Text='<%#Eval ("stId")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%#Eval("name")%>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ClassName">
<ItemTemplate>
<asp:TextBox ID="txtClassName" runat="server" Text='<%#Eval ("Classname">'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="RollNo">
<ItemTemplate>
<asp:TextBox ID="txtRollNo" runat="server" Text='<%#Eval ("rollno")%>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="EmailId">
<ItemTemplate>
<asp:TextBox ID="txtEmailId" runat="server" Text='<%#Eval ("emailId")%>'>
</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>
<table>
<tr>
<td>
<asp:Label ID="lblName" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblClassName" runat="server" Text="ClassName"></asp:Label>
<asp:TextBox ID="txtClassName" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblRollNo" runat="server" Text="RollNo"></asp:Label>
<asp:TextBox ID="txtRollNo" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblEmailId" runat="server" Text="EmailId"></asp:Label>
<asp:TextBox ID="txtEmailId" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblTotalRecord" runat="server" Text="TotalRecord"></asp:Label>
<asp:TextBox ID="txtTotalRecord" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Button ID="Submit" runat="server" Text="Submit" OnClick="Submit_Click1" />
<asp:Button ID="Reset" runat="server" Text="Reset" OnClick="Reset_Click1" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
This is .vb code:-
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
-------------------------------------------------------------------------------------------------------
Partial Class _Default
Inherits System.Web.UI.Page
Private sqlDataAdapter As SqlDataAdapter
Private sqlConnection As SqlConnection
Private dataSet As New DataSet()
Private sqlCommand As New SqlCommand()
-------------------------------------------------------------------------------------------------------
Protected Sub GridView1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.Load
If Not Page.IsPostBack Then
BindData()
End If
End Sub
-------------------------------------------------------------------------------------------------------
Public Sub BindData()
sqlConnection = New SqlConnection(ConfigurationManager.AppSettings("connect"))
sqlCommand.CommandText = "Select * from StudentRecord"
sqlCommand.Connection = sqlConnection
sqlDataAdapter = New SqlDataAdapter(sqlCommand)
sqlDataAdapter.Fill(dataSet)
sqlConnection.Open()
sqlCommand.ExecuteNonQuery()
GridView1.DataSource = dataSet
GridView1.DataBind()
sqlConnection.Close()
End Sub
-------------------------------------------------------------------------------------------------------
Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
GridView1.EditIndex = e.NewEditIndex
BindData()
End Sub
-------------------------------------------------------------------------------------------------------
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
sqlConnection = New SqlConnection(ConfigurationManager.AppSettings("connect"))
Dim lblstid As Label = DirectCast(GridView1.Rows(e.RowIndex).FindControl("lblstId"), Label)
Dim txtname As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtName"), TextBox)
Dim txtclassname As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtClassName"), TextBox)
Dim txtrollno As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtRollNo"), TextBox)
Dim txtemailid As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtEmailId"), TextBox)
sqlCommand.Connection = sqlConnection
sqlCommand.CommandText = "Update StudentRecord set Name='" & txtname.Text & "',ClassName='" &
txtclassname.Text & "',RollNo='" & txtrollno.Text & "',EmailId='" & txtemailid.Text & "' where StId='"
& lblstid.Text & "'"
sqlCommand.Connection.Open()
sqlCommand.ExecuteNonQuery()
GridView1.EditIndex = -1
BindData()
sqlConnection.Close()
End Sub
------------------------------------------------------------------------------------------------------
Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs) sqlConnection = New SqlConnection(ConfigurationManager.AppSettings("connect"))
sqlCommand.Connection = sqlConnection
Dim lbldeleteID As Label = DirectCast(GridView1.Rows(e.RowIndex).FindControl("lblstId"), Label)
sqlCommand.CommandText = "Delete from StudentRecord where StId='" & lbldeleteID.Text & "'"
sqlConnection.Open()
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
BindData()
End Sub
-------------------------------------------------------------------------------------------------------
Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As
GridViewCancelEditEventArgs)
GridView1.EditIndex = -1
BindData()
End Sub
-------------------------------------------------------------------------------------------------------
Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
BindData()
End Sub
-------------------------------------------------------------------------------------------------------
Protected Sub Submit_Click1(ByVal sender As Object, ByVal e As EventArgs)
Dim sqlConnection As SqlConnection
sqlConnection = New SqlConnection(ConfigurationManager.AppSettings("connect"))
sqlConnection.Open()
Dim sqlCommand As SqlCommand
sqlCommand = New SqlCommand((((("Insert into StudentRecord (Name,ClassName,RollNo,EmailId,TotalRecord) Values('" + txtName.Text & "','") + txtClassName.Text & "','") + txtRollNo.Text & "','") + txtEmailId.Text & "','") + txtTotalRecord.Text & "')", sqlConnection)
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
End Sub
-------------------------------------------------------------------------------------------------------
Protected Sub Reset_Click1(ByVal sender As Object, ByVal e As EventArgs)
txtName.Text = ""
txtClassName.Text = ""
txtRollNo.Text = ""
txtEmailId.Text = ""
End Sub
End Class
-------------------------------------------------------------------------------------------------------
Output:-
