In this example I will show you how to bind data in asp.net vb gridview using JQuery. I created EmployeeDetails class with table column as properties. On the pageload ajax function will call a request to webmethod and return the json array list. I used Html table structure to populate data to gridview.
We will be using employee table
Step 1: Create a table using the following script with data:
CREATE TABLE[dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
CONSTRAINT[PK_tbl_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON[PRIMARY]
SET IDENTITY_INSERT[dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (1, N'Mark', N'Male', N'Berlin')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (2, N'John', N'Male', N'Beijing')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (3, N'Mary', N'Femal', N'Brussels')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (4, N'Steve', N'Male', N'Luxembourg')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (5, N'Ben', N'Male', N'Pyongsong')
SET IDENTITY_INSERT[dbo].[Employee] OFF
Step 2: Copy and paste the following code.
JSONGrid.aspx:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="JSONGrid.aspx.vb" Inherits="JSONGrid" %>
<!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>Asp.net Bind Datato Datatable using JQuery or JSON</title>
<script type="text/javascript" src="//code.jquery.com/jquery-1.10.2.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$.ajax({
type: "POST",
contentType: "application/json;charset=utf-8",
url: "JSONGrid.aspx/BindDatatable",
data: "{}",
dataType: "json",
success: function (data) {
for (var i = 0; i < data.d.length; i++){
$("#grid").append("<tr><td>" + data.d[i].EmployeeId + "</td><td>" + data.d[i].Name + "</td><td>" + data.d[i].Gender + "</td><td>" + data.d[i].City + "</td></tr>");
}
},
error: function (result) {
alert("Error");
}
});
});
</script>
<style type="text/css">
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div style="border: 1px solid #DED8D8; width: 500px; height: 250px; font-family: Arial;">
<h2 style="color: #FF5722;">Bind grid in JSON or JQuery usingvb.net </h2>
<asp:GridView ID="grid" runat="server">
<HeaderStyle BackColor="#DC5807" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
JSONGrid.aspx.vb:
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Partial Class JSONGrid
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Try
If Not IsPostBack Then
BindColumnToGridview()
End If
Catch ex As Exception
MsgBox(ex.ToString, MsgBoxStyle.Information)
End Try
End Sub
''' <summary>
''' This method is used to binddummy row to gridview to bind data using JQuery
''' </summary>
Private Sub BindColumnToGridview()
Try
Dim dt As New DataTable()
dt.Columns.Add("Employeed")
dt.Columns.Add("Name")
dt.Columns.Add("Gender")
dt.Columns.Add("City")
dt.Rows.Add()
grid.DataSource = dt
grid.DataBind()
grid.Rows(0).Visible = False
Catch ex As Exception
Throw
End Try
End Sub
<WebMethod()> _
Public Shared Function BindDatatable() As EmployeeDetails()
Dim dt As New DataTable()
Dim details As New List(Of EmployeeDetails)()
Using con As New SqlConnection("DataSource=BIG-PC;Initial Catalog=ShoppingZone;Integrated Security=true")
Using cmd As New SqlCommand("select EmployeeID,Name,Gender,City fromEmployee",con)
con.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
For Each dtrow As DataRow In dt.Rows
Dim user As New EmployeeDetails()
user.EmployeeId = dtrow("EmployeeId").ToString()
user.Name = dtrow("Name").ToString()
user.Gender = dtrow("Gender").ToString()
user.City = dtrow("City").ToString()
details.Add(user)
Next
End Using
End Using
Return details.ToArray()
End Function
Public Class EmployeeDetails
Public Property EmployeeId() As Integer
Get
Return m_EmployeeId
End Get
Set(ByVal value As Integer)
m_EmployeeId = value
End Set
End Property
Private m_EmployeeId As Integer
Public Property Name() As String
Get
Return m_Name
End Get
Set(ByVal value As String)
m_Name = value
End Set
End Property
Private m_Name As String
Public Property Gender() As String
Get
Return m_Gender
End Get
Set(ByVal value As String)
m_Gender = value
End Set
End Property
Private m_Gender As String
Public Property City() As String
Get
Return m_City
End Get
Set(ByVal value As String)
m_City = value
End Set
End Property
Private m_City As String
End Class
End Class
Output: