How to persist checked state to bit field

rdm

In my scenario, I have a bit field in my SQL Server table and a .NET MVC 5 view.

I used Always Shown Checkbox to get started. While I see a checkbox and I'm able to toggle it on and off, I have these symptoms:

• Any fields marked as true do not appear checked on page load.
• Toggling a checkbox _does _trigger the controller action, but the browser console shows "id :null".
• Verifying on the database, the bit field was not toggled.

What do I need to do in this scenario in order to correctly display the bit value (true = checked, false = unchecked) and change the bit value for that record by clicking the checkbox?

Controller

    [AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
    public ActionResult EditorTable(string campus)
    {
        var settings = Properties.Settings.Default;
        var formData = HttpContext.Request.Form;

        Debug.WriteLine("Function called"); // it's called when I click the checkbox.

        using (var db = new Database(settings.DbType, settings.DbAttendanceConnection))
        {
            var response = new Editor(db, "ReconcileSummaryDaysAbsentAndReturned", "Id")
                .Model<ReconcileSummaryDaysAbsentAndReturned>()
                .Field(new Field("BatchDate"))
                .Field(new Field("Reconciled"))    // this is the boolean field
                /*  .. more fields */
                .Where("Campus",campus)
                .Process(formData)
                .Data();

            return Json(response, JsonRequestBehavior.AllowGet);
        }
    }

jQuery Block:

  var editor;
$(() => {
    editor = new $.fn.dataTable.Editor({
        ajax: "@Url.Action("EditorTable")",
        table: "#example",
        fields: [
            { label: "BatchDate", name: "BatchDate" },
            {
                label: "Reconciled", name: "Reconciled",
                type:      "checkbox",
                separator: "|",
                options:   [
                    { label: '', value: 1 }
                ]
            }
                           /* ... more fields */ 
        ]
    });


    var dataTableConfig = {
        dom: "Blfrti",
        ajax: {
            url: "@Url.Action("EditorTable")",
            data: {
                campus: "@Model.Campus"
            },
            type: "POST"
        },
        serverSide: true,
        deferRender: true,
        scrollCollapse: true,
        order: [[1, 'asc']],
        columns:
        [
            {
                data: "BatchDate",
                render: function (data, type, full) {
                    var dtStart = new Date(parseInt(data.substr(6)));
                    return dtStart.toLocaleDateString();
                }
            },
            {
                data: "Reconciled", render: function (data, type, row) {
                    if (type === 'display') {
                        return '<input type="checkbox" class="editor-active">';
                    }
                    return data;
                },
                className: "dt-body-center"
            }
                           /* ... more fields*/
        ],
        rowCallback: function ( row, data ) {
            $('input.editor-active', row).prop( 'checked', data.active === 1 );
        }
    };

    $('#example').DataTable(dataTableConfig);


    $('#example').on('change', 'input.editor-active', function () {
        console.log($(this).closest('tr'));
        editor.edit( $(this).closest('tr'), false )
            .set( 'Reconciled', $(this).prop( 'checked' ) ?1:0)
            .submit();
    } );

});

kthorngren

Your code looks like it should work except for one thing:

        rowCallback: function ( row, data ) {
            $('input.editor-active', row).prop( 'checked', data.active === 1 );
        }

Instead of data.active I think you should have data.Reconciled. See this thread if you want 0 returned for unchecked checkboxes.

Kevin

rdm

@kthorngren -- I made the suggested change, but it did not resolve the problem. I also saw this in the browser console. "Conversion failed when converting date and/or time from character string."

kthorngren

That sounds like an error with you BatchDate field?

The other thing you might want to do is to use == instead of === in this statement:
$('input.editor-active', row).prop( 'checked', data. Reconciled == 1 );

This way the comparison will work whether the returned value is the digit 1 or a string "1".

Kevin

rdm

@kthorngren

You're right about the BatchDate having a problem. For some reason I can't understand, all my dates are converted to JSON data format: data[row_10][BatchDate]:/Date(1517900400000)/

allan

What's its data type in your model? If its Date or DateTime then that is probably the issue. Try setting it to be a string.

Allan

rdm

The setting is date. If I set it to string, it would cause unwanted to consequences to all the t-sql scripts depending on that table.

allan

That's your SQL schema. I was referring to the C# model - ReconcileSummaryDaysAbsentAndReturned in the above. What does that contain?

Allan

rdm

The C# model is a mirror of the SQL schema.

namespace RoseDashboard.Areas.AttendanceAuditSy1718.Models
{
    using System;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table("ReconcileSummaryDaysAbsentAndReturned")]
    public class ReconcileSummaryDaysAbsentAndReturned
    {
        public int Id { get; set; }

        [Column(TypeName = "date")]
        [DisplayFormat(DataFormatString = "{0:d}")]
        [Display(Name = "Batch Date")]
        public DateTime BatchDate { get; set; }

        public bool Reconciled { get; set; }

        [Required]
        [StringLength(4)]
        public string Campus { get; set; }

        [Required]
        [StringLength(50)]
        [Display(Name = "Student Name")]
        public string StudentName { get; set; }

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

        [Column(TypeName = "date")]
        [DisplayFormat(DataFormatString = "{0:d}")]
        [Display(Name = "Entry Date")]
        public DateTime EntryDate { get; set; }

        [Column(TypeName = "date")]
        [DisplayFormat(DataFormatString = "{0:d}")]
        [Display(Name = "Exit Date")]
        public DateTime? ExitDate { get; set; }

        [Column(TypeName = "date")]
        [DisplayFormat(DataFormatString = "{0:d}")]
        [Display(Name = "Date Attended")]
        public DateTime DateAttended { get; set; }

        [Display(Name = "Days Absent")]
        public int DaysAbsent { get; set; }

        [StringLength(50)]
        [Display(Name = "Duration of Absence")]
        public string DurationOfAbsence { get; set; }

        [Display(Name = "Total Chronic Health Records in School Year")]
        public int TotalChronicHealthRecordsInSchoolYear { get; set; }

        [Display(Name = "Total Days Suspended in School Year")]
        public int? SbmsTotalDaysSuspendedInSchoolYear { get; set; }
    }
}

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

If you change the DateTimes to be strings, does it then work? The problem with DateTime is that is can't directly be represented in JSON. There is some discussion on that in the manual here.

Allan

rdm

While I can do that, I would also have to change my SQL field definition as well. I use Entity Framework.

I've addressed the problem by doing this:

 .Field(new Field("BatchDate").SetFormatter((val, data) => JsonConvert.DeserializeObject<DateTime>(WrapStringInQuotes(val.ToString()))))

 private static string WrapStringInQuotes(string input)
    {
        return @"""" + input + @"""";
    }

allan

Ah! Yes, EF is going to be a problem there. Good to hear you have a solution. The other option would have been to have a copy of the modal that would have been used for Editor alone.

Allan