Manipulation DataGrid and paging in VB.NET
In this article, We will see how to Edit, Delete and Update the data in DataGrid and paging in VB.NET.
In this article, We will see how to Edit, Delete and Update the data in DataGrid and paging too in VB.NET.
If you are going to use DataGrid then here we need to edit, delete, update data in DataGrid. So many records that on one page all of the records can not come, then here paging plays an important role to show our data in a manner in DataGrid.
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:DataGrid ID="gridedit" runat="server" DataKeyField="StId" BorderStyle="Ridge"
GridLines="None" BorderWidth="2px" BorderColor="White" BackColor="White" CellPadding="3"
CellSpacing="1" AllowSorting="True" PagerStyle-HorizontalAlign="Center" HorizontalAlign="Left"
OnEditCommand="editgrid_click" OnCancelCommand="gridcancel_click" OnPageIndexChanged="gridedit_PageIndexChanged"
OnUpdateCommand="updategrid_UpdateCommand" PageSize="5" AllowPaging="true" AutoGenerateColumns="false"
Width="50%">
<FooterStyle ForeColor="Black" BackColor="#C6C3C6"></FooterStyle>
<HeaderStyle Font-Bold="True" ForeColor="#FFFFFF" BackColor="#A53A6A"></HeaderStyle>
<FooterStyle BackColor="beige" />
<PagerStyle Font-Bold="true" Mode="NumericPages" Font-Underline="true" />
<Columns>
<asp:BoundColumn DataField="StId" HeaderText="StId">
<ItemStyle BackColor="graytext" />
<HeaderStyle BackColor="graytext" />
</asp:BoundColumn>
<asp:BoundColumn DataField="StudentName" HeaderText="StudentName">
<ItemStyle BackColor="GhostWhite" />
</asp:BoundColumn>
<asp:BoundColumn DataField="ClassName" HeaderText="ClassName">
<ItemStyle BackColor="GhostWhite" />
</asp:BoundColumn>
<asp:BoundColumn DataField="RollNo" HeaderText="RollNo">
<ItemStyle BackColor="GhostWhite" />
</asp:BoundColumn>
<asp:BoundColumn DataField="EmailId" HeaderText="EmailId">
<ItemStyle BackColor="GhostWhite" />
</asp:BoundColumn>
<asp:EditCommandColumn CancelText="Cancel" EditText="Edit" UpdateText="Update" HeaderText="Edit">
<ItemStyle BackColor="GhostWhite" />
</asp:EditCommandColumn>
<asp:ButtonColumn CommandName="Delete" HeaderText="Delete" Text="Delete">
<ItemStyle BackColor="GhostWhite" />
</asp:ButtonColumn>
</Columns>
</asp:DataGrid>
</div>
</form>
</body>
</html>
This is .vb code:-
Imports System
Imports System.Data
Imports System.Configuration
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 Data.SqlClient.SqlDataAdapter
Private dataSet As New Data.DataSet()
Private sqlConnection As Data.SqlClient.SqlConnection
Private sqlCommand As New Data.SqlClient.SqlCommand()
---------------------------------------------------------------------------------------------------
'Page Load
Protected Sub gridedit_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles gridedit.Load
If Not Page.IsPostBack Then
Binddata()
End If
End Sub
---------------------------------------------------------------------------------------------------
'HBind the data.
Public Sub Binddata()
sqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("connect"))
sqlCommand.CommandText = "Select * From StudentRecords"
sqlCommand.Connection = sqlConnection
sqlDataAdapter = New Data.SqlClient.SqlDataAdapter(sqlCommand)
sqlDataAdapter.Fill(dataSet)
sqlConnection.Open()
sqlCommand.ExecuteNonQuery()
gridedit.DataSource = dataSet
gridedit.DataBind()
sqlConnection.Close()
End Sub
---------------------------------------------------------------------------------------------------
'Update Command Defination.
Protected Sub updategrid_UpdateCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
sqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("connect"))
sqlCommand.CommandText = "Update StudentRecords set StudentName=@StudentName ,ClassName=@ClassName, RollNo=@RollNo,EmailId=@EmailId where StId=@StId"
sqlCommand.Parameters.Add("@StudentName", SqlDbType.[Char]).Value = DirectCast(e.Item.Cells(1).Controls(0), TextBox).Text
sqlCommand.Parameters.Add("@ClassName", SqlDbType.[Char]).Value = DirectCast(e.Item.Cells(2).Controls(0), TextBox).Text
sqlCommand.Parameters.Add("@RollNo", SqlDbType.[Char]).Value = DirectCast(e.Item.Cells(3).Controls(0), TextBox).Text
sqlCommand.Parameters.Add("@EmailId", SqlDbType.[Char]).Value = DirectCast(e.Item.Cells(4).Controls(0), TextBox).Text
sqlCommand.Parameters.Add("@StId", SqlDbType.Int).Value = gridedit.DataKeys(e.Item.ItemIndex)
sqlCommand.Connection = sqlConnection
sqlCommand.Connection.Open()
sqlCommand.ExecuteNonQuery()
sqlCommand.Connection.Close()
gridedit.EditItemIndex = -1
Binddata()
End Sub
---------------------------------------------------------------------------------------------------
' Define the Edit Command.
Public Sub editgrid_click(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
gridedit.EditItemIndex = e.Item.ItemIndex
Binddata()
End Sub
---------------------------------------------------------------------------------------------------
' Define the Cancel/Delete Command.
Public Sub gridcancel_click(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
gridedit.EditItemIndex = -1
Binddata()
End Sub
---------------------------------------------------------------------------------------------------
' This is for Paging.
Public Sub gridedit_PageIndexChanged(ByVal source As Object, ByVal e As DataGridPageChangedEventArgs)
gridedit.CurrentPageIndex = e.NewPageIndex
Binddata()
End Sub
End Class
---------------------------------------------------------------------------------------------------
Output:-
