Uncaught Unable to automatically determine field from source. (C# ASP.NET)

sbsu

Error: Uncaught Unable to automatically determine field from source. Please specify the field name.

Hello everyone. I'm trying to evaluate and possibly utilize the datatables.net editor along with its .NET libraries and would like another set of eyes on my code to see what's causing the error I've mentioned. I have previously had great success marrying datatables.net with jeditable and ASP.NET using a variety of tools (including building my own back end API) but would like to try the datatables.net editor as an intended replacement of jeditable and custom APIs.

The test scenario is very simple: Build a couple of SQL Server tables to test editor's join capability. Let's display some records and display a select/dropdown when there is a joined table. Like this: https://editor.datatables.net/examples/inline-editing/join.html

Step 1 - define and build SQL Server Tables

Table1 Name: [TestProduct]
Field: [TestProductId], int, pk, not null, non-identity
Field: [TestProductName], string
Field: [TestProductLocationId], int, fk, not null

Table2 Name: [TestProductLocation]
Field: [TestProductLocationId], int, pk, not null, non-identity
Field: [TestProductLocationDescription], string

Step 2 - query tables to test relationships
SQL to join tables using the common [TestProductLocationId] key:

SELECT 
[TestProduct].[TestProductId], 
[TestProduct].[TestProductName], 
[TestProduct].[TestProductLocationId], 
[TestProductLocation].[TestProductLocationDescription]
FROM [TestProduct] 
INNER JOIN [TestProductLocation] ON [TestProduct].[TestProductLocationId] = [TestProductLocation].[TestProductLocationId]

Query result:
0, Test Product Zero, 0, Located at Facility 0
1, Test Product One, 1, Located at Facility 1
2, Test Product Two, 2, Located at Facility 2

Step 3 - instruct datatables what fields to display in the table and define a select/join condition
Here is my code to show the table data as well as provide a select/dropdown which points to the related table:

columns: [
    {
        data: null,
        defaultContent: '',
        className: 'select-checkbox',
        orderable: false
    },
    { data: 'TestProductId' },
    { data: 'TestProductName' },
    { data: '[TestProductLocation].[TestProductLocationDescription]', editField: '[TestProduct].[TestProductLocationId]' }
],

select: {
    style: 'os',
    selector: 'td:first-child'
},
buttons: [
    { extend: "create", editor: editor },
    { extend: "edit", editor: editor },
    { extend: "remove", editor: editor }
],

Step 4: Define database table model classes

namespace DatatablesNet.Models
{
    public class DatatablesNetJoinProductModelTestProduct
    {
        public decimal TestProductId { get; set; }

        public string TestProductName { get; set; }

        public int TestProductLocationId { get; set; }
    }

    public class DatatablesNetJoinProductModelTestProductLocation
    {
        public int TestProductLocationId { get; set; }

        public string TestProductLocationDescription { get; set; }
    }
}

Step 5: Utilize the editor .NET libraries to handle CRUD; pass in the models
The C# ASP.NET webform codebehind looks like this:

    protected void Page_Load(object sender, EventArgs e)
    {
        var request = HttpContext.Current.Request;

        using (var db = new Database("sqlserver", dbcsProperty))
        {
            var response = new Editor(db, "TestProduct")

            .Model<DatatablesNetJoinProductModelTestProduct>("TestProduct")
            .Model<DatatablesNetJoinProductModelTestProductLocation>("TestProductLocation")
            .Field(new Field("TestProduct.TestProductLocationId")
                .Options(new Options()
                    .Table("TestProductLocation")
                    .Value("TestProductLocationId")
                    .Label("TestProductLocationDescription")
                )
                .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
            )
            //INNER JOIN TestProductLocation ON TestProduct.TestProductLocationId = TestProductLocation.TestProductLocationId
            .LeftJoin("TestProductLocation", "TestProduct.TestProductLocationId", "=", "TestProductLocation.TestProductLocationId")
            .Process(request)
            .Data();

            Response.ContentType = "text/json";
            Response.Write(JsonConvert.SerializeObject(response));
        }
    }

Step 6: Test the application
Web page displays the table as follows:
[checkbox control], 0, awesome product, [null/blank]

The blank column should contain the values of [TestProductLocationDescription] but whereas it's blank, clicking on the cell produces the 'Unable to automatically determine field from source' error message.

Troubleshooting performed
Have tried:
1. Separating model classes into their own class files
2. Not passing in the models into editor and defining all fields manually
3. Experimenting with various table referencing conventions (dotted notation, using [ and ] and various combinations of both
4. Looked at technical notes, such as: https://datatables.net/tn/11
5. Looked at the request/response data, including json.
6. Etc.

Investigating json results:

Evaluating messages based on table/field notations:

Experimenting with different table/field notations:

Datatables does a great job drawing the table, providing paging, search, sort, etc. and I find no trouble with any of that. I also find no trouble using editor on 1:1 data using the cell editor. This tells me that I can successfully configure the .NET editor libraries and back end code just fine.

However, the one piece of functionality that I really need the most is the ability to define simple table joins in editor and be able to use select/dropdown controls within the table cells. Like this: https://editor.datatables.net/examples/inline-editing/join.html

Any help would be appreciated. Thank you.

sbsu

If I massage the table and editor definitions on the UI side as follows, I can eliminate the datatables error message but the select/dropdown remains empty.

sbsu

It appears to me after another look at the working examples online as well as code generated with the code generator that all of these examples are using the editor library to not only handle the editing/updating/deleting but also the reading and populating of the basic datatable....

Does this mean datatables.net cannot do table joins when data is obtained from an external json api?

sbsu

There were a number of issues.... In general, I resolved them by going away from a custom json delivery api and used the editor api. Then using the proper table (dotted) notation allowed me to properly display the table data. I used a left join parameter and options in editor and this made the dropdowns work properly.

allan

Hi,

Good to hear you got this resolved - thanks for posting back. It isn't immediately clear to me why it wasn't working for you, but if it happens again, could you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Does this mean datatables.net cannot do table joins when data is obtained from an external json api?

If the joined information is in the JSON data, then yes - it just uses that JSON file.

Allan