Search and Display result from Database in HTML Table using jQuery AJAX and WebService in ASP.Net

Last Reply 5 months ago By dharmendr

Posted 6 months ago

how to search data from database using ajax,jquery, query string and display the result data in html table in same page, by passing search keyword through query string to next page and display the result there..reply plzz

    <script type="text/javascript"> 
        $(document).ready(function () { 
            $.ajax({
                type: "POST", 
                url: "webservice.asmx/GetData", 
                contentType: "application/json;charset=utf-8", 
                data: {}, 
                dataType: "json", 
                success: function (data) { 
                    $("#gvProduct").empty(); 
                    if (data.d.length > 0) { 
                        $("#gvProduct").append("<tr><th>Product ID</th> <th>Product Name</th><th>Price</th><th> Stock</th></tr>"); 
                        for (var i = 0; i < data.d.length; i++) { 
                            $("#gvProduct").append("<tr><td>" + 
                        data.d[i].ProductID + "</td> <td>" + 
                        data.d[i].ProductName + "</td> <td>" +                     
                        data.d[i].Price + "</td> <td>" + 
                        data.d[i].Stock + "</td></tr>"); 
                        } 
                    } 
                }, 
                error: function (result) { 
                    //Handling error 
                } 
            });
         }); 
     </script>

 

<input type="text" id="txtsearch" name="search" />
<input type="submit" name="submit" id="btnsearch" value="search"/>

 

[WebMethod] 
public static ProductDetails[] GetData() 
{ 
    List<ProductDetails> MyData = new List<ProductDetails>(); 
    string cmdstring = "SELECT* FROM Products"; 
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString)) 
    { 
        SqlCommand cmd = new SqlCommandcmdstring conn); 
        conn.Open(); 
        SqlDataAdapter da = new SqlDataAdapter(cmd); 
        DataTable dt = new DataTable(); 
        da.Fill(dt); 
        foreach (DataRow DR in dt.Rows) 
        { 
            ProductDetails objProduct = new ProductDetails(); 
            objProduct.ProductID = DR["ProductID"].ToString(); 
            objProduct.ProductName = DR["ProductName"].ToString(); 
            objProduct.Price = DR["Price"].ToString(); 
            objProduct.Stock = DR["Stock"].ToString(); 
            MyData.Add(objProduct); 
        } 
        return MyData.ToArray(); 
    } 
}
 
public class ProductDetails 
{ 
    public string ProductID { get; set; } 
    public string ProductName { get; set; }    
    public string Price { get; set; } 
    public string Stock { get; set; } 
}

 

You are viewing reply posted by: dharmendr 5 months ago.
Posted 5 months ago

Hijochk12345,

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

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        ShowProduct();
        function ShowProduct() {
            var productName = window.location.search.split('=')[1] == undefined ? '' : window.location.search.split('=')[1];
            $.ajax({
                type: "POST",
                url: "WebService.asmx/GetData",
                contentType: "application/json;charset=utf-8",
                data: '{name:"' + productName + '"}',
                dataType: "json",
                success: function (data) {
                    $("#gvProduct").empty();
                    if (data.d.length > 0) {
                        $("#gvProduct").append("<tr><th>Product ID</th> <th>Product Name</th><th>Price</th><th> Stock</th></tr>");
                        for (var i = 0; i < data.d.length; i++) {
                            $("#gvProduct").append("<tr><td>" +
                        data.d[i].ProductID + "</td> <td>" +
                        data.d[i].ProductName + "</td> <td>" +
                        data.d[i].Price + "</td> <td>" +
                        data.d[i].Stock + "</td></tr>");
                        }
                    }
                },
                error: function (result) {
                    alert(result.responsetext);
                }
            });
        }

        $('[id*=btnsearch]').on('click', function () {
            if ($('[id*=txtsearch]').val().length > 0) {
                window.location.href = window.location.pathname + "?Name=" + $('[id*=txtsearch]').val();
            }
            else {
                window.location.href = window.location.pathname;
            }
            ShowProduct();
            return false;
        });
    }); 
</script>
<input type="text" id="txtsearch" name="search" />
<input type="submit" name="submit" id="btnsearch" value="search" />
<table id="gvProduct">
    <tr>
        <td>
            ProductID
        </td>
        <td>
            ProductName
        </td>
        <td>
            Price
        </td>
        <td>
            Stock
        </td>
    </tr>
</table>

WebService

C#

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

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    [WebMethod]
    public ProductDetails[] GetData(string name)
    {
        List<ProductDetails> MyData = new List<ProductDetails>();
        string cmdstring = "SELECT TOP 10 ProductID,ProductName,UnitPrice Price,UnitsInStock Stock FROM Products WHERE ProductName = @Name OR @Name IS NULL";
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand(cmdstring, conn);
            if (!string.IsNullOrEmpty(name))
            {
                cmd.Parameters.AddWithValue("@Name", name);
            }
            else
            {
                cmd.Parameters.AddWithValue("@Name", (object)DBNull.Value);
            }
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            foreach (DataRow DR in dt.Rows)
            {
                ProductDetails objProduct = new ProductDetails();
                objProduct.ProductID = DR["ProductID"].ToString();
                objProduct.ProductName = DR["ProductName"].ToString();
                objProduct.Price = DR["Price"].ToString();
                objProduct.Stock = DR["Stock"].ToString();
                MyData.Add(objProduct);
            }
            return MyData.ToArray();
        }
    }

    public class ProductDetails
    {
        public string ProductID { get; set; }
        public string ProductName { get; set; }
        public string Price { get; set; }
        public string Stock { get; set; }
    }
}

VB.Net

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService2
    Inherits System.Web.Services.WebService
    <WebMethod()>
    Public Function GetData(ByVal name As String) As ProductDetails()
        Dim MyData As List(Of ProductDetails) = New List(Of ProductDetails)()
        Dim cmdstring As String = "SELECT TOP 10 ProductID,ProductName,UnitPrice Price,UnitsInStock Stock FROM Products WHERE ProductName = @Name OR @Name IS NULL"

        Using conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("connString").ConnectionString)
            Dim cmd As SqlCommand = New SqlCommand(cmdstring, conn)
            If Not String.IsNullOrEmpty(name) Then
                cmd.Parameters.AddWithValue("@Name", name)
            Else
                cmd.Parameters.AddWithValue("@Name", CObj(DBNull.Value))
            End If
            conn.Open()
            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            Dim dt As DataTable = New DataTable()
            da.Fill(dt)
            For Each DR As DataRow In dt.Rows
                Dim objProduct As ProductDetails = New ProductDetails()
                objProduct.ProductID = DR("ProductID").ToString()
                objProduct.ProductName = DR("ProductName").ToString()
                objProduct.Price = DR("Price").ToString()
                objProduct.Stock = DR("Stock").ToString()
                MyData.Add(objProduct)
            Next
            Return MyData.ToArray()
        End Using
    End Function
    Public Class ProductDetails
        Public Property ProductID As String
        Public Property ProductName As String
        Public Property Price As String
        Public Property Stock As String
    End Class
End Class

Screenshot