Conversion failed when converting the varchar value to data type int

mnemeth

I'm writing an .Net Core web app using controllers.

I have a controller that looks like this:

   public class HearingTypesController : Controller
    {
        [HttpGet]
        [HttpPost]
        public ActionResult HearingTypes(ICalendarRepository repository, ILogger<HearingTypesController> logger)
        {
            using (var db = repository.GetDatabase())
            {
                var response = new Editor(db, "HearingTypes", "HearingId")
                    .Model<HearingType>()
                    .Field(new Field("HearingId")
                        .GetFormatter((val, data) => Convert.ToString(val)
                        )
                        .SetFormatter((val, data) => Convert.ToString(val)
                        )
                        .Validator(Validation.NotEmpty())
                        .Validator(Validation.Unique())
                    )
                    .Field(new Field("Hearing")
                        .GetFormatter((val, data) => Convert.ToString(val)
                        )
                        .SetFormatter((val, data) => Convert.ToString(val)
                        )
                        .Validator(Validation.NotEmpty())
                    )
                    .TryCatch(false)
                    .Process(Request)
                    .Data();
                return Json(response);
            }
        }
    }

The DataTable loads correctly.

However, if I attempt to edit the table and change one of the HearingIds to just an integer value, I get this error message:

In the above example error, I'm attempting to change the "166C" HearingId to just "166". The " None" is the first option for a HearingId.

This is what the HearingType class looks like:

    [PrimaryKey(nameof(HearingId))]
    public class HearingType
    {
        [Column(TypeName = "varchar(15)")]
        public string HearingId { get; set; } = string.Empty;
        public string Hearing { get; set; } = string.Empty;
    }

The HearingTypes table javascript looks like this:

//function to load the HearingTypes table
try {
    $(document).ready(function () {
        //load the editor with the HearingType fields
        hearingEditor = new $.fn.dataTable.Editor({
            ajax: "/api/HearingTypes",
            table: "#HearingTypes",
            fields: [{
                label: "HearingId:",
                name: "HearingId"
            },
            {
                label: "Hearing:",
                name: "Hearing"
            }]
        });
        // Activate an inline edit on click of an HearingTypes table cell
        $('#HearingTypes').on('click', 'tbody td:not(:first-child)', function (e) {
            hearingEditor.inline(this);
        });
        //load the datatable with the HearingType fields
        $('#HearingTypes').DataTable({
            dom: "Bfrtip",
            ajax: "/api/HearingTypes",
            order: [[1, 'asc']],
            columns: [
                {
                    data: null,
                    defaultContent: '',
                    className: 'select-checkbox',
                    orderable: false
                },
                { data: "HearingId" },
                { data: "Hearing" }
            ],
            select: {
                style: 'os',
                selector: 'td:first-child'
            },
            buttons: [
                { extend: "create", editor: hearingEditor },
                { extend: "edit", editor: hearingEditor },
                { extend: "remove", editor: hearingEditor }
            ]
        });
    });
} catch (error) {
    // display the HearingTypes error message on the page
    $('#error-message-hearing').text(error.message);
}

mnemeth

I've also tried adding a type definition, but I still get the same conversion error.

            { data: "HearingId", type: "string" },
            { data: "Hearing", type: "string" }

allan

Hi,

I don't have an immediate answer for you with this, but a few questions that I hope can help us get this resolved.

In the above example error, I'm attempting to change the "166C" HearingId to just "166". The " None" is the first option for a HearingId.

I'm a bit confused as to way "None" would be in the error message if you are editing HearingId "166C". I wouldn't have expected "None" to be submitted for such an edit. Can you show me the POST data that is sent to the server please?

I'm wondering if it is worth adding a call to the DbType() method for each field to make sure that it knows to treat them as a string:

.DbType(DbType.String)

Thanks,
Allan

mnemeth

When I submit the form while inspecting in the browser, the browser debugger pauses and highlights this code. No idea if this is related or not, but I thought I'd include it.

I'm still getting the SQL conversion error on the " None" option, but it does not appear in the payload.

This is the unparsed payload:

data%5Brow_166C%5D%5BHearingId%5D=166&data%5Brow_166C%5D%5BHearing%5D=166-Criminal&action=edit

Also, this bug is repeatable in other tables using other controllers in my project. When I try to change a data field that has a string character in it to one that is just made up of numbers, it fails.

mnemeth

Adding .DbType(DbType.String) to each .Field in the controller did not solve the issue.

This is what the code to instantiate the database looks like:

        public Database GetDatabase()
        {
            var db = new Database("sqlserver", _ctx.Database.GetDbConnection().ConnectionString);
            return db;
        }

In my Program.cs file, this is the code that establishes the context and DBProviderFactories.

DbProviderFactories.RegisterFactory("System.Data.SqlClient", SqlClientFactory.Instance);

builder.Configuration.AddUserSecrets<CalendarContext>();
builder.Services.AddDbContext<CalendarContext>(cfg =>
{
    cfg.UseSqlServer(builder.Configuration.GetConnectionString("CalendarDb"));
});

mnemeth

If you're interested, I could give you access to a private Git repository where you could pull the project down to test it yourself. The project automatically seeds its own database with test data, so you'd just need to run the "dotnet ef database update" on the project to build the database and then run the project.

mnemeth

Very interesting update. Pretty sure I figured out where the problem is coming from. It has to deal with how Editor manages primary key columns.

You can see in my original post, the HearingTypes entity has a custom primary key of HearingId which is a varchar(15) data type, while the Hearing column defaults to nvarchar.

[PrimaryKey(nameof(HearingId))]
public class HearingType
{
    [Column(TypeName = "varchar(15)")]
    public string HearingId { get; set; } = string.Empty;
    public string Hearing { get; set; } = string.Empty;
}

I've found that I can change Hearing column back and forth from text to numbers with no issue at all, but if I try it in the primary key column, that's when Editor pukes.

In all my other tables that don't have a custom primary key, I can flip back and forth between letters and numbers no problem. It's just the custom primary key fields that seem to have an issue.

This is an issue I can work around.

allan

Thanks for your investigation here! I've just tried it locally with this database table:

IF OBJECT_ID('hearing', 'U') IS NOT NULL
  DROP TABLE hearing;

CREATE TABLE hearing (
    HearingId nvarchar(255) NOT NULL default '',
    Hearing nvarchar(255) NOT NULL default '',
    PRIMARY KEY (HearingId)
);

I set up by controller with:

            using (var db = new Database(dbType, dbConnection))
            {
                var response = new Editor(db, "hearing", "HearingId")
                    .Field(new Field("HearingId")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("Hearing"))
                    .TryCatch(false)
                    .Process(Request)
                    .Data();

                return Json(response);
            }

I haven't had any problems converting from mix to just a number on the HearingId property.

What version of the DataTables dll are you using in your project? I'll drop you a PM with my github details as well as access to the project might help resolve this, unless you can spot something I've missed in the above.

Allan

mnemeth

Try changing the HearingId column to a varchar(15) instead of an nvarchar, see if that breaks it.

I'm using the 2.1.1.1 dll.

I just realized that it would be a big pain for you to debug my project because I've already put in a bunch of security features that require authorization from the identity platform that's tied to my employer.

allan

No dice unfortunately. Using:

CREATE TABLE hearing (
    HearingId varchar(15) NOT NULL default '',
    Hearing varchar(15) NOT NULL default '',
    PRIMARY KEY (HearingId)
);

I can change 116C to 116 in the primary key column without any error:

I totally agree - its got to be something to do with the primary key. Do you use any triggers on the db table?

Allan

mnemeth

hmmm... I noticed that I'm also setting the order. Try the varchar and include the ordering in the datatable load:

dom: "Bfrtip",
order: [[1, 'asc']],

It was blowing up on " None" which is the first one in an ordered list, so perhaps the ordering of a key field might play a role.

I've already corrected my project to account for the issue, so I can't test or replicate the error any longer.

I'm not using any triggers.