Bind GridView control using SqlDataSource control

Create a table in MS-SQL-SERVER as given below.

bind-gridview-control

Bind the GridView control using the SqlDataSource control. Consider that data is available in the table.

Answer:

Introduction:
We use SQL Server database to create an Employee table with columns, as shown above. The GridView control is used to populate the record from the database in the form of table. Each column represents a field and each row represents a record.

In this application, we use GridView control to populate the record from the database. The SQL Server database is used to create the table. VB.NET is used for coding.

Steps to create this Application:

1. Create the table “EmpDetails”

Create table EmpDetails (EmpId int not null primary key, Name nvarchar(50) not null, Gender nvarchar(50), Salary float, Address nvarchar(50),DEPID varchar(50) not null) ON [PRIMARY]


2. Develop the web page

grid-control.aspx

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Employee Details</title>
</head>

<body>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System" %>
    <%@ Import Namespace=" System.Configuration" %>

    <script language="VB" runat="server">
        Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                Me.BindData()
            End If
        End Sub

        Private Sub BindData()
            Dim constr As String = ConfigurationSettings.AppSettings("ConnectionString")
            Using oConn As New SqlConnection(constr)
                Using cmd As New SqlCommand("select * from EmpDetails")
                    Using sda As New SqlDataAdapter()
                        cmd.Connection = oConn
                        sda.SelectCommand = cmd
                        Using dt As New DataTable()
                            sda.Fill(dt)
                            GridView1.DataSource = dt
                            GridView1.DataBind()
                        End Using
                    End Using
                End Using
            End Using
        End Sub

        Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
            GridView1.PageIndex = e.NewPageIndex
            Me.BindData()
        End Sub
    </script>
    <form id="form1" runat="server">
       <div id = "dvGrid" style ="padding:10px;width:550px">

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPageIndexChanging" PageSize="10">
             <Columns>
                  <asp:BoundField DataField="EmpID" HeaderText="EmpID" />
                  <asp:BoundField ItemStyle-Width="150px" DataField="Name" HeaderText="Name" />
                  <asp:BoundField ItemStyle-Width="150px" DataField="Gender" HeaderText="Gender" />
                  <asp:BoundField ItemStyle-Width="150px" DataField="Salary" HeaderText="Salary" />
                 <asp:BoundField ItemStyle-Width="150px" DataField="Address" HeaderText="Address" />
                 <asp:BoundField ItemStyle-Width="150px" DataField="DeptID" HeaderText="DeptID" />
            </Columns>
        </asp:GridView>

      </div>
   </form>
</body>
</html>


Output:

The output shows the record of the EmpDetails Table:

grid view output
▲