Add / Update Join´d Table in .NET MVC / MS SQL
Add / Update Join´d Table in .NET MVC / MS SQL
OT@CODAN
Posts: 10Questions: 3Answers: 0
Link to test case: - (can be provided, but issue semm to be related to .NET Backend Server Processing)
Debugger code (debug.datatables.net): https://debug.datatables.net/uxiraj
Error messages shown: -
Description of problem:
I have an ASP .NET MVC Application, running Version .Net 8.
At first, I integrated all necessary files for the Category Section. And did testing. Adding, editing and deleting is working like a charm.
Following this guide, I was able to create a view with LEFT JOIN:
Which works fine. The Category Name is correctly displayed in the table.
Now, when it comes to editing or adding new entries, it is time for the nasty stuff.
In this project I use JOIN the first time, so I guess I messed it up. But as research in the forum, I am at least not alone...
Models
Backend Code:
[HttpPost("[action]")]
[HttpGet("[action]")]
[HttpDelete("[action]")]
public IActionResult GetProjectDiary()
{
DataTables.DtResponse response = new DtResponse();
var request = HttpContext.Request;
// DB Connection Parameter
string DBConnectionString = HttpContext.Session.GetString("DBConnectionString");
try
{
using (var db = new Database("sqlserver", DBConnectionString, "System.Data.SqlClient"))
{
var editor = new Editor(db, "dbo.ProjectDiary", "ProjectDiaryId")
.Model<ProjectDiary>("dbo.ProjectDiary")
.Model<Category>("dbo.Category")
.Field(new Field("ProjectDiary.ProjectDiaryId")
.Validator(Validation.NotEmpty())
)
.Field(new Field("ProjectDiary.MaschineName")
.Validator(Validation.NotEmpty())
)
.Field(new Field("ProjectDiary.KMNumber")
.Validator(Validation.NotEmpty())
)
.Field(new Field("ProjectDiary.InventoryNumber")
.Validator(Validation.NotEmpty())
)
.Field(new Field("ProjectDiary.ProjectStart")
.Validator(Validation.NotEmpty())
.GetFormatter(Format.DateSqlToFormat("yyyy-MM-dd"))
.SetFormatter(Format.DateFormatToSql("yyyy-MM-dd"))
)
.Field(new Field("ProjectDiary.CategoryId")
.Options(new Options()
.Table("dbo.Category")
.Value("CategoryId")
.Label("Name")
)
.SetFormatter(Format.IfEmpty(null))
)
.LeftJoin("dbo.Category", "Category.CategoryId", "=", "ProjectDiary.CategoryId")
.TryCatch(true)
.Debug(true);
editor.PostCreate += (sender, e) => _logger.LogInformation("create {@id} {@values} {@DT} ", e.Id, e.Values, DateTime.Now);
editor.PostEdit += (sender, e) => _logger.LogInformation("edit {@id} {@values} {@DT} ", e.Id, e.Values, DateTime.Now);
editor.PostRemove += (sender, e) => _logger.LogInformation("remove {@id} {@values} {@DT} ", e.Id, e.Values, DateTime.Now);
return Json(editor.Process(request).Data());
}
}
catch (Exception ex)
{
return Json(ex);
}
}
Frontend Code:
var editor = new DataTable.Editor({
ajax: '/api/GetProjectDiary',
table: '#Editor',
template: '#customForm',
fields: [
{
name: "ProjectDiary.ProjectDiaryId",
type: "hidden"
},
{
label: "@{ @Localizer["MaschineName"] }",
name: 'ProjectDiary.MaschineName',
attr: {
type: 'text',
maxlength: 30,
}
},
{
label: "@{ @Localizer["KMNumber"] }",
name: 'ProjectDiary.KMNumber',
attr: {
type: 'text',
maxlength: 30,
}
},
{
label: "@{ @Localizer["InventoryNumber"] }",
name: 'ProjectDiary.InventoryNumber',
attr: {
type: 'text',
maxlength: 30,
}
},
{
label: "@{ @Localizer["ProjectStart"] }",
name: 'ProjectDiary.ProjectStart',
type: 'datetime',
def: () => new Date(),
format: 'DD.MM.YYYY',
displayFormat: 'DD.MM.YYYY',
wireFormat: 'YYYY-MM-DD',
keyInput: false
},
{
label: "@{ @Localizer["Category"] }",
name: 'ProjectDiary.CategoryId',
type: 'select',
placeholder: 'Select a category',
},
],
});
const table = new DataTable('#Editor', {
// Get Data from API
ajax: {
url: '/api/GetProjectDiary',
type: 'POST'
},
// Columns
columns: [
{ "data": "ProjectDiary.ProjectDiaryId" },
{ "data": "ProjectDiary.MaschineName" },
{ "data": "ProjectDiary.KMNumber" },
{ "data": "ProjectDiary.InventoryNumber" },
{ "data": "ProjectDiary.ProjectStart" },
{ "data": "dbo.Category.Name", "name": "ProjectDiary.CategoryId" },
],
// Column Def
columnDefs: [
{ // ProjectDiaryId
targets: 0,
},
{ // MaschineName
targets: 1,
},
{ // KMNumber
targets: 2,
},
{ // InventoryNumber
targets: 3,
},
{ // ProjectStart
targets: 4,
render: DataTable.render.datetime('DD.MM.YYYY'),
},
{ // Category
targets: 5,
},
],
// Define Layout / Functions
dom: 'Bfrtip',
// Table Configuration
stripeClasses: [],
autoWidth: true,
responsive: true,
select: true,
serverSide: true,
processing: true,
order: [0, "desc"],
fixedHeader: {
header: true,
headerOffset: 130
},
pageLength: 25,
info: true,
paging: true,
ordering: true,
Button config removed (to many characters)
});
The JSON Data, send by server seem to be wrong, because the UPDATE Statement is a SELECT and the changed Data (MaschineName changed from Test to Test1) is not recognized.
The Debugger / Log function shows correct values and a correct action:
edit "1" [("ProjectDiary": [("ProjectDiaryId": 1), ("MaschineName": "Test1"), ("KMNumber": 123456), ("InventoryNumber": "123-654-987"), ("ProjectStart": "2024-02-19"), ("CategoryId": 2)])] 02/21/2024 20:02:31
What goes wrong?? I have no errors in debug in Visual Studio, nor in MS SQL Server, nor in Browser Console.
I really appreciate any help!
Thank you in advance,
Chris
This question has an accepted answers - jump to answer
Answers
Hi!
After a lot of research and just as much trial and error, I finally found the missing link! The Problem was definitely the "dbo", which I had to implement into the editor backend.
But this caused the error with the saving.
In this forum and also in this article: https://consolecommando.net/DTLeftJoins2.html I saw the idea, to change ApplicationDbContext.cs in the Data Folder of the MVC Project.
In the Article, I changed this:
to this:
The JSON Is now correct:
So, Problem solved
Many thanks for the update - great to hear you've got it working!
Allan