DataGrid View Control in VB.NET

In this article, We will see how to use DataGrid control in VB.NET.
  • 3148
 

In this article, We will see how to use DataGrid control in VB.NET.

This code is aspx:-

<%@ 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="Grid" runat="server" PageSize="5" AllowPaging="True" DataKeyField="EmpId"
            AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None"
            OnPageIndexChanged="Grid_PageIndexChanged" OnCancelCommand="Grid_CancelCommand"
            OnDeleteCommand="Grid_DeleteCommand" OnEditCommand="Grid_EditCommand" OnUpdateCommand="Grid_UpdateCommand">
            <Columns>
                <asp:BoundColumn HeaderText="EmpId" DataField="EmpId"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="F_Name" DataField="F_Name"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="L_Name" DataField="L_Name"></asp:BoundColumn>
                <asp:BoundColumn DataField="City" HeaderText="City"></asp:BoundColumn>
                <asp:BoundColumn DataField="EmailId" HeaderText="EmailId"></asp:BoundColumn>
                <asp:BoundColumn DataField="EmpJoining" HeaderText="EmpJoining"></asp:BoundColumn>
                <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderText="Edit">
                </asp:EditCommandColumn>
                <asp:ButtonColumn CommandName="Delete" HeaderText="Delete" Text="Delete"></asp:ButtonColumn>
            </Columns>
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <SelectedItemStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" Mode="NumericPages" />
            <AlternatingItemStyle BackColor="White" />
            <ItemStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        </asp:DataGrid>
        <br />
        <br />
        <table>
            <tr>
                <td>
                    <asp:Label ID="lblEmpId" runat="server" Text="EmpId"></asp:Label>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:Label ID="lblfname" runat="server" Text="F_Name"></asp:Label>
                    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:Label ID="lblLname" runat="server" Text="L_Name"></asp:Label>
                    <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:Label ID="lblCity" runat="server" Text="City"></asp:Label>
                    <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:Label ID="lblEmailId" runat="server" Text="EmailId"></asp:Label>
                    <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:Label ID="lblEmpJoining" runat="server" Text="EmpJoining"></asp:Label>
                    <asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>
                </td>
            </tr>
        </table>
        <asp:Button ID="btnsubmit" runat="server" Text="Submit" OnClick="btnsubmit_Click" />
        <asp:Button ID="btnReset" runat="server" Text="Reset" OnClick="btnReset_Click" />
        <asp:Button ID="btnOk" runat="server" Text="OK" OnClick="btnOk_Click" />
    </div>
    <div>
        <asp:DataGrid ID="Grid1" runat="server" PageSize="5" AllowPaging="True" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">
            <Columns>
                <asp:BoundColumn HeaderText="EmpId" DataField="EmpId"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="F_Name" DataField="F_Name"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="L_Name" DataField="L_Name"></asp:BoundColumn>
                <asp:BoundColumn DataField="City" HeaderText="City"></asp:BoundColumn>
                <asp:BoundColumn DataField="EmailId" HeaderText="EmailId"></asp:BoundColumn>
                <asp:BoundColumn DataField="EmpJoining" HeaderText="EmpJoining"></asp:BoundColumn>
            </Columns>
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <SelectedItemStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" Mode="NumericPages" />
            <AlternatingItemStyle BackColor="White" />
            <ItemStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        </asp:DataGrid>
    </div>
    </form>
</body>
</
html>

This code is .vb:-

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 da As SqlDataAdapter
    Private ds As New DataSet()
    Private cmd As New SqlCommand()
    Private con As SqlConnection
-------------------------------------------------------------------------------------------------------
    Protected Sub Grid_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Grid.Load
        If Not Page.IsPostBack Then
            BindData()
        End If
    End Sub
-------------------------------------------------------------------------------------------------------
    Public Sub BindData()
        con = New SqlConnection(ConfigurationManager.AppSettings("connect"))
        cmd.CommandText = "Select * from Employee"
        cmd.Connection = con
        da = New SqlDataAdapter(cmd)
        da.Fill(ds)
        con.Open()
        cmd.ExecuteNonQuery()
        Grid.DataSource = ds
        Grid.DataBind()
        con.Close()
    End Sub
-------------------------------------------------------------------------------------------------------    

     Protected Sub Grid_PageIndexChanged(ByVal source As Object, ByVal e As DataGridPageChangedEventArgs)
        Grid.CurrentPageIndex = e.NewPageIndex
        BindData()
    End Sub
-------------------------------------------------------------------------------------------------------   

  Protected Sub Grid_EditCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
        Grid.EditItemIndex = e.Item.ItemIndex
        BindData()
    End Sub
-------------------------------------------------------------------------------------------------------    

Protected Sub Grid_CancelCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)        Grid.EditItemIndex = -1
        BindData()
    End Sub
-------------------------------------------------------------------------------------------------------  

  Protected Sub Grid_DeleteCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
        con = New SqlConnection(ConfigurationManager.AppSettings("connect"))
        cmd.Connection = con
        Dim EmpId As Integer = CInt(Grid.DataKeys(CInt(e.Item.ItemIndex)))
        cmd.CommandText = "Delete from Employee where EmpId=" & EmpId
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
        Grid.EditItemIndex = -1
        BindData()
    End Sub
-------------------------------------------------------------------------------------------------------   

 Protected Sub Grid_UpdateCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
        con = New SqlConnection(ConfigurationManager.AppSettings("connect"))
        cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = DirectCast(e.Item.Cells(0).Controls(0), TextBox).Text
        cmd.Parameters.Add("@F_Name", SqlDbType.[Char]).Value = DirectCast(e.Item.Cells(1).Controls(0), TextBox).Text
        cmd.Parameters.Add("@L_Name", SqlDbType.[Char]).Value = DirectCast(e.Item.Cells(2).Controls(0),
TextBox).Text
        cmd.Parameters.Add("@City", SqlDbType.[Char]).Value = DirectCast(e.Item.Cells(3).Controls(0),
TextBox).Text
        cmd.Parameters.Add("@EmailId", SqlDbType.[Char]).Value = DirectCast(e.Item.Cells(4).Controls(0
, TextBox).Text
        cmd.Parameters.Add("@EmpJoining", SqlDbType.DateTime).Value = DateTime.Now.ToString()
        cmd.CommandText = "Update Employee set F_Name=@F_Name,L_Name=@L_Name,City=@City,EmailId=@EmailId,EmpJoining=@EmpJoining where EmpId=@EmpId"
        cmd.Connection = con
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
        Grid.EditItemIndex = -1
        BindData()
    End Sub
-------------------------------------------------------------------------------------------------------  

  Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim con As SqlConnection
        con = New SqlConnection(ConfigurationManager.AppSettings("connect"))
        con.Open()
        Dim cmd As SqlCommand
        cmd = New SqlCommand(((((("Insert into Employee (EmpId,F_Name,L_Name,City,EmailId,EmpJoining) values('" + TextBox1.Text & "','") + TextBox2.Text & "','") + TextBox3.Text & "','") + TextBox4.Text & "','") + TextBox5.Text & "','") + TextBox6.Text & "')", con)
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
-------------------------------------------------------------------------------------------------------   

Protected Sub btnReset_Click(ByVal sender As Object, ByVal e As EventArgs)
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
    End Sub
-------------------------------------------------------------------------------------------------------   

 Protected Sub btnOk_Click(ByVal sender As Object, ByVal e As EventArgs)
        BindData1()
    End Sub
-------------------------------------------------------------------------------------------------------   
Public
Sub BindData1()
        con = New SqlConnection(ConfigurationManager.AppSettings("connect"))
        cmd.CommandText = "Select * from Employee"
        cmd.Connection = con
        da = New SqlDataAdapter(cmd)
        da.Fill(ds)
        con.Open()
        cmd.ExecuteNonQuery()
        Grid1.DataSource = ds
        Grid1.DataBind()
        con.Close()
    End Sub
End Class
-------------------------------------------------------------------------------------------------------

Output:-

dataGrid.gif

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.