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

Dennis Chan

When I save a new row with one column, which is nvarchar, to database, it is totally fine when there is any English character like "12345z" in the value.

However, when the value is pure numbers like "12345", there is an error "Conversion failed when converting the nvarchar value to data type int".

I captured the SQL script by profiler and the script was like:
exec sp_executesql N'SELECT Field as ''Field'' FROM Table WHERE Field= @where_0 ',N'@where_0 int',@where_0=12345

DataTables helped me to change the text field which is nvarchar to int automatically.

How can this problem be solved?

allan

Could you show me some of your code and your database schema for this table please? Is there only a single field in the table and that is the primary key, and you are writing to that column?

Allan

Dennis Chan

Database schema:
[Key, Required]
public String Field { get; set; }

Controller:

public IHttpActionResult TableApi()
{
       var request = HttpContext.Current.Request;
       using (var db = new DataTables.Database("sqlserver", ""))
       {
           Field myField = new Field("Field").Validator(Validation.NotEmpty()).Validator(Validation.Unique());
           var response = new Editor(db, "Table", "Field").Model<Table>().Field(myField).Process(request).Data();
           return Json(response);
       }
}

Javascript:

        $(document).ready(function() {
            var editor = new $.fn.dataTable.Editor( {
                ajax: 'TableApi',
                table: '#Table',
                fields: [
            {
                "label": "Field:",
                "name": "Field"
            },
                 ]
            } );
            var table = $('#Table').DataTable({
                dom: 'Bfrtip',
                ajax: 'TableApi',
                columns: [
            {
                "data": "Field"
            }
                ],
                select: true,
                lengthChange: false,
                buttons: [
                    { extend: 'create', editor: editor },
                    { extend: 'edit',   editor: editor },
                    { extend: 'remove', editor: editor }
                ]
            } );
        } );

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

allan

Thanks. I'll create a local test case based on that and get back to you.

Allan

allan

I've just tried this with the latest DataTables.dll that will be included in Editor 1.6.0 next week and haven't been able to reproduce the issue, although I have done some work in this area that might have address any problems related to it.

I've just sent a PM with an updated dll. Could you try that and let me know how you get on please?

Thanks,
Allan

Dennis Chan

The problem still exists after using the library u sent me.
I discovered that it is fine to enter "123" to other columns EXCEPT the key column.
The problem happens on key column only.

allan

Could you do a dump of your database and send that to me please so I can insert exactly what you are using into my test case? I tried it exactly as you have above yesterday with a single field called "Field" and a table called "Table" and wasn't able to reproduce the issue I'm afraid.

Thanks,
Allan

Dennis Chan

Finally I found out how to reproduce the error.

Initially, the table is empty.
If I insert the first record with "123", it is totally fine.

However, if I insert the first record with "abc" then insert another record with "123", the error occurs.
Error message: Conversion failed when converting the nvarchar value 'abc' to data type int.

allan

Thanks for your investigation here! I'll try to reproduce it with that information and get back to you.

Allan

Dennis Chan

Hi Allan,
Any update on this issue?

allan

Sorry - I've been waylaid with a few other things. I'll get back to you tomorrow about this one.

Allan

allan

I've just tried to reproduce this again, but I'm afraid again unsuccessfully.

For the SQL I used:

    CREATE TABLE Table1 (
        [Field] nchar(10) not null identity
        PRIMARY KEY( [Field] )
    );

And for the C# controller:

                var response = new Editor(db, "Table1", "Field")
                    .Field(new Field("Field")
                        .Validator(Validation.NotEmpty())
                    )
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }

I had no problem inserting first abc and then 123 (screenshot attached).

Are you able to tell me how to modify what I've tried in order to replicate the issue, or how you have your setup so I can duplicate that.

Thanks,
Allan