Editor: Conversion from nvarchar to int??

kmbonin

Started using MS Sql Server instead of MySQL and am now having this issue when I try to save a new record:

Conversion failed when converting the nvarchar value 'displayOrder' to data type int.

I see there is a similar thread here but there is no resolution: https://datatables.net/forums/discussion/34989/edit-record

The database defines displayOrder in this table as an int.

Here is my model:

public class AlertDataModel : EditorModel
    {
        
        public string alertName { get; set; }
        public string display_YN { get; set; }
        public string alertText { get; set; }
        public string alertDateAdded { get; set; }
        public string alertAddedBy { get; set; }
        public int displayOrder { get; set; }
    }

And here is the controller:

using (var db = new Database(settings.DbType, settings.DbConnection))
            {                
                var response = new Editor(db, "logan_alerts", "alertID")
                    .Model<AlertDataModel>()
                    .Field(new Field("logan_alerts.alertName"))
                    .Field(new Field("logan_alerts.alertText")
                        .Xss(false))
                    .Field(new Field("logan_alerts.display_YN"))
                    .Field(new Field("logan_alerts.displayOrder")
                        //.Validator(Validation.Numeric())
                        )
                   .Field(new Field("logan_alerts.addedBy as alertAddedBy")
                        .Options("logan_user", "userID", "fullName")
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_alerts.dateAdded as alertDateAdded")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy"))
                        .SetValue(sysDate))
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_alerts.addedBy");

                response.PreCreate += (sender, e) => {
                    response.Db()
                    .Query("update", "logan_alerts")
                    .Set("displayOrder", "displayOrder + 1", false)
                    .Where("displayOrder", "displayOrder", ">=")
                    .Exec();
                };
                response.PostRemove += (sender, e) => {
                    response.Db()
                    .Query("update", "logan_alerts")
                    .Set("displayOrder", "displayOrder - 1", false)
                    .Where("displayOrder", e.Values["displayOrder"], ">=")
                    .Exec();
                };

                return Json(response.Process(request).Data());
            }

allan

Just to confirm - is it Editor 1.5.6 that you are using? Also what value are you submitting for the displayOrder value?

I notice the numeric validation is disabled - if you enable that, what happens?

Thanks,
Allan

kmbonin

Not entirely sure which version I am using. I downloaded my package from the Editor Generator download widget that you built (which, by the way, is fabulous), so it would be whichever version of Editor that comes with. Under my DataTables folder there is a folder for Editor and it says 1.5.6, so I'm going to assume that's it, but I'm actually referencing the JS from the Resources folder:

<script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/ju-1.11.4/jqc-1.12.3/moment-2.13.0/ed-quill-0.20.1/dt-1.10.12/b-1.2.2/ef-quill-1.5.6/r-2.1.0/se-1.2.0/datatables.min.js"></script>
    <script type="text/javascript" charset="utf-8" src="Resources/js/dataTables.editor.js"></script>
    <script type="text/javascript" charset="utf-8" src="Resources/js/editor.jqueryui.min.js"></script> 

The value I am trying to save on create is 1, and it's always 1 (logic being, for this "alert" screen, the newest alert should always default to 1). The rest of the table is reordered after submit. Adding back in the validation does not seem to make any difference in the functionality, as I still get the error.

allan

That's really odd! Can you show me your database schema for that table? Is the displayOrder column an integer?

Thanks,
Allan

kmbonin

Yes, column DisplayOrder is an int.

The table is very simple:
alertID int
alertText varchar 1000
DateAdded date
AddedBy int FK
DisplayOrder int
Display_YN varchar 1

kmbonin

Anything on this one? It's only occurring on create, not on edit.

allan

I'm afraid I don't understand why this is happening. With this example in .NET I use:

    public class SequenceModel
    {
        public string title { get; set; }
        public string author { get; set; }
        public int duration { get; set; }
        public int readingOrder { get; set; }
    }

and the SQL is:

CREATE TABLE audiobooks (
    id int not null identity,
    title varchar(250) NOT NULL,
    author varchar(250) NOT NULL,
    duration int NOT NULL,
    readingOrder int NOT NULL,
    PRIMARY KEY (id)
);

There are two integer columns there and they appear to work without any issue.

Do you have any triggers on the database table?

Allan

kmbonin

Nope, no triggers.

kmbonin

The issue was with this line I commented out:

response.PreCreate += (sender, e) => {
                    response.Db()
                    .Query("update", "logan_alerts")
                    .Set("rowDisplayOrder", "rowDisplayOrder + 1", false)
                    //.Where("rowDisplayOrder", e.Values["rowDisplayOrder"], ">=")
                    .Exec();                    
                };

Something in the database (SQL Server) has an issue with e.Values, only on create. Editing and deleting are not an issue. To get around this, I defaulted the new displayOrder on create to 1. Therefore, this block of code bumps everything up one number.

allan

Ah!

Try casting the value as an integer:

(int)e.Values["rowDisplayOrder"]

Regards,
Allan