How to persist checked state to bit field

How to persist checked state to bit field

rdmrdm Posts: 192Questions: 54Answers: 4
edited February 2018 in Editor

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();
    } );

});

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • rdmrdm Posts: 192Questions: 54Answers: 4

    @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."

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    Answer ✓

    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

  • rdmrdm Posts: 192Questions: 54Answers: 4

    @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)/

  • allanallan Posts: 61,441Questions: 1Answers: 10,053 Site admin

    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

  • rdmrdm Posts: 192Questions: 54Answers: 4

    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.

  • allanallan Posts: 61,441Questions: 1Answers: 10,053 Site admin

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

    Allan

  • rdmrdm Posts: 192Questions: 54Answers: 4
    edited February 2018

    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.

  • allanallan Posts: 61,441Questions: 1Answers: 10,053 Site admin

    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

  • rdmrdm Posts: 192Questions: 54Answers: 4

    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 + @"""";
        }
    
  • allanallan Posts: 61,441Questions: 1Answers: 10,053 Site admin

    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

This discussion has been closed.