How to update data in SQL for an html table row using Input Button, in JS or C#?

Question

I have a datatable in C# and I am converting it to html table like below.

public static string ConvertDataTableToHTML(DataTable dt)
{
        StringBuilder html = new StringBuilder();
        html.Append("<table id='example' class='table table-striped table-bordered' cellspacing ='0' width ='100%' font size='8' aria-hidden='true'>");

       //add header row
        html.Append("<thead>");
        html.Append("<tr>");
        for (int i = 0; i < dt.Columns.Count; i++)
            html.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
        html.Append("<td>" + "Action" + "</td>");
        html.Append("</tr>");
        html.Append("</thead>");

        //add rows
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            html.Append("<tr>");
            for (int j = 0; j < dt.Columns.Count; j++)
                html.Append("<td>" + dt.Rows[i][j].ToString() + "</td>");
            html.Append("<td><input type=\"button\" value=\"Delete\" onclick=\"deleteRow(this)\"/></td>");
            html.Append("</tr>");
        }

        html.Append("</table>");

        return html.ToString();
}

This is showing a table in my aspx page like below:

Name City Quantity  Action
A     X      5      Delete
B     Y     10      Delete
C     Z     15      Delete

When I click "Delete" button for a row, the function below works and the row is gone from the result table.

<script>
    function deleteRow(btn)
     {
        var row = btn.parentNode.parentNode;
        row.parentNode.removeChild(row);
     }
</script>

What I want is that, in addition to the current process, I need to run a SQL query to update IsRemoved flag for this data in my SQL Server 2014 table.

The query I need to run: Update MyTable set IsRemoved=1 where Name='A' and City='X'

I could not manage to run it in JavaScript function, and could not find a way to execute another function in C# after the JS function. OnClientClick is not working since it is not an asp:Button, and when I try to use asp:Button instead of input element, it does not show it on the screen.

How can I change data in DB here for such an example? Please note that I am trying not to use a GridView. Any help would be appreciated.

EDIT: By using Ajax, how can I send paramaters from my ajax call to c#:

I am trying:

$.ajax({
     type: 'POST',
     url: 'mypage.aspx/DeleteRowFromDB',
     data: JSON.stringify({ name: **<nameshouldcomehere>**, city:**<cityshouldcomehere>** }),
     contentType: 'application/json; charset=utf-8',
     dataType: 'json',
      success: function (msg) {
           var row = btn.parentNode.parentNode;
           row.parentNode.removeChild(row);
      }
});

I can't find how to set name and city dynamically based on the row clicked the delete button, any tips?

call an `ajax` function which will call a `WebMethod` on your`.cs` page which will mark the row as IsRemoved in the DB and then on the success of the `ajax` remove the row from the `html`. — vikscool, Jan 22 '19 at 16:14
You can use `ajax` to post the `name` and the `city` values of the row to your server and have it call a stored procedure to mark that item as `IsRemoved`. Resource for how to use `ajax` (https://developer.mozilla.org/en-US/docs/Web/Guide/AJAX) — Ryan Wilson, Jan 22 '19 at 16:15
A simple example: https://stackoverflow.com/questions/7770679/jquery-ajax-call-to-an-asp-net-webmethod. Inside the webmethod you will execute the update query/stored procedure. And your button will call the ajax method with proper payload which will be used as your webmethod parameter. Heres another one https://www.aspsnippets.com/Articles/Calling-ASPNet-WebMethod-using-jQuery-AJAX.aspx — Sabbir Hassan, Jan 22 '19 at 16:53
Thanks all for the comments, I am working on setting an Ajax call to my page, I'll update you with the result. — Eray Balkanli, Jan 22 '19 at 16:54
@ErayBalkanli **1st** To get the `Name` and `City`, take your code`var row = btn.parentNode.parentNode;` out of the ajax call. So its row values can be accessed easily. **2nd** you can access the `td` values from the `row` variable created like: `var cells = row.getElementsByTagName("td");` and then it will return you an array of whose `innerText` property you can use to get their value like:`var name = cells[0].innerText; var city=cells[1].innerText;` — vikscool, Jan 22 '19 at 17:36
@vikscool thanks, it is working. You saved my day. If you add an answer to this question I'll upvote it and select as correct answer. — Eray Balkanli, Jan 22 '19 at 17:48

score 2 · Accepted Answer · answered Jan 23 '19 at 03:53

In your .cs page create a WebMethod which will mark the Database entry as IsRemoved=1 as:

[System.Web.Services.WebMethod]
public static string DeleteRowFromDB(string name,string city)
{
   var status = "0";
   //Your code to mark `IsRemoved=1` for the selected entry goes here and set the `status` variable as status="1" if the DB command successed.
   return status;
}

And then create a function with an ajax call to invoke the created WebMethod and remove the row from HTML if the status is true as:

function deleteRow(btn)
{
   var row = btn.parentNode.parentNode;
   var cells = row.getElementsByTagName("td");
   var reqData = JSON.stringify({ name: cells[0].innerText, city:city=cells[1].innerText });
   //now make a call to the `WebMethod` via `ajax`.
   $.ajax({
    type: 'POST',
    url: 'mypage.aspx/DeleteRowFromDB',
    contenttype: 'application/json; charset=utf-8',
    data: reqData,
    datatype: 'json',
    success: function (response) {        
       if(response === "1") {
          row.parentNode.removeChild(row);
       }
       else
       {
         //do other stuff
       }
    },
    error: function (error) {
        //handle the error
    }
});

}

Note: if the response variable in the ajax success function doesn't have the desired value try to look for its response.d property value.

How to update data in SQL for an html table row using Input Button, in JS or C#?

1 Answers1