Update Database based on HTML Table Row CheckBox checked using jQuery AJAX and Web Service in C# and VB.Net

Last Reply 3 months ago By dharmendr

Posted 3 months ago

i want to update/change checkbox status of grid row   true/false when checked using jquery ajax to database/json..my issue is i am not getting or not able to update the new status when checkbox clicked(i.e. pdtstatus as true/false when clicked)..please reply

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
         $("#chk1").click(function () {
                 var id = $(this).attr("data-id");
                 var pdtstatus = "";
                 var isChecked = $(this).is(":checked");
                 if (isChecked) {
                      PdtStatus = "true";
 
                 }
                 else {
                     PdtStatus = "false";
                     
                 }
                 $.ajax({
                     type: "Post",
                     contentType: "application/json; charset=utf-8",
                     url: "/webservice.asmx/updatetstatus",
                     
                     data: '{PdtStatus:' + PdtStatus + ',eid:' + id + '}',
                     datatype: "json",
                     success: function (data) {
                          
                         
                     },
                     error: function (data) {
                         alert("Error while Updating data of :" + id);
                     }
                 });
             });
        GetProduct();
    });
    function GetProduct() {
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "WebService.asmx/Products",
            data: {},
            dataType: "json",
            success: function (data) {
                var table = $('#example');
                var rows = "";
                for (var i = 0; i < data.d.length; i++) {
                    var name = data.d[i].ProductName;
                    var details = data.d[i].Proddetails;
                    var status = data.d[i].Status;
                    rows += "<tr><td>" + name + "</td><td>" + details + "</td>";
                    if (status == "true") {
                        rows += "<td><input type=checkbox data-id='" + data.d[i].Productid + "'id=chk1 checked=checked ></td></tr>";
                    }
                    else {
                        rows += "<td><input type=checkbox data-id='" + data.d[i].Productid + "' id=chk1 ></td></tr>";
                    }
                }
                table.append(rows);
            },
            error: function (response) {
                alert("Error while Showing update data");
            }
        });
    }
</script>

 

Posted 3 months ago

Hi jovceka,

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

HTML

<table id="example">
    <tr>
        <th>
            ProductName
        </th>
        <th>
            Proddetails
        </th>
        <th>
            Status
        </th>
    </tr>
</table>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        GetProduct();
    });

    function Checked(ele) {
        var id = $(ele).attr("data-id");
        var pdtstatus = "";
        var isChecked = $(ele).is(":checked");
        if (isChecked) {
            PdtStatus = "true";
        }
        else {
            PdtStatus = "false";
        }
        $.ajax({
            type: "Post",
            contentType: "application/json; charset=utf-8",
            url: "WebService.asmx/UpdeateStatus",
            data: '{PdtStatus:' + PdtStatus + ',eid:' + id + '}',
            datatype: "json",
            success: function (data) {
                if (data.d > 0) {
                    alert("ProductId " + id + " Updated Successfully");
                }
            },
            error: function (data) {
                alert("Error while Updating data of :" + id);
            }
        });
    }

    function GetProduct() {
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "WebService.asmx/Products",
            data: {},
            dataType: "json",
            success: function (data) {
                var table = $('#example');
                var rows = "";
                for (var i = 0; i < data.d.length; i++) {
                    var name = data.d[i].ProductName;
                    var details = data.d[i].Proddetails;
                    var status = data.d[i].Status;
                    rows += "<tr><td>" + name + "</td><td>" + details + "</td>";
                    if (status == "true") {
                        rows += "<td><input type=checkbox onclick=Checked(this) data-id='" + data.d[i].ProductId + "' id=chk1 checked=checked ></td></tr>";
                    }
                    else {
                        rows += "<td><input type=checkbox onclick=Checked(this) data-id='" + data.d[i].ProductId + "' id=chk1 ></td></tr>";
                    }
                }
                table.append(rows);
            },
            error: function (response) {
                alert("Error while Showing update data");
            }
        });
    }
</script>

C#

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

/// <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 List<Product> Products()
    {
        List<Product> productList = new List<Product>();
        productList.Add(new Product { ProductId = 1, ProductName = "Chai", Proddetails = "Soft drinks, coffees, teas, beers, and ales", Status = "true" });
        productList.Add(new Product { ProductId = 2, ProductName = "Aniseed Syrup", Proddetails = "Sweet and savory sauces, relishes, spreads, and seasonings", Status = "false" });
        productList.Add(new Product { ProductId = 3, ProductName = "Manjimup Dried Apples", Proddetails = "Dried fruit and bean curd", Status = "false" });
        productList.Add(new Product { ProductId = 4, ProductName = "Carnarvon Tigers", Proddetails = "Seaweed and fish", Status = "true" });

        return productList;
    }

    [WebMethod]
    public int UpdeateStatus(string PdtStatus, string eid)
    {
        int i = 0;
        // Code for updating Status column in Database.
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = "UPDATE Orders SET Status = @Status WHERE ProductId = @Id";
        SqlCommand cmd = new SqlCommand(query);
        using (SqlConnection con = new SqlConnection(conString))
        {
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@Status", PdtStatus);
            cmd.Parameters.AddWithValue("@Id", eid);
            con.Open();
            i = cmd.ExecuteNonQuery();
            con.Close();
        }
        return i;
    }

    public class Product
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public string Proddetails { get; set; }
        public string Status { get; set; }
    }
}

VB.Net

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

<WebService([Namespace]:="http://tempuri.org/")>
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)>
<System.Web.Script.Services.ScriptService>
Public Class WebService
    Inherits System.Web.Services.WebService

    <WebMethod>
    Public Function Products() As List(Of Product)
        Dim productList As List(Of Product) = New List(Of Product)()
        productList.Add(New Product With {
            .ProductId = 1,
            .ProductName = "Chai",
            .Proddetails = "Soft drinks, coffees, teas, beers, and ales",
            .Status = "true"
        })
        productList.Add(New Product With {
            .ProductId = 2,
            .ProductName = "Aniseed Syrup",
            .Proddetails = "Sweet and savory sauces, relishes, spreads, and seasonings",
            .Status = "false"
        })
        productList.Add(New Product With {
            .ProductId = 3,
            .ProductName = "Manjimup Dried Apples",
            .Proddetails = "Dried fruit and bean curd",
            .Status = "false"
        })
        productList.Add(New Product With {
            .ProductId = 4,
            .ProductName = "Carnarvon Tigers",
            .Proddetails = "Seaweed and fish",
            .Status = "true"
        })
        Return productList
    End Function

    <WebMethod>
    Public Function UpdeateStatus(ByVal PdtStatus As String, ByVal eid As String) As Integer
        Dim i As Integer = 0
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "UPDATE Orders SET Status = @Status WHERE ProductId = @Id"
        Dim cmd As SqlCommand = New SqlCommand(query)

        Using con As SqlConnection = New SqlConnection(conString)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@Status", PdtStatus)
            cmd.Parameters.AddWithValue("@Id", eid)
            con.Open()
            i = cmd.ExecuteNonQuery()
            con.Close()
        End Using

        Return i
    End Function

    Public Class Product
        Public Property ProductId As Integer
        Public Property ProductName As String
        Public Property Proddetails As String
        Public Property Status As String
    End Class
End Class

Screenshot