How to insert,update and Delete data from Html Table using Ajax Jquery in ASP.Net

Last Reply on Dec 14, 2015 08:32 AM By Shashikant

Posted on Dec 12, 2015 12:50 AM

i want to add (Edit(update/cancel) and Delete like Gridview) button in html table and perform edit and Delete operation using jquery and Webservices ?

Posted on Dec 14, 2015 08:32 AM Modified on on Dec 14, 2015 08:34 AM

Here I have created sample that will help you out.

I referred below article.

ASP.Net jQuery AJAX CRUD: Select Insert Edit Update and Delete in ASP.Net GridView using jQuery AJAX

HTML

<div>
    <table id="tblCustomers" border="0" cellpadding="0" cellspacing="0">
        <thead>
            <tr>
                <th>
                    CustomerId :
                </th>
                <th>
                    Name :
                </th>
                <th>
                    Country :
                </th>
                <th>
                </th>
                <th>
                </th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td class="CustomerId">
                    <span></span>
                </td>
                <td class="Name">
                    <span></span>
                    <input type="text" id="txtEditName" class="Hide" />
                </td>
                <td class="Country">
                    <span></span>
                    <input type="text" id="txtEditCountry" class="Hide" />
                </td>
                <td>
                    <input type="button" id="btnEdit" value="Edit" class="Edit" />
                    <input type="button" id="btnUpdate" value="Update" class="Hide Update" />
                </td>
                <td>
                    <input type="button" id="btnDelete" class="Delete" value="Delete" />
                    <input type="button" id="btnCancel" value="Cancel" class="Hide Cancel" />
                </td>
            </tr>
        </tbody>
        <tfoot>
            <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
                <tr>
                    <td style="width: 150px">
                        Name:<br />
                        <asp:TextBox ID="txtName" runat="server" Width="140" />
                    </td>
                    <td style="width: 150px">
                        Country:<br />
                        <asp:TextBox ID="txtCountry" runat="server" Width="140" />
                    </td>
                    <td style="width: 100px">
                        <br />
                        <asp:Button ID="btnAdd" runat="server" Text="Add" />
                    </td>
                </tr>
            </table>
        </tfoot>
    </table>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $.ajax({
                type: "POST",
                url: "CS.aspx/GetCustomers",
                data: '{}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess
            });
        });

        function OnSuccess(response) {
            var xmlDoc = $.parseXML(response.d);
            var xml = $(xmlDoc);
            var customers = xml.find("Table");
            var row = $("[id*=tblCustomers] > tbody tr:last-child").clone(true);
            $("[id*=tblCustomers] tr").not(':has(th)').remove();
            $.each(customers, function () {
                var customer = $(this);
                AppendRow(row, $(this).find("CustomerId").text(), $(this).find("Name").text(), $(this).find("Country").text())
                row = $("[id*=tblCustomers] > tbody tr:last-child").clone(true);
            });
        }

        function AppendRow(row, customerId, name, country) {
            //Bind CustomerId.
            $(".CustomerId", row).find("span").html(customerId);

            //Bind Name.
            $(".Name", row).find("span").html(name);
            $(".Name", row).find("input").val(name);

            //Bind Country.
            $(".Country", row).find("span").html(country);
            $(".Country", row).find("input").val(country);
            $("[id*=tblCustomers]").append(row);
        }

        //Add event handler.
        $("body").on("click", "[id*=btnAdd]", function () {
            var txtName = $("[id*=txtName]");
            var txtCountry = $("[id*=txtCountry]");
            if (txtName.val() != '' && txtCountry.val() != '') {
                $.ajax({
                    type: "POST",
                    url: "CS.aspx/InsertCustomer",
                    data: '{name: "' + txtName.val() + '", country: "' + txtCountry.val() + '" }',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        var row = $("[id*=tblCustomers] > tbody tr:last-child").clone(true);
                        AppendRow(row, response.d, txtName.val(), txtCountry.val());
                        txtName.val("");
                        txtCountry.val("");
                    }
                });
            } else {
                alert('Please Enter both Name and Country');
            }
            return false;
        });

        //Edit event handler.
        $("body").on("click", "[id*=tblCustomers] .Edit", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find("input").length > 0) {
                    $(this).find("input").show();
                    $(this).find("span").hide();
                }
            });
            row.find(".Update").show();
            row.find(".Cancel").show();
            row.find(".Delete").hide();
            $(this).hide();
            return false;
        });

        //Update event handler.
        $("body").on("click", "[id*=tblCustomers] .Update", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find("input").length > 0) {
                    var span = $(this).find("span");
                    var input = $(this).find("input");
                    span.html(input.val());
                    span.show();
                    input.hide();
                }
            });
            row.find(".Edit").show();
            row.find(".Delete").show();
            row.find(".Cancel").hide();
            $(this).hide();

            var customerId = row.find(".CustomerId").find("span").html();
            var name = row.find(".Name").find("span").html();
            var country = row.find(".Country").find("span").html();
            $.ajax({
                type: "POST",
                url: "CS.aspx/UpdateCustomer",
                data: '{customerId: ' + customerId + ', name: "' + name + '", country: "' + country + '" }',
                contentType: "application/json; charset=utf-8",
                dataType: "json"
            });

            return false;
        });

        //Cancel event handler.
        $("body").on("click", "[id*=tblCustomers] .Cancel", function () {
            var row = $(this).closest("tr");
            $("td", row).each(function () {
                if ($(this).find("input").length > 0) {
                    var span = $(this).find("span");
                    var input = $(this).find("input[type='text']");
                    input.val(span.html());
                    span.show();
                    input.hide();
                }
            });
            row.find(".Edit").show();
            row.find(".Delete").show();
            row.find(".Update").hide();
            $(this).hide();
            return false;
        });

        //Delete event handler.
        $("body").on("click", "[id*=tblCustomers] .Delete", function () {
            if (confirm("Do you want to delete this record?")) {
                var row = $(this).closest("tr");
                var customerId = row.find("span").html();
                $.ajax({
                    type: "POST",
                    url: "CS.aspx/DeleteCustomer",
                    data: '{customerId: ' + customerId + '}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        row.remove();
                    }
                });
            }

            return false;
        });
    </script>
</div>

C#

[WebMethod]
public static string GetCustomers()
{
    string query = "SELECT CustomerId, Name, Country FROM Customers";
    SqlCommand cmd = new SqlCommand(query);
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds);
                return ds.GetXml();
            }
        }
    }
}

[WebMethod]
public static int InsertCustomer(string name, string country)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@Name, @Country) SELECT SCOPE_IDENTITY()"))
        {
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Country", country);
            cmd.Connection = con;
            con.Open();
            int customerId = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return customerId;
        }
    }
}

[WebMethod]
public static void UpdateCustomer(int customerId, string name, string country)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId"))
        {
            cmd.Parameters.AddWithValue("@CustomerId", customerId);
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Country", country);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

[WebMethod]
public static void DeleteCustomer(int customerId)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId = @CustomerId"))
        {
            cmd.Parameters.AddWithValue("@CustomerId", customerId);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

VB

<WebMethod> _
Public Shared Function GetCustomers() As String
	Dim query As String = "SELECT CustomerId, Name, Country FROM Customers"
	Dim cmd As New SqlCommand(query)
	Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
	Using con As New SqlConnection(constr)
		Using sda As New SqlDataAdapter()
			cmd.Connection = con
			sda.SelectCommand = cmd
			Using ds As New DataSet()
				sda.Fill(ds)
				Return ds.GetXml()
			End Using
		End Using
	End Using
End Function

<WebMethod> _
Public Shared Function InsertCustomer(name As String, country As String) As Integer
	Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand("INSERT INTO Customers VALUES(@Name, @Country) SELECT SCOPE_IDENTITY()")
			cmd.Parameters.AddWithValue("@Name", name)
			cmd.Parameters.AddWithValue("@Country", country)
			cmd.Connection = con
			con.Open()
			Dim customerId As Integer = Convert.ToInt32(cmd.ExecuteScalar())
			con.Close()
			Return customerId
		End Using
	End Using
End Function

<WebMethod> _
Public Shared Sub UpdateCustomer(customerId As Integer, name As String, country As String)
	Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand("UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId")
			cmd.Parameters.AddWithValue("@CustomerId", customerId)
			cmd.Parameters.AddWithValue("@Name", name)
			cmd.Parameters.AddWithValue("@Country", country)
			cmd.Connection = con
			con.Open()
			cmd.ExecuteNonQuery()
			con.Close()
		End Using
	End Using
End Sub

<WebMethod> _
Public Shared Sub DeleteCustomer(customerId As Integer)
	Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand("DELETE FROM Customers WHERE CustomerId = @CustomerId")
			cmd.Parameters.AddWithValue("@CustomerId", customerId)
			cmd.Connection = con
			con.Open()
			cmd.ExecuteNonQuery()
			con.Close()
		End Using
	End Using
End Sub

Screenshot