Why did I get an invalid data format, when entering a date? RRS feed

  • Question

  • I'm using LightSwitch for the first time, because I've got to put together an application quickly. I'm also using VS 2013. I routinely design by first designing the database, which I did. (A SQL Server database, very simple, just a half dozen tables.) Then I went into VS 2013, fired up a new LightSwitch app, connected it to the new database, and created the screens. It seemed to be working well. But when I actually tried to enter some data into one of the pages (I chose the HTML version) it gave me an error on the date field saying that I had entered data in the wrong format. How is that even possible? It choose the datepicker control, which is very appropriate because the SQL data type is date (not datetime). So, how is this even possible? Why did the LightSwitch screen designer, which used a datepicker control for adding new records, fail to enter the data in the correct format???


    Saturday, November 8, 2014 11:42 PM


All replies

  • OK, I just ran a SQL Profile trace while my new LightSwitch application was running, in order to see what it was trying to insert into the table. I now see that it was trying to enter the date (5/31/2014, USA format) as '2014-5-31 00:00:00', which is really a datetime data type.

    So this begs the question, how to I modify the INSERT string used by LightSwitch in order to make certain that it will put in the data in the correct format? Or does one have to just use datetime values everywhere and select either the datepicker contrl or the datetimepicker control, based upon whatever it is that they want to store?


    Saturday, November 8, 2014 11:57 PM
  • Hi,

    You could format the lightswitch application datetime to fit the sql date type.


    You can also check this https://connect.microsoft.com/VisualStudio/feedback/details/717693/problem-in-lightswitch-with-concurrency

    • Marked as answer by Angie Xu Monday, November 17, 2014 5:21 AM
    Monday, November 10, 2014 3:27 AM
  • Hi Avatarku,

    Thank you for responding to my question. And thank you for the links! I've gone to the first link that you gave, but am concerned about something I saw further down that page. It's in a note that's close to the bottom of the page. There it said:

    "The Date Picker, Date Time Picker, Date Time Viewer and Date Viewer Controls don't respect the Format Pattern property."

    So, it looks to me as though regardless of what numeric formatting I might use or specify in the format pattern, it will be ignored.

    (BTW, in checking what sort of data type that is used in the .lsml file of the Data Sources folder, I saw that it properly identified the data type as Date, not DateTime.)

    Anyway, I'm thinking that what I've got to do is go back to the SQL Server table and changed that data type of that field/column to DateTime. It just looks to me as though LightSwitch doesn't want to 'play nice' with SQL Server, when handing the data to it. That I really should just use what SQL Server is expecting. Do you concur? Assuming that your answer to that last question is yes, then can I make the change to the data type in the SQL Server table and leave it as is in LightSwitch?


    Tuesday, November 11, 2014 1:22 AM
  • change sql data type of that field to DateTime could solve the issue.

    if you really want to change this issue. submit this to microsoft.


    • Marked as answer by Angie Xu Monday, November 17, 2014 5:21 AM
    Tuesday, November 11, 2014 1:44 AM