Server-side delete row

keyroche

Hello!
I am using DataTables for displaying information in the back end of my website. One of the uses is to display the pages I have on my site. I need to be able to delete the pages from the database and the table. I tried to use the built in delete function, but after searching the forum realized it only works for a static table. I was wondering how to delete the row from the table while also deleting it from the database. Whatever function I need for deleting it, I also need to be able to pass the 'id' of the page through to mysql so it can delete the row.

Thank you ahead of time for any help,
Key Roche'

anjibman

I am also looking for the same issue - deleting the row from table as well as DB.
How can we achieve this?
Any hint will be appreciated.

Thank you
Anjib

Alex.Sh

It will be great to hear some guide how to delete rows using server-side processing. (I'm speaking only about client side, it's not a problem to delete DB row, knowing it's ID)

btvbill

Yes, I would also like to see a guide for adding, updating and deleting with server side processing. Other than that, these static tables are just toys and not of much use. Vague instructions to do Ajax calls to ID's is about as much use as showing a movie trailer and then saying "might be a theater you can watch it at if you can find one somewhere". Details would be greatly appreciated.

allan

There are a lot of ways of doing this, and which one is the right one for you will depend upon your exact deployment case. However this is what I would normally do:

1. Have the DB row ID as an ID on the TR row (for example ). The easiest way of doing this is with DT_RowId: http://datatables.net/release-datatables/examples/server_side/ids.html

2. From there it is trivial to get the row id and Ajax send it to the server which will do a DELETE FROM 'table' WHERE id = _POST['id'];

3. In the 'success' handler for the delete Ajax call just call fnDraw which will redraw the table, minus the deleted row (since it is no longer on the server).

Allan

prasannaj

The fnDraw does a ajax call that makes the number a server calls 2. (fnDraw + delete request). If the new data (with the deleted row removed) returned in the same delete request can the datatables be configured to use that?

chankl78

This is my humble code which i try solving 2 weeks for deleting items.
I am using asp.net razor (cshtml)

I am also using some 3rd party modal dialog. So u can ignore all those $.msg or $.wl_Alert

[code]
$(document).ready(function () {
var ModuleID = getParam('MM');
var MMName = "IN";
var RowID = "";
var ColumnID = "";
var oTable = $('#tModuleListing').dataTable({
"iDisplayLength": 25, // Default No of Records per page on 1st load
"aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]], // Set no of records in per page
//"aaSorting": [[0, "asc"]], // Default 1st column sorting
"aoColumnDefs": [{ "bVisible": false, "aTargets": [0] }, { "bVisible": false, "aTargets": [1] },
{ "bVisible": false, "aTargets": [2]}], // Hide Column
"bStateSave": true, // Remember paging & filters
"bDeferRender": true, // Delay loading of DOM
"bProcessing": true,
"bServerSide": true,
"bPaginate": false, // Disable pagination, showing full records.
"bInfo": false,
"bFilter": false,
"sPaginationType": "full_numbers", // Include page number
"sAjaxSource": 'Ajax_Functions/ModuleDetailListingSelect.cshtml?MMName=' + MMName + '&ModuleID=' + ModuleID,
"aoColumns": [
{ "mDataProp": "ModuleDetailID" }, { "mDataProp": "ItemID" }, { "mDataProp": "ItemAltID" },
{ "mDataProp": "ItemDescription" }, { "mDataProp": "ItemQty" }, { "mDataProp": "UnitPrice" },
{ "mDataProp": "LineDiscount" }, { "mDataProp": "LineGST" }, { "mDataProp": "LineAmt" }
],
"fnDrawCallback": function () {
$("#tModuleListing tbody tr").click(function () {
var position = oTable.fnGetPosition(this); // getting the clicked row position
RowID = oTable.fnGetData(position); // getting the value of the first (invisible) column
sessionStorage.setItem("ModuleDetailID", RowID.ModuleDetailID); // HTML 5 Session Storage;
});
oTable.$('td').click(function () {
var sData = oTable.fnGetPosition(this);
ColumnID = (sData.toString()).substring(4);
});
}
}).makeEditable({
sDeleteRowButtonId: "btnDeleteItem",
fnOnDeleting: function () {
$.confirm("Do you want to Delete Item " + RowID.ItemDescription + "?", function () {
$.msg("Processing - Deleting Item " + RowID.ItemDescription, { header: 'Deleting Item' });
$.ajax({
type: 'POST',
data: { "MMName": "IN", "ModuleID": $('#txtModuleID').val(), "ModuleDetailID": RowID.ModuleDetailID, "mAcc": "4",
"mAccType": "6" },
dataType: 'json',
url: 'Ajax_Functions/ModulesItemsDeleteFunctions.cshtml',
success: function (data) {
if (data == "Denied") {
$.msg('No Access Rights to delete!', { header: 'Access Denied!' });
$.wl_Alert('No Access Rights to delete!', 'warning', '#content');
}
else {
var oTable = $('#tModuleListing').dataTable();
oTable.fnDraw();

$("#txtDiscount").attr("value", data.LineDiscount.toFixed(2));
$("#txtGST").attr("value", data.BCGST.toFixed(2));
$("#txtAmount").attr("value", data.BCAmount.toFixed(2));
$("#txtSubTotal").attr("value", data.BCSubTotal.toFixed(2));
$("#txtBalance").attr("value", data.Balance.toFixed(2));
$("#txtAmtRecd").attr("value", data.AmtRecd.toFixed(2));
$.msg("Item - " + RowID.ItemDescription + " Deleted!", { header: 'Success!' });
$.wl_Alert("Item - " + RowID.ItemDescription + " Deleted!", 'success', '#content');
}
},
error: function () {
$.wl_Alert('Failed to Delete item', 'warning', '#content'); $.msg(id, { header: 'Deleting Item' });
}
});
});
return false;
},
"aoColumns": [ null, null, null, null, null, null ] // Disable all the inline editable
});
}); // tModuleListing - Items Detail Datatable -- END
[/code]

This is my ModulesItemsDeleteFunctions.cshtml codes.
[code]
var ModuleID = Request["ModuleID"];
var ModuleDetailID = Request["ModuleDetailID"];
var MMName = Request["MMName"];
var Acc = Request["mAcc"];
var AccType = Request["mAccType"];

// Find the correct data
var SQLDelete = "";
var db = Database.Open("xxxConnectionString");

// Check Access Rights
var varSQLAccessRightsSelect = "SELECT * FROM qryModuleAccessListing WHERE ModuleID = @1 AND PositionID = @0";
var varSQLAccessRights = db.QuerySingle(varSQLAccessRightsSelect, int.Parse(Request.Cookies["UserInfo"]["sPID"]), int.Parse(Acc));

bool Access = false;

switch (AccType)
{
case "1":
Access = varSQLAccessRights.ModuleAdd;
break;
case "2":
Access = varSQLAccessRights.ModuleEdit;
break;
case "3":
Access = varSQLAccessRights.ModuleVoid;
break;
case "4":
Access = varSQLAccessRights.ModulePrint;
break;
case "5":
Access = varSQLAccessRights.ModuleRead;
break;
case "6":
Access = varSQLAccessRights.ModuleDelete;
break;
}

if (Access == false)
{
Json.Write("Denied", Response.Output);
}
else
{
switch (MMName)
{
case "IN":
SQLDelete = "DELETE FROM mInvoiceDetail WHERE ModuleDetailID=@0";
db.Execute(SQLDelete, ModuleDetailID);
var SQLResult = "SELECT BCAmount, Balance, BCSubTotal, BCGST, AmtRecd, LineDiscount FROM qryInvoiceListing WHERE ModuleID = @0";
var Data = db.QuerySingle(SQLResult, ModuleID);
Json.Write(Data, Response.Output);
break;
case "PU":
SQLDelete = "DELETE FROM mPurchaseDetail WHERE ModuleDetailID=@0";
db.Execute(SQLDelete, ModuleDetailID);
SQLResult = "SELECT BCAmount, Balance, BCSubTotal, BCGST, AmtPaid, LineDiscount FROM qryPurchaseListing WHERE ModuleID = @0";
Data = db.QuerySingle(SQLResult, ModuleID);
Json.Write(Data, Response.Output);
break;
}
}
}
[/code]

Hope it helps.

Smile
Chankl78