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.
  • 30772
Download Files:
 

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

 GridView.bmp

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.