Requested unknown parameter 'tblIngredient.IngredientName' for row 0, column 0

Requested unknown parameter 'tblIngredient.IngredientName' for row 0, column 0

Jords2point0Jords2point0 Posts: 9Questions: 1Answers: 0

All was going fine. I was doing a Left Join and displaying data from 3 related tables in MVC...until I upgrade from ASP.NET CORE 2.2 to 3.0. Then I get this error.

_Uncaught Error: DataTables warning: table id=recipeIngredientTable - Requested unknown parameter 'tblIngredient.IngredientName' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4_

Yes I looked at the link in that error. I do not see any typos in the unknown string parameter. The field names match my SQL Server database table fields, all of them. I mean it worked already in .NET Core 2.2. Also from the breakpoints in the server-side C# code I notice the values are being retrieved successfully and the database is being seeded correctly. Except the table is not rendering in the front-end. I can only wonder why.

Yes I am creating the database with EF Core and know it is not supported with Editor libraries. All good.

index.cshtml

@model DTEditorLeftJoinSample.Models.RecipeIngredient

@{
    ViewData["Title"] = "Index";
}

<h1>Index</h1>
<p>
    <a asp-action="Create">Create New</a>
</p>
<table id="recipeIngredientTable">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Recipe)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Ingredient)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Quantity)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody></tbody>
</table>
@section scripts{
    <script>
    $.fn.dataTable.ext.errMode = 'throw';
    function renderDT_RowId(data) {
        return data.replace('row_', '');
    };
    var oTable = $('#recipeIngredientTable').DataTable({
        "ajax": {
            type: "POST",
            "url": "@Url.Action("LeftJoinRecipesAndIngredientsOntoRecipeIngredient")",
            "dataSrc": function (result) {
                return result.data;
                }
        },
        "columns": [
            { "data": "tblIngredient.IngredientName"},
            { "data": "tblRecipe.Description"},
            { "data": "tblRecipeIngredient.Quantity" },
            {
                "data": null,
                "render": function (value) {
                    return '<a href="/RecipeIngredients/Details/' + renderDT_RowId(value.DT_RowId) + '"button type="button" class="btn btn-primary btn-block">Details</a> <br> '
                        + '<a href="/RecipeIngredients/Edit/' + renderDT_RowId(value.DT_RowId) + '"button type="button" class="btn btn-info btn-block">Edit </a> <br> '
                        + '<a href="/RecipeIngredients/Delete/' + renderDT_RowId(value.DT_RowId) + '"button type="button" class="btn btn-primary btn-block">Delete</a>';
                }
            }
            ]
    });
    </script>
}

RecipeIngredientsController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using DTEditorLeftJoinSample.Data;
using DTEditorLeftJoinSample.Models;
using DataTables;
using Microsoft.Extensions.Configuration;

namespace DTEditorLeftJoinSample.Controllers
{

    public class RecipeIngredientsController : Controller
    {
        private readonly CookingContext _context;


        private readonly IConfiguration _config;

        public RecipeIngredientsController(CookingContext context, IConfiguration config)
        {
            _context = context;
            _config = config;
        }

        // GET: RecipeIngredients
        public IActionResult Index()
        {
            return View();
        }

        public IActionResult LeftJoinRecipesAndIngredientsOntoRecipeIngredient()
        {
            //DECLARE database connection.
            string connectionString = _config.GetConnectionString("DefaultConnection");
            //CREATE debatable instance.
            using (var db = new Database("sqlserver", connectionString))
            {
                //CREATE Editor instance with starting table.
                var response = new Editor(db, "tblRecipeIngredient")
                    .Field(new Field("tblRecipeIngredient.Quantity"))
                    .Field(new Field("tblRecipe.Description"))                     
                    .Field(new Field("tblIngredient.IngredientName"))
                     //JOIN from tblIngredient column RecipeID linked from tblRecipe column ID
                    //and IngredientID linked from tblUser column ID.                    
                    .LeftJoin("tblRecipe ", " tblRecipe.ID ", "=", " tblRecipeIngredient.RecipeID")
                    .LeftJoin("tblIngredient ", " tblIngredient.ID ", "=", " tblRecipeIngredient.IngredientID")
                    .Process(HttpContext.Request)
                    .Data();
                return Json(response);
            }
        }
   }
}

Ingredient.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DTEditorLeftJoinSample.Models
{
    public class Ingredient
    {
        public int ID { get; set; }
        [Display(Name = "Ingredient Name")]
        public string IngredientName { get; set; }

        public ICollection<RecipeIngredient> RecipeIngredient { get; set; }
    }
}

Recipe.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DTEditorLeftJoinSample.Models
{
    public class Recipe
    {
        public int ID { get; set; }

        public string Title { get; set; }
        public string Description { get; set; }
        public string Direction { get; set; }

        public ICollection<RecipeIngredient> RecipeIngredient { get; set; }
    }
}

RecipeIngredient.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DTEditorLeftJoinSample.Models
{
    public class RecipeIngredient
    {
        public int ID { get; set; }

        [Display(Name = "Recipe ID")]
        public int RecipeID { get; set; }

        [Display(Name = "Ingredient ID")]
        public int IngredientID { get; set; }

        public int Quantity { get; set; }
        public Recipe Recipe { get; set; }
        public Ingredient Ingredient { get; set; }
    }
}

CookingContext.cs

using DTEditorLeftJoinSample.Models;
using Microsoft.EntityFrameworkCore;

namespace DTEditorLeftJoinSample.Data
{
    public class CookingContext : DbContext
    {
        public CookingContext(DbContextOptions<CookingContext> options) : base(options)
        {
        }

        public DbSet<Recipe> Recipe { get; set; }
        public DbSet<Ingredient> Ingredient { get; set; }
        public DbSet<RecipeIngredient> RecipeIngredient { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Recipe>().ToTable("tblRecipe");
            modelBuilder.Entity<Ingredient>().ToTable("tblIngredient");
            modelBuilder.Entity<RecipeIngredient>().ToTable("tblRecipeIngredient ");
        }
    }
}


Replies

  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736

    Start with looking at the JSON response using the browser's network inspector. This technote will give some steps.

    You have this:

                "dataSrc": function (result) {
                    return result.data;
                    }
    

    If your row data is an array in the data object then you don't need this as that is the default location where Datatables will look for the data as described here.

    If you still need help please post an example of your JSON response to include the start (the data object) and a few rows of your data.

    Kevin

  • Jords2point0Jords2point0 Posts: 9Questions: 1Answers: 0
    edited December 2020

    Hi @kthorngren

    Seems it is returning data all the way from the backend even even in the JSON response.

    Here is the preview as it looks neater.

    data: [{dT_RowId: "row_1", tblRecipeIngredient: {quantity: "15"}, tblRecipe: {,…},…},…]
    0: {dT_RowId: "row_1", tblRecipeIngredient: {quantity: "15"}, tblRecipe: {,…},…}
    dT_RowId: "row_1"
    tblIngredient: {ingredientName: "Duis eu ligula felis"}
    ingredientName: "Duis eu ligula felis"
    tblRecipe: {,…}
    description: "Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas."
    tblRecipeIngredient: {quantity: "15"}
    quantity: "15"
    1: {dT_RowId: "row_2", tblRecipeIngredient: {quantity: "3"}, tblRecipe: {,…},…}
    dT_RowId: "row_2"
    tblIngredient: {ingredientName: "Donec id mollis arcu"}
    ingredientName: "Donec id mollis arcu"
    tblRecipe: {,…}
    description: " Etiam aliquam, magna quis lobortis facilisis, lorem eros dignissim nulla, ultrices pulvinar orci lectus a ligula."
    tblRecipeIngredient: {quantity: "3"}
    quantity: "3"
    2: {dT_RowId: "row_3", tblRecipeIngredient: {quantity: "4"}, tblRecipe: {,…},…}
    dT_RowId: "row_3"
    tblIngredient: {ingredientName: "Cras nec enim luctus"}
    ingredientName: "Cras nec enim luctus"
    tblRecipe: {,…}
    description: "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Praesent sed pharetra neque. Curabitur laoreet eu lectus eu tempus. Fusce elementum arcu ut justo tincidunt mattis."
    tblRecipeIngredient: {quantity: "4"}
    quantity: "4"
    

    And here is the actual JSON response

    {"draw":null,"data":[{"dT_RowId":"row_1","tblRecipeIngredient":{"quantity":"15"},"tblRecipe":{"description":"Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas."},"tblIngredient":{"ingredientName":"Duis eu ligula felis"}},{"dT_RowId":"row_2","tblRecipeIngredient":{"quantity":"3"},"tblRecipe":{"description":" Etiam aliquam, magna quis lobortis facilisis, lorem eros dignissim nulla, ultrices pulvinar orci lectus a ligula."},"tblIngredient":{"ingredientName":"Donec id mollis arcu"}},{"dT_RowId":"row_3","tblRecipeIngredient":{"quantity":"4"},"tblRecipe":{"description":"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Praesent sed pharetra neque. Curabitur laoreet eu lectus eu tempus. Fusce elementum arcu ut justo tincidunt mattis."},"tblIngredient":{"ingredientName":"Cras nec enim luctus"}}],"recordsTotal":null,"recordsFiltered":null,"error":null,"fieldErrors":[],"id":null,"meta":{},"options":{},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":null,"cancelled":[]}
    
  • kthorngrenkthorngren Posts: 20,150Questions: 26Answers: 4,736
    edited December 2020

    The response has this for example:

            "tblIngredient": {
                "ingredientName": "Duis eu ligula felis"
            }
    

    You defined it as:

    { "data": "tblIngredient.IngredientName"},
    

    Its case sensitive so it should be ingredientName instead of IngredientName, like this:

    { "data": "tblIngredient.ingredientName"},
    

    Same with the other columns.data options you have the wrong case.

    Kevin

  • Jords2point0Jords2point0 Posts: 9Questions: 1Answers: 0

    Thank you so much @kthorngren it works. Also had to change the value DT_RowId to dT_RowId which totally worked.

    The thing is I never had such case sensitivity when I used DataTables in ASP .NET Core 2.2. Maybe it has changed in future frameworks.

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    The Javascript side of things has always been case sensitive. Possibly things have changed a little on the server-side (although not intentionally on our part if so!).

    Allan

  • Jords2point0Jords2point0 Posts: 9Questions: 1Answers: 0

    @allan

    I think you're right about changes on the server side. Because now that I've upgraded my app to .NET 5.0 guess what, I have to make the first letter of all fields now in upper case in order for the application to run.

    Even had to install newtonsoft manually so I don't get **Uncaught TypeError: Cannot read property 'length' of undefined. **

    I'm sure it's a problem on the server. Anyway thanks for your help guys. I actually made a whole blog about how to integrate DataTables Editor into ASP.NET 5.0 which couldn't have been done without your help. https://layersofabstraction.github.io/DTLeftJoins2.html

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    Very nice! Is it okay if I link to your article from our news feed on the front page?

    Allan

  • Jords2point0Jords2point0 Posts: 9Questions: 1Answers: 0

    @allan

    I would be delighted in seeing that. Thanks.

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    First one of 2021 - many thanks!

    Allan

This discussion has been closed.