Error message when trying to save a numeric value to an NVARCHAR field
Error message when trying to save a numeric value to an NVARCHAR field
JonathanTurner
Posts: 2Questions: 1Answers: 0
I cannot link to a test case unfortunately.
Debugger link:
https://debug.datatables.net/urepap
When I add a new line to my Editable Data Table and enter the code 10.
The error message shown is:
System.Data.SqlClient.SqlException: 'Conversion failed when converting the nvarchar value 'NA' to data type int.'
"NA" is the code value for ANOTHER row's data in the database that is the first row that is NOT numerical.
- I am using Editable DataTables in .Net Framework.
- My tables has just 2 fields but I am experiencing this same issue on ALL of my editable data tables.
- The "Code" column in my database table is an NVARCHAR(5), so should allow both letters and numbers (i.e. 10, NV and N123).
- I am using Server Side Processing due to the amount of data to display.
The controller script is as follows:
string dbType = "sqlserver";
string dbConnection = ConfigurationManager.ConnectionStrings["JcbSwpDatabaseContext"].ConnectionString;
using (DataTables.Database db = new DataTables.Database(dbType, dbConnection))
{
DtResponse response = new Editor(db, "MDM.IncoTerms", "Id")
.Model<MDMModels>()
.Field(new Field("Code")
.Validator(Validation.MaxLen(3, new ValidationOpts { Message = "Maximum character length is 3." }))
.Validator(Validation.Required(new ValidationOpts { Message = "Code is required." }))
.Validator(Validation.Unique(new ValidationOpts { Message = "Please enter a unique Code." }))
)
.Field(new Field("Name")
.Validator(Validation.MaxLen(250, new ValidationOpts { Message = "Maximum character length is 250." }))
.Validator(Validation.Required(new ValidationOpts { Message = "Name is required." }))
)
.TryCatch(false)
.Process(HttpContext.Request.Form)
.Data();
return Json(new
{
data = response.data,
draw = response.draw,
recordsFiltered = response.recordsFiltered,
recordsTotal = response.recordsTotal,
cancelled = response.cancelled,
debug = response.debug,
searchPanes = response.searchPanes,
options = response.options,
upload = response.upload,
error = response.error,
fieldErrors = response.fieldErrors,
files = response.files,
id = response.id,
meta = response.meta,
});
}
The javascript functions on the View are as follows:
editor = new $.fn.dataTable.Editor({
ajax: {
url: '@Url.Action("IncoTerms", "MDM")'
},
table: "#dt_load",
fields: [
{
label: "Code:",
name: "Code"
},
{
label: "Name:",
name: "Name"
}
]
});
and
var table = $('#dt_load').DataTable({
"ajax": {
"url": '@Url.Action("IncoTerms", "MDM")',
"type": "POST",
"datatype": "json"
},
dom: "<'row'<'col'i><'col'p><'col mb-0 pt-2'l><'col pt-1 'B><'col pt-2 'f>>",
pagingType: "full",
serverSide: true,
"language": {
"lengthMenu": "Items Per Page _MENU_",
info: "Showing _START_ to _END_ of _TOTAL_ entries",
"paginate": {
"next": '>',
"previous": '<',
"first": "|<",
"last": ">|"
}
},
order: [[2, 'asc']],
responsive: true,
//paging: true,
initComplete: function () {
// Apply the search
this.api().columns().every(function () {
var that = this;
$('input', this.footer()).on('keyup change', delay(function (e) {
if (that.search() !== this.value) {
that
.search(this.value)
.draw();
}
}, 1500));
});
},
columns: [
{
data: '',
defaultContent: '',
searchable: false,
orderable: false
},
{
data: '',
defaultContent: '<span class="custom-edit-icon"><i class="fa fa-edit"></i></span>',
className: 'row-edit dt-center',
orderable: false,
searchable: false
},
{ data: "Code" },
{ data: "Name" },
{
data: '',
defaultContent: '<i class="fa fa-trash"/>',
className: 'row-remove dt-center',
orderable: false,
searchable: false
}
],
select: {
style: 'os',
selector: 'td:first-child'
},
buttons: {
dom: {
button: {
className: ''
}
},
buttons: [{
extend: "createInline",
text: '<i class="fa fa-plus"></i>',
editor: editor,
formOptions: {
submitTrigger: 1,
submitHtml: '<i class="fa fa-play"/>'
},
className: "btn btn-dhl-red"
},
{
extend: 'collection', text: '<i class="fa fa-download"></i>', className: "btn btn-dhl-yellow", buttons: [
{ extend: 'copy', text: 'Copy', className: 'btn btn-dhl-red coll' },
{ extend: 'csv', text: 'Csv', className: 'btn btn-dhl-red coll' },
{ extend: 'excel', text: 'Excel', className: 'btn btn-dhl-red coll' },
{ extend: 'pdf', text: 'Pdf', className: 'btn btn-dhl-red coll' }]
}
]
}
});
- I have tried using the ".DbType(System.Data.DbType.String)" option on the field in the Controller Action to no avail.
- I have tried to set the field data type in both the "dataTable.Editor" and ".DataTable" sections but I don't think you can affect the data type when using Server Side processing.
- I have tried to find a similar example in the forums but could not find this issue.
This appears to be to do with the way that Data Tables "guesses" the data types of the field (orthogonal data types), it thinks the field is numerical when you type a number(int), but the base database data type is a string(NVARCHAR).
I am not sure if this is down to the .Net framework version of DataTables or the fact that I am using an older version of the DataTables js script files (I have tried the newer versions and it did not fix my issue), I have no other ideas. Help please!
Answers
I finally fixed this issue with the help of the following:
https://datatables.net/forums/discussion/59700
The issue was with "Validation.Unique" in the controller action, it try's to guess the data type unless it is specified like the following:
Validation.Unique<string>