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:-
