Introduction:
In this article I will explain how to bind data to datatable and display it on our aspx page using JQuery or JSON in asp.net.
In this article I will explain how to bind data to datatable and display it on our aspx page using JQuery or JSON in asp.net.
Description:
In previous article I explained how to bind data to gridview with JQuery. Now I am using same concept to explain how to bind data to datatable using JQuery/JSON and display it on aspx page in asp.net.
To implement this concept first we need to design table in database give name as UserInformation to save user details in database.
In previous article I explained how to bind data to gridview with JQuery. Now I am using same concept to explain how to bind data to datatable using JQuery/JSON and display it on aspx page in asp.net.
To implement this concept first we need to design table in database give name as UserInformation to save user details in database.
Column Name
|
Data Type
|
Allow Nulls
|
UserId
|
int(set
identity property=true)
|
No
|
UserName
|
varchar(50)
|
Yes
|
Location
|
nvarchar(max)
|
Yes
|
After completion
table design enter some of user details in database to work for our sample.
Now create new
website and write the following code in your
aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"
runat="server">
<title>Asp.net Bind Data to Datatable using JQuery or JSON</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "Default.aspx/BindDatatable",
data: "{}",
dataType: "json",
success: function(data)
{
for (var i = 0; i <
data.d.length; i++) {
$("#tbDetails").append("<tr><td>" +
data.d[i].UserId + "</td><td>"
+ data.d[i].UserName + "</td><td>"
+ data.d[i].Location + "</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">
<table id="tbDetails"
cellpadding="0"
cellspacing="0">
<thead style="background-color:#DC5807; color:White; font-weight:bold">
<tr style="border:solid 1px #000000">
<td>UserId</td>
<td>UserName</td>
<td>Location</td>
</tr>
</thead>
<tbody>
</tbody>
</table>
</form>
</body>
</html>
|
If
you observe above code in header section I added script file link by using that
file we have a chance to interact with JQuery and if you want to know the
script function declared in header section check these posts JQuery UI AutoComplete textbox with
database and call asp.net pagemethods in JQuery. Here I explained that
function clearly.
Now
open code behind file and add the following namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Collections.Generic;
|
After
that write the following code in code behind
C#.NET Code
protected void
Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static UserDetails[] BindDatatable()
{
DataTable dt = new DataTable();
List<UserDetails>
details = new List<UserDetails>();
using (SqlConnection
con = new SqlConnection("Data Source=SureshDasari;Initial
Catalog=MySampleDB;Integrated Security=true"))
{
using (SqlCommand
cmd = new SqlCommand("select TOP 10 UserId,UserName,Location from
UserInformation", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow
dtrow in dt.Rows)
{
UserDetails user = new UserDetails();
user.UserId = dtrow["UserId"].ToString();
user.UserName = dtrow["UserName"].ToString();
user.Location = dtrow["Location"].ToString();
details.Add(user);
}
}
}
return details.ToArray();
}
public class UserDetails
{
public string
UserId { get; set;
}
public string
UserName { get; set;
}
public string
Location { get; set;
}
}
|
VB.NET Code:
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Partial Class
VBSample
Inherits System.Web.UI.Page
Protected Sub
Page_Load(ByVal sender As Object, ByVal e As
EventArgs) Handles Me.Load
End Sub
<WebMethod()> _
Public Shared Function BindDatatable() As
UserDetails()
Dim dt As New DataTable()
Dim details As New List(Of
UserDetails)()
Using con As New SqlConnection("Data
Source=SureshDasari;Initial Catalog=MySampleDB;Integrated Security=true")
Using cmd As New SqlCommand("select
TOP 10 UserId,UserName,Location from UserInformation", con)
con.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
For Each dtrow As DataRow In
dt.Rows
Dim user As New UserDetails()
user.UserId = dtrow("UserId").ToString()
user.UserName = dtrow("UserName").ToString()
user.Location = dtrow("Location").ToString()
details.Add(user)
Next
End Using
End Using
Return details.ToArray()
End Function
Public Class
UserDetails
Public Property
UserId() As String
Get
Return m_UserId
End Get
Set(ByVal value As String)
m_UserId = Value
End Set
End Property
Private m_UserId As String
Public Property
UserName() As String
Get
Return m_UserName
End Get
Set(ByVal value As String)
m_UserName = Value
End Set
End Property
Private m_UserName As
String
Public Property
Location() As String
Get
Return m_Location
End Get
Set(ByVal value As String)
m_Location = Value
End Set
End Property
Private m_Location As
String
End Class
End Class
|
Demo
 |
Download
sample code attached
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
27 comments :
Very Very Helpfull
Good one
Excellent Post...It Solves my Problem around reading more than 300 post on different post.
I want to call this On Blur Event of Text Box
this.TxtAccountCode.Attributes.Add("onblur", "javascript:CallMe('" + TxtAccountCode.ClientID + "', '" + this.TxtAccountDescription.ClientID + "')");
Once Again Zabardast.
Hi ,
This is Shankar,
how to add asp button to this grid(auto filling grid using Jquery) which fires onClick event to process some action on that particular row data.
and code the code behind to get data of that row
please help me.
very very helpful and nice code
Thank you... Its really useful
Hi, I can run it well with aspx page in asp.net but when i try to implement it in mvc then it gives problem. I wrote web method in controller and called it from ajax call. seems blank table. When i debugged and checked i was the list array returning data, but in chrome development tool (F12), it shows blank data in networking tab. Is there any js conflict? please let me know.
its not running on asp.net 2.0
Its not working. I m getting following error
Failed to load resource: the server responded with a status of 500 (Internal Server Error)
http://localhost:1582/Default.aspx/BindDatatable
Excellent, thank you so much for such an "Easy to understand" article
it works fine...but additionally i should add the edit and delete links to the table...can u pls help me
undefined error what is it
its very much useful thank you..... :)
nice article
thank you. good article.
1155 rows only reterive the code... how can i get more records 1155
This code perfecty work for below 1155 rows of records only..... how can i get the record more then 1155...
Guys check it.........
You are a god
how edit this records?
What if I return data as DataTable(i,e DataTable in return type)? How to retrieve return data in jQuery as DataTable in return type?
............
How to bind paging in Table Using Jquery Json
Excellent, very useful and I worked very well
Very Helpful
show error if reocrds more then 200 hunders
Note: Only a member of this blog may post a comment.