Hyphenated numeric string saving as a negative number

Jorm

I've inherited a database with a table that has a numeric primary key and a more... user friendly?... hyphenated numeric string (column datatype varchar). The latter is generally in the format of something like '418013-'.

In Editor, this value is getting saved with the hyphen prefixing the numeric portion of the string like so: '-418013'.

I'm guessing this value is being interpreted as a number and I need to explicitly type the value as a string, but I'm not sure where/how to do that. I've tried telling Editor not to bother saving that value at all (i.e. to not do a full row update, only changed values update), but the issue persists (I'm guessing because this hyphenated number value is being interpreted as changed somewhere early on?).

Many thanks in advance for help on this.

allan

Have a look at the Network panel in your browser and inspect the Ajax request that is used for the edit. Specifically look at the data that is being submitted to the server from the form (this is in the "Headers" section). That will show what Editor is sending - can you let me know what it is? That will narrow the problem down a bit.

With a hyphen at the end of a number DataTables and Editor should just be treating it as a regular number. Are you able to give me a link to the page showing the issue?

Allan

Jorm

Hi Allan,

As an example, the 'Form Data' information from the Network->Headers panel for a record with the original value of '90194-' is showing as this:

data[row_777][Projects][PrjNumber]: 90194-

On pressing the update button, without having made any changes to the record whatsoever, the refreshed DataTable presents the value as this: '-90194', and the field's value has also updated to '-90194' in the database.

Also, looks like this happens in any text field taking user input: if I enter the 'string' 123456-, it returns as '-123456', but if I enter '123456- abc', it returns the same as '123456- abc'. Tested this way with an editor instance having a field typed as 'text area' (e.g. fields: [{type: "textarea" ....).

I'm managing other applications (C#, Access) that save these same data to our database, and have not encountered this issue with those, but could this be something un-associated with Editor I wonder?

Unfortunately, I am unable to link you to an example.

-jorm

allan

data[row_777][Projects][PrjNumber]: 90194-

If that is what is being submitted to the server, then I don't believe that it is a client-side issue. Rather there is something odd going on in the server-side libraries. Are you using the Editor .NET libraries? Can you show me the code that you are using if so?

It would be worth adding .Debug( true ) immediately before the .Process( ... ) call. That will show SQL debugging information in the JSON return from the server after an edit. Can you show me that return?

Thanks,
Allan

Jorm

Hi Allan,

Here's info from implementation of Debug(true), showing what I believe is the relevant bit (that the binding value is transformed to a prefixed-hyphen number - hopefully this is what you were looking for):

debug: [{Query: "SELECT * FROM [Projects] WHERE [Projects].[PrjDBid] = @where_0 ",…}, {,…}, {,…}]

Bindings: [{Name: "@PrjNumber", Value: -199999, Type: null},…]
0: {Name: "@PrjNumber", Value: -199999, Type: null}
Name: "@PrjNumber"
Type: null
Value: -199999

Then from debug there's a subsequent 'Update' statement:

Query: "UPDATE  [Projects] SET  [PrjNumber] = @PrjNumber, [PrjName] = @PrjName,....

I am using the following libraries from Editor 1.7.4 (in addition to DataTables 1.10.16 libraries):

editor.dataTables.min.css
dataTables.editor.js

The code for the problem column is pretty straightforward:

//ApiController
 
        .Field(new Field('Projects.PrjNumber'))

//Editor instance

        var editor = new $.fn.dataTable.Editor({
                           
                             ajax: '/api/projects'
                           , table: '#ActiveProjects'
                           , template: '#customForm'
                           , fields: [
                                  {
                                     label: 'Project Number:',
                                     name: 'Projects.PrjNumber'
                                   }


//DataTable instance

         var table = $('#ActiveProjects').DataTable({
                             
                               repsonsive: true
                             , dom: 'Bfrtip'
                             , ajax: {
                                          url: '/api/projects'
                                          , type: 'POST'
                                          }
                              , stateSave: true
                              , { data: 'Projects.PrjNumber' }

//Editor custom template

           <editor-field name='Projects.PrjNumber'></editor-field>

allan

I've just been digging around with this and I believe the issue is caused by the type conversion from when the data comes in from the client-side. The type conversion function is:

        private static object _HttpConv(string dataIn)
        {
            // Boolean
            if (dataIn == "true")
            {
                return true;
            }
            if (dataIn == "false")
            {
                return false;
            }

            // Numeric looking data, but with leading zero
            if (dataIn.IndexOf('0') == 0 && dataIn.Length > 1)
            {
                return dataIn;
            }

            try
            {
                return Convert.ToInt32(dataIn);
            }
            catch (Exception) { }

            try
            {
                return Convert.ToDecimal(dataIn);
            }
            catch (Exception) { }

            return dataIn;
        }

The Convert.ToInt32() and Convert.ToDecimal() both take the "123456-" input as a negative number! Hence why it is changed.

To fix, in the "Numeric looking data" part above change the condition to be:

if (dataIn.Length > 1 && (dataIn.IndexOf('0') == 0 || dataIn.IndexOf('-') == dataIn.Length-1  )

Thanks,
Allan

Jorm

Allan,

That fixed it!
Many thanks, as always,

-jorm