I am aware that there have been similar questions raised before and answers provided. I did go through them but either due to my poor experience/knowledge I was not able to find solution for my problem.
I am using DataTables and got this error:
DataTables warning: table id=DT_load - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
I went through the documentation on the website and the diagnostics but still struggle to work it out. It worked on localhost but when I deployed it on the web server, it broke. I used to get the 404 error but when I switched to Named Handler methods, the above error appeared with 200 status code. Below you will find my code:
**systemList.js**
$(document).ready(function () {
$('#DT_load').DataTable({
ajax: {
url: "?handler=SystemsAll",
type: "GET",
dataSrc: '',
dataType: "json",
},
columnDefs: [{
visible: false,
searchable: false
}],
columns: [
{ "data": "name", "width": "10%"},
{ "data": "website", "width": "10%" },
{ "data": "wiki", "width": "10%" },
{ "data": "lastDeployed", "width": "10%" },
{ "data": "sql", "width": "10%" },
{ "data": "web", "width": "10%" },
{
data: "id",
success: function (data) {
console.log(data);
return `<div class="text-center">
<a href="/AppPages/Update?id=${data}" class='btn btn-success text-white' style='cursor:pointer; width:70px;'>
Update
</a>
<a class='btn btn-danger text-white' style='cursor:pointer; width:70px;'
onclick=Delete('/api/system?id='+${data})>
Delete
</a>
</div>`;
}, width: "40%"
}
],
language: {
emptyTable: "no data found"
},
width: "100%"
});
}
**HomeController.cs**
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ServiceCatalogue.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Linq.Dynamic.Core;
namespace ServiceCatalogue.Controllers
{
[Produces("application/json")]
public class HomeController : Controller
{
private readonly AppDbContext _db;
public HomeController(AppDbContext db)
{
_db = db;
}
public async Task<IActionResult> OnGetSystemsAll()
{
//var allData = await _db.SystemAsset.ToListAsync();
// return new JsonResult(allData);
return Json(new { data = await _db.SystemAsset.ToListAsync() });
}
public async Task<IActionResult> OnPostDelete(int id)
{
var systemFromDb = await _db.SystemAsset.FirstOrDefaultAsync(u => u.Id == id);
if (systemFromDb == null)
{
return Json(new { success = false, message = "Error while Deleting" });
}
_db.SystemAsset.Remove(systemFromDb);
await _db.SaveChangesAsync();
return Json(new { success = true, message = "Delete successful" });
}
}
}
**Index.chtml**
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ServiceCatalogue.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Linq.Dynamic.Core;
namespace ServiceCatalogue.Controllers
{
[Produces("application/json")]
public class HomeController : Controller
{
private readonly AppDbContext _db;
public HomeController(AppDbContext db)
{
_db = db;
}
public async Task<IActionResult> OnGetSystemsAll()
{
//var allData = await _db.SystemAsset.ToListAsync();
// return new JsonResult(allData);
return Json(new { data = await _db.SystemAsset.ToListAsync() });
}
public async Task<IActionResult> OnPostDelete(int id)
{
var systemFromDb = await _db.SystemAsset.FirstOrDefaultAsync(u => u.Id == id);
if (systemFromDb == null)
{
return Json(new { success = false, message = "Error while Deleting" });
}
_db.SystemAsset.Remove(systemFromDb);
await _db.SaveChangesAsync();
return Json(new { success = true, message = "Delete successful" });
}
}
}
**Startup.cs**
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using ServiceCatalogue.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json.Serialization;
using System.Text.Json;
namespace ServiceCatalogue
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.Configure<IISServerOptions>(options =>
{
options.AutomaticAuthentication = false;
});
services.AddDbContext<AppDbContext>(option => option.UseSqlServer(Configuration.GetConnectionString("CatologueDb")));
services.AddControllersWithViews();
services.AddMvc()
.AddNewtonsoftJson(options =>
options.SerializerSettings.ContractResolver =
new DefaultContractResolver());
services.AddRazorPages();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Error");
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
endpoints.MapRazorPages();
});
}
}
}
I made it work by simply removing the controller as it is Razor Page application and not a MVC one. Doing so I used the Name Handler directly in the PageModel class of the razor page where I wanted to render the Data Table. And also make sure you bind the property.
[BindProperty]
public SystemAsset SystemAsset { get; set; }
public IEnumerable<ServerAsset> ServerAsset { get; set; }
public async Task<IActionResult> OnGetSystemsAll()
{
var allData = await _db.SystemAsset.ToListAsync();
return new JsonResult(allData);
}
public async Task<IActionResult> OnPostDelete(int id)
{
var asset = await _db.SystemAsset.FindAsync(id);
if (asset == null)
{
return NotFound();
}
_db.SystemAsset.Remove(asset);
await _db.SaveChangesAsync();
return RedirectToPage("Index");
}
Another problem I needed to fix was the name of the columns in my AJAX call, they need match exactly the name of the columns in the database table.
"ajax": {
"url": "?handler=SystemsAll",
"type": "GET",
"dataSrc": '',
"dataSrc": '',
"datatype": "jsonp",
},
"columns": [
{ "data": "Name", "name": "Name", "width": "10%" },
{ "data": "Website", "name": "Website", "width": "10%" },
{ "data": "Wiki", "name": "Wiki", "width": "10%" },
{ "data": "LastDeployed", "name": "LastDeployed", "width": "10%" },
{ "data": "Sql", "name": "Sql", "width": "10%" },
{ "data": "Web", "name": "Web", "width": "10%" },
Another silly thing that I have missed was to clear out the cache on the browser as it sometimes renders a cached version.
Hope this helps other people.