Display data from database without page refresh in ASP.Net using jQuery Ajax

Hi ashish007,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:TextBox runat="server" ID="txtId" Text="TOMSP" />
<asp:Button Text="Search" runat="server" OnClientClick="return showData();" />
<hr />
<table id="tblOrders">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Freight</th>
            <th>Country</th>
        </tr>
    </thead>
</table>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css" />
<script type="text/javascript">
    function showData() {
        var customerId = $('[id*=txtId]').val();
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "Default.aspx/GetOrders",
            data: '{id:"' + customerId + '"}',
            dataType: "json",
            success: function (data) {
                $("#tblOrders").dataTable({
                    searching: false,
                    paging: false,
                    info: false,
                    data: data.d,
                    columns: [
                        { 'data': 'OrderID' },
                        { 'data': 'Name' },
                        { 'data': 'Freight' },
                        { 'data': 'Country' },
                    ]
                });
            }
        });
        return false;
    }
</script>

Namespaces

C#

using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

[WebMethod]
public static List<Order> GetOrders(string id)
{
    List<Order> orders = new List<Order>();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT OrderID, ShipName, Freight, ShipCountry FROM Orders WHERE CustomerID = @Id", con))
        {
            cmd.Parameters.AddWithValue("Id", id);
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    orders.Add(new Order
                    {
                        OrderID = sdr["OrderID"].ToString(),
                        Name = sdr["ShipName"].ToString(),
                        Freight = sdr["Freight"].ToString(),
                        Country = sdr["ShipCountry"].ToString()
                    });
                }
            }
            con.Close();
        }
    }

    return orders;
}

public class Order
{
    public string OrderID { get; set; }
    public string Name { get; set; }
    public string Freight { get; set; }
    public string Country { get; set; }
}

VB.Net

<WebMethod>
Public Shared Function GetOrders(ByVal id As String) As List(Of Order)
    Dim orders As List(Of Order) = New List(Of Order)()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT OrderID, ShipName, Freight, ShipCountry FROM Orders WHERE CustomerID = @Id", con)
            cmd.Parameters.AddWithValue("Id", id)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    orders.Add(New Order With {
                        .OrderID = sdr("OrderID").ToString(),
                        .Name = sdr("ShipName").ToString(),
                        .Freight = sdr("Freight").ToString(),
                        .Country = sdr("ShipCountry").ToString()
                    })
                End While
            End Using
            con.Close()
        End Using
    End Using

    Return orders
End Function

Public Class Order
    Public Property OrderID As String
    Public Property Name As String
    Public Property Freight As String
    Public Property Country As String
End Class

Screenshot