Select 2 backend model and database storage type

Peter.prc48

Hi,
I'm using Select2 for a multi-select component but I'm not sure what the backend model and database storage type should be. At present, I'm using an nvarchar(255) for the database column and a string for the model property as I thought it's looking for a comma-separated string of integers to represent the multiple option ids. I get the select multi-select populated okay from the backend but when I try and save a record I'm getting an error. I can see in the data sent back, that the multi-select is not a single comma-separated string but rather two separate data elements. How are these processed in the backend? Do you need to join to a many-many table?

The error:

No mapping exists from object type System.Collections.Generic.Dictionary`2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Object, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type.

allan

I added a separator option to the select2 integration plug-in for exactly this sort of thing. You can add separator: ',' to get and set a list of comma separated values for the field.

Allan

Peter.prc48

Hi Allan,

Thanks for the answer. So now I get a comma-separated string returned from the front end but I still get the error above, which is generated in the Process() method. I'm sure its something simple I'm missing.

Here is my model:

public class ReportScheduleModel
    {

        public class ReportSchedule
        {
            public int Id { get; set; }
            public int Run { get; set; }
            public int ReportId { get; set; }
            public string UniqueName { get; set; }
            public int TimeRangeId { get; set; }
            public int ScheduleId { get; set; }
            public string Format { get; set; }
            public int CompanyId { get; set; }
            public string EmailTo { get; set; }
            public string DivisionIds { get; set; }
            public string StatusIds { get; set; }
            public string SalesRepIds { get; set; }
            public string ClientIds { get; set; }
            public string SalesStageIds { get; set; }
            public string SourceIds { get; set; }
            public string ServiceTypeIds { get; set; }
            public string EmployeeIds { get; set; }
        }

        public class Report
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }

        public class TimeRange
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }


        public class TimeSchedule
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
        
    }

And here is my Process() method:

using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, Table, "Id")
                    .Model<ReportScheduleModel>()
                    .Field(new Field("ReportSchedule.Run")
                        .Options(DdlCache.YnDropDown))
                    .Field(new Field("ReportSchedule.ReportId")
                        .Options(() => db
                        .Sql(reportNamesSql)
                        .FetchAll()))
                    .Field(new Field("ReportSchedule.TimeRangeId")
                        .Options(() => db
                        .Sql(timeRangeSql)
                        .FetchAll()))
                    .Field(new Field("ReportSchedule.ScheduleId")
                        .Options(() => db
                            .Sql(scheduleSql)
                            .FetchAll()))
                    .Field(new Field("ReportSchedule.DivisionIds")
                        .Options(DdlCache.DivisionsDdl))
                    .Field(new Field("ReportSchedule.Format")
                        .Options(DdlCache.ReportFormatsDdl))
                    .Field(new Field("ReportSchedule.StatusIds")
                        .Options(DdlCache.StatusDdl))
                    .Field(new Field("ReportSchedule.SalesRepIds")
                        .Options(DdlCache.SalesStaffDdl))
                    .Field(new Field("ReportSchedule.ClientIds")
                        .Options(DdlCache.ClientsDdl))
                    .Field(new Field("ReportSchedule.SalesStageIds")
                        .Options(DdlCache.SalesStageDdl))
                    .Field(new Field("ReportSchedule.SourceIds")
                        .Options(DdlCache.SourceDdl))
                    .Field(new Field("ReportSchedule.ServiceTypeIds")
                        .Options(DdlCache.ServiceTypesDdl))
                    .Field(new Field("ReportSchedule.EmployeeIds")
                        .Options(DdlCache.EmployeeDdl))
                    .LeftJoin("Report", "Report.Id", "=", "ReportSchedule.ReportId")
                    .LeftJoin("TimeRange", "TimeRange.Id", "=", "ReportSchedule.TimeRangeId")
                    .LeftJoin("TimeSchedule", "TimeSchedule.Id", "=", "ReportSchedule.ScheduleId")
                    .Where("ReportSchedule.CompanyId", companyId)
                    .Process(data)
                    .Data();

                return Json(response);
            }

allan

Does your version of the Select2 integration plug-in for Editor use the separator option? (just wondering if you might have an old version).

If you look at the data submitted to the server, is it a string of comma separated values, or an array?

Thanks,
Allan

Peter.prc48

I'm using the separator option now:

{
                label: "Division",
                name: "ReportSchedule.DivisionIds",
                type: "select2",
                separator: ",",
                opts: {
                    "allowClear": true,
                    "multiple": true,
                    "placeholder": {
                        "id": "",
                        "text": "Please select one or more"
                    }
                }
            },

and the data being submitted to the server is a string of comma separated values. It looks like I'm using version 4.0.2 of select2 according to this line from my editor.select2.js:
* @depjs //cdnjs.cloudflare.com/ajax/libs/select2/4.0.2/js/select2.min.js

allan

It'd be worth updating to the latest version of Select2.

However, what I was really meaning was does your dataTables.editor.js file make use of separator? Does it contain that text anywhere?

Allan

Peter.prc48

Finally solved it.
What I didn't realise was that I should include the javascript code which is on the Select2 plugin page. Once I had copied and pasted that into my javascript, I got the select2 list being populated with the selected options (prior to this it only showed the full list of options in the dropdown but not those already selected).
The next issue I faced was that I had set the separator as a "," and when the data was being saved in the database, the Process(data) call was stripping out the commas so "5,2" was being saved as "52". This meant that when the row was edited again, there were no selected options showing. I noticed somewhere that the default separator was a pipe character "|"so I changed my separator from a comma to a pipe and voila, select2 works like a charm.
Posting this in case it helps someone else out.