Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Bind data to gridview with JQuery or JSON in asp.net

Mar 31, 2012
Introduction:

Here I will explain how to bind data to asp.net gridview with JQuery or JSON using c#, vb.net or bind data to 
gridview with JQuery or JSON in asp.net using c#, vb.net.

Description:
  
In previous article I explained
JQuery UI AutoComplete textbox with database and call asp.net pagemethods in JQuery. Now I am using those concepts to explain how to bind data to gridview 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 Gridview 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++) {
$("#gvDetails").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">
<asp:GridView ID="gvDetails" runat="server">
<HeaderStyle BackColor="#DC5807" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</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. If you want to know about script function mentioned 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)
{
if (!IsPostBack)
{
BindColumnToGridview();
}
}
/// <summary>
/// This method is used to bind dummy row to gridview to bind data using JQuery
/// </summary>
private void BindColumnToGridview()
{
DataTable dt = new DataTable();
dt.Columns.Add("UserId");
dt.Columns.Add("UserName");
dt.Columns.Add("Location");
dt.Rows.Add();
gvDetails.DataSource = dt;
gvDetails.DataBind();
gvDetails.Rows[0].Visible = false;
}

[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; }
}
If you observe above code in page load I written one method BindColumnToGridview() in this method I am binding columns and row values to datatable and assigned that value to gridview this one condition is necessary to bind gridview with jquery because if it contain header part and data then only we have chance to bind data correctly to gridview with JQuery.

Another Method BindDatatable() in this method I am getting data from database and converting datatable to array and returing that value because Jquery can’t understand DataTable, DataSet or DataReader objects because of that I converted datatable to array string format and returned it.

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

If Not IsPostBack Then
BindColumnToGridview()
End If
End Sub
''' <summary>
''' This method is used to bind dummy row to gridview to bind data using JQuery
''' </summary>
Private Sub BindColumnToGridview()
Dim dt As New DataTable()
dt.Columns.Add("UserId")
dt.Columns.Add("UserName")
dt.Columns.Add("Location")
dt.Rows.Add()
gvDetails.DataSource = dt
gvDetails.DataBind()
gvDetails.Rows(0).Visible = False
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 RSS subscribe by email Subscribe by Email

47 comments :

Chakradhar said...

But this gridview show only 1000 rows not complete data.

Sami said...

Wonderful Job

Sami said...

Wonderful job

Unknown said...

thanks ,
i put the json code in function

function BindGridView() {



and wanna to call it every 5000 sec by this cod

window.setInterval(BindGridView, 5000);

it work perfect but the data has been duplicated every 5 second ,please help me if u want to prevent duplicated data to get new data only from data base , thanks .

Anonymous said...

super,ultimate concept
- amit kumar rupak

Anonymous said...

hi.how can i insert data from my jquery mobile website(asp.net web form)into sql database.plz help me...

Anonymous said...

;lop

Abhijit Barua said...

respected sir,
i get problem on setting label value within datalist. i return value from web service as list and i get value at json. i display it label also but in case of datalist i not able to bind. plz help me.

David said...

Nice. Remember to use one of these if in master page style :

$("#MainContent_gvDetails").append(
or
$("#ContentPlaceHolderID_gvDetails").append(

Anonymous said...

Great Job

everardo cunha said...

this is very elegant

VIJAY KARNAM said...

Excellent article....!

Need help to bind the same data to a textbox or label in the grid.

Please respond ASAP.... THANKS IN ADVANCE...!

adusumilli anudeep said...
This comment has been removed by the author.
Madhu B said...

Very useful article. Thank you so much Keep posting...

Dhiraj said...

I like this Article .... But anybody can tell me that .. if i have data in purly in json formate then how to bind that json data directly to Griedview or convert it in datatable and bind with griedview.... please replay here..

Anonymous said...

how to edit, update and delete data using javascript in grig view????

Anonymous said...

how to edit, update and delete data using javascript in grig view???? Please Reply as Soon as Possible...

adusumilli anudeep said...

anna nannu batikinchinav thank you anna

Unknown said...

awsome ,but how delete data

Marthak software solutions. said...

greate job thanks by Marthak Software Solutions

Anonymous said...

kamal ke bande ho yar

Manna InfoTech said...

good work!

S. Rama Shankara Rao said...

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.

Help said...

Hi,in my database 100 records are their I want to display only 25 records in gridview using asp.net,
plz help me

Unknown said...

this code is running very fine
but for clearing grid view for next time
I added this line in document.ready
$("[id$=GridView1] tr:not(:first-child)").html("");
//Gridview1 is id and the empties gridview except the header

sayed farhan said...

what if i have a paginaiton in gridview?

sandy said...

hi i want to get this work in to master page concept. but it is not working can any one give me feedback for this thanks

Anonymous said...

Data which has binded is not there after the postback

Unknown said...

you dnt have attach database with these example

Unknown said...

Thats works fine. I used it with xml file.

if i want to transmit a parameter like this:
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "TestJsonGridview.aspx/BindDatatable",
data: "{Username:" + UserName +"}",
dataType: "json",
success: function (data) { }

TO VB CODE BEHIND :
Public Shared Function BindDatatable(ByVal UserName As String) As UserDetails()
Dim details As New List(Of UserDetails)()
Dim ds As DataSet = New DataSet()

Try
ds.ReadXml(FileNameXml)
Dim dv As DataView = New DataView(ds.Tables(0))

dv.Sort = "User"
dv.RowFilter = " User = '" & UserName & "' "
end try

This generate error 500 immediately .

Some help will be appreciate.
if somebody need code for this xml file:
lesage_farid@hotmail.com

prashanth.gourishetti said...

can anyone help me
this code is working in IE but not in chrome
error:0 error

Anonymous said...

Hello,

How to bind data in RadGrid control using Ajax/Jquery/JSON/any other method...But my real requirements is,need to bind data in a RadTextBox which is inside the RadGrid...also need to activate the RadGrid's Paging property...RadGrid may contains more then 20K data...

pls contact me:santhoshnair86@gmail.com


Regards
Santhosh

queryingsql said...

good one

NARAYANAN said...

i have 9000 records but it only takes 800 records only. More than that it gives error.Please Help

SURESH MCA said...

Super Articles

Unknown said...

what if i want to add some tooltip and some controls like checkbox and buttons in Grid. is it possible to add hover and click events etc. ?

jaymin modi said...

best but it can loaded only 1293 records.........................if there is any solution then help me...jayminmodi8@yahoo.c0m

Unknown said...

Hi Suresh,
It is not working in for me, infact the values from webmethod is coming currectly but while append to girdview it is not working, i have gone through the page source also there is no table has created for girdview, perhaps there is something that i want to do than u written, please reply me ASAP

Unknown said...

i am sorry, now it is working for me, previously it was not because i didn't bind the gridview in page load

Anonymous said...

fgfgfgf

Anonymous said...

Help; error

d.length is null or not an object .... help me please

Unknown said...

Dear ,

Can you please reply me how to bind date value

Unknown said...

thank for such wonderful artical but i am unable to bind more than 7000 records i have used same code illustrated above and it gives me alert message 'error' i can bind small amount of data but not more than 371+
records please reply me ASAP....

Unknown said...

Super CoDE Sir I A Fresher But it is a code very help full for fresher
thankQ sir

Anonymous said...

why we need to bind dummy rows on page lode

Anonymous said...

very good..but if i want to save grid data to database..then it will not show the data on cs page..how to do that?

Unknown said...

This Code is not working,inside updatepanel

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.