Date Format validator doesn't work proper

Markus Evacoso

Hi,

I used the generator and created two entries
valid from
valid until
with the German date format "dd.MM.yy".

It works fine for the online preview.

Starting the project leads to the following situation, http://prntscr.com/hvjjxp , where it is possible to store a date like 01.01.18, but not 10.01.18.

The POST request includes

data[0][gueltig_von] 01.01.18
data[0][gueltig_bis] 10.01.18

The result is

{  
   "draw":null,
   "data":[  

   ],
   "recordsTotal":null,
   "recordsFiltered":null,
   "error":null,
   "fieldErrors":[  
      {  
         "name":"gueltig_bis",
         "status":"Input not valid"
      }
   ],
   "id":null,
   "meta":{  

   },
   "options":{  

   },
   "files":{  

   },
   "upload":{  
      "id":null
   },
   "debugSql":[  

   ],
   "cancelled":[  

   ]
}

debugSql is empty, also I set

.Debug(true)

in the Controller.cs

The validation in the Controller.cs is

                    .Field(new Field("gueltig_von")
                            .Validator(Validation.DateFormat("dd.MM.yy"))
                            .GetFormatter(Format.DateSqlToFormat("dd.MM.yy"))
                            .SetFormatter(Format.DateFormatToSql("dd.MM.yy"))
                    )
                    .Field(new Field("gueltig_bis")
                            .Validator(Validation.DateFormat("dd.MM.yy"))
                            .GetFormatter(Format.DateSqlToFormat("dd.MM.yy"))
                            .SetFormatter(Format.DateFormatToSql("dd.MM.yy"))
                    )

The type of both dates are "date" in the MS SQL SERVER.

I suspect that there is an error in the validation for the German date format.

All the best,
Markus

allan

I've just tried it with the following configuration:

It appears to work correctly.

That could be related to your other thread about the 1.7.0 dll which Generator is now using.

Regards,
Allan

Markus Evacoso

Thank you Allen for your quick reply.
The thing is: It is working, if you are using the generator and the result online. It worked also for me, bevor you updated the dll to 1.7.0.

But it is not working, when you generate the code via generator, download it and use it in a Visual Studio project. I'm using MS SQL Server 2014 and the table is generated by the sql script, which is included in the ZIP file.

I still get the error http://prntscr.com/hvjjxp or, as you can see that I tried it again: http://prntscr.com/hvl80a
Here is a screenshot of the database structure, which was generated by the sql script http://prntscr.com/hvl8nm

    CREATE TABLE tbl_Bonusvereinbarungen (
        [PK_Bonusvereinbarung] int not null identity,
        [fk_bereich] numeric(9,2),
        [fk_bonusart] numeric(9,2),
        [fk_res_id] numeric(9,2),
        [beschreibung] nvarchar(max),
        [gueltig_von] date,
        [gueltig_bis] date,
        [created] datetime,
        [changed] datetime,
        PRIMARY KEY( [PK_Bonusvereinbarung] )
    );

I'm really confused, as I have never seen this behaviour before!

Best,
Markus

allan

The live one uses a PHP backend since it its a PHP server that this site is running on.

I was actually using a downloaded package on my Windows machine with the configuration shown rather than the online one. I must confess I'm confused as well as I've not need it before and I've not been able to reproduce it.

Are you able to send me a screenshot of your Generator configuration so I can try using exactly what you've got. Did you modify any of the files?

Allan

Markus Evacoso

Of course!

The thing is... actually I thought, the problem could be the different behaviour of the English and German Date format YY-MM-DD and and so a day between the 13th up to 30th/31th could be mixed up with the number of month. And so a number bigger than 12 could cause the problem.

But then I noticed, that the problem occurs also with the 10th of a month. So it must be a two digits problem.
I though, I could get the SQL Query string, through the .Debug (true) parameter in the Controller.cs but the entry in the POST request result is still empty.

So... maybe the problem is the Validator?

.Field(new Field("gueltig_von")
                            .Validator(Validation.DateFormat("dd.MM.yy")) //Could this cause the problem?
                            .GetFormatter(Format.DateSqlToFormat("dd.MM.yy"))
                            .SetFormatter(Format.DateFormatToSql("dd.MM.yy"))

The SQL Server instance is a 2014 one and the language of the database is English.

Thank you very much, Allen. If I can provide any other information to find the solution for this bug (?), I'm willing to do it.

I didnt change any file, but the web.config to access the database.

Best,
Markus

allan

Super - thanks! I've just tried setting up a local copy based on your Generator configuration using the following:

Unfortunately, I've not been able to recreate the issue:

That seems to accept the 01.01.18 and 10.01.18 dates without any issues for me. This was downloaded via Generator today (4th Jan) and uses the 1.7.0 dll without any modifications to the package (only to add the SQL connection information).

I'm not sure what I'm missing here. Are you able to send me a dropbox or similar link to the package you are using so I can take a look directly?

Allan

Markus Evacoso

Thank you very much, Allan.

This is the generated code, I'm using: https://upload.evaco.de/public/20f047
As I said, the only thing I changed, is the SQL information.

This are the properites of the database:

All the best,
Markus

allan

Thanks! I've got to confess, I'm at a bit of a loss with this one. I've just downloaded your file and opened it up in VS, added my sqlserver details and it works with the following data:

Can you see anything different in the data I put in compared to the data you are using?

Thanks,
Allan

Markus Evacoso

Hi Allen,

I still think that it is a validation problem ... I will continue with examinating the issue, as ... as I said, it is generated by the Code Generator and used with a usuall MS SQL Server...

I will also try to find another SQL Server instance and check if the problem will occurs.

Thank you so far Allen.

All the best,
Markus

allan

I doubt that its an SQL issue since the validation happens before that point, and as you noted the Debug(true) option didn't show anything (it basically just shows the SQL statements that were run).

The only thing I can think of is that the "culture" on your machine is set differently from mine. However, this is the code that Editor uses for the DateFormat validator:

DateTime.ParseExact(str, format, System.Globalization.CultureInfo.InvariantCulture);

What is your computer's internationalisation options set to?

Allan