locked
Incorrect Date Value '' when no date is selected RRS feed

  • Question

  • User750344513 posted

    I have created a form with a date field, MeetingDate, that works perfectly if a date is chosen.

    What I am trying to achieve is that the user can either enter the Article, or enter Doc, MeetingDate and Committee.

    Here is my code:

    var Article = "";
    var Doc = "";
    var MeetingDate = "2020-12-31";
    var Committee = "";
    
    if(IsPost && Validation.IsValid()){
            if(!Request.QueryString["Article"].IsEmpty()){
                Article = Request.Form["Article"];  
               }
            else {
                Doc = Request.Form["Doc"];
                Committee = Request.Form["Committee"];
                MeetingDate = Request.Form["MeetingDate"];
                Article = Request.Form["Committee"] + " Meeting " + Request.Form["Doc"] + " " +  Request.Form["MeetingDate"];
                
            }

    Here is my html for the date entry:

     <p><label for="MeetingDate">Meeting Date:</label>
             <input type="date" name="MeetingDate" value="@Request.Form["MeetingDate"]" />   </p>

    If I just enter an Article, and do not choose a date with the datepicker, I get this compile error:

    MySql.Data.MySqlClient.MySqlException: Incorrect date value: '' for column 'MeetingDate' at row 1

    I do not know why the value getting passed for MeetingDate is ''.  

    I am trying to send it "2020-12-31"

    Can anyone help me fix my code?

    Thank you!

    Thursday, June 18, 2015 3:46 PM

Answers

  • User281315223 posted

    Could you post the code that you are using when you are inserting this Date into your MySQL database?

    Most .NET providers for databases will allow you to simply pass in a DateTime object that corresponds to your specific DateTime as opposed to a string :

    using(var mySqlCommand = new MySqlCommand("query", mySqlConnection))
    {
         // Add your date as a DateTime parameter
         mySqlCommand.Parameters.AddWithValue("@MeetingDate",DateTime.ParseExact(MeetingDate,"yyyy-MM-dd",null));
    
         // Execute your command here
         mySqlCommand.ExecuteNonQuery();
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 18, 2015 4:12 PM

All replies

  • User281315223 posted

    Could you post the code that you are using when you are inserting this Date into your MySQL database?

    Most .NET providers for databases will allow you to simply pass in a DateTime object that corresponds to your specific DateTime as opposed to a string :

    using(var mySqlCommand = new MySqlCommand("query", mySqlConnection))
    {
         // Add your date as a DateTime parameter
         mySqlCommand.Parameters.AddWithValue("@MeetingDate",DateTime.ParseExact(MeetingDate,"yyyy-MM-dd",null));
    
         // Execute your command here
         mySqlCommand.ExecuteNonQuery();
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 18, 2015 4:12 PM
  • User750344513 posted

    The command to send the information to the database is this:

    var db = Database.Open("intranet");
    var insertCommand = "INSERT INTO JobBoard(Position, Library, wage, PartFull, AppDate, Newsletter, NewsletterDate, FileName, FileContent, MimeType, Committee, MeetingDate, Article) VALUES(@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12)";
        db.Execute(insertCommand, Position, Library, wage, PartFull, AppDate, News, NewsDate, fileName, fileContent, fileMime, Committee, MeetingDate, Article);

    In the MySQL database the field type for MeetingDate is date.

    Friday, June 19, 2015 8:58 AM
  • User281315223 posted

    If your MeetingDate parameter is a string, try sending in a DateTime object instead as highlighted below :

    db.Execute(insertCommand, Position, Library, wage, PartFull, AppDate, News, NewsDate, fileName, fileContent, fileMime, Committee, DateTime.ParseExact(MeetingDate,"yyyy-MM-dd",null), Article);
    Friday, June 19, 2015 11:44 AM
  • User750344513 posted

    Thank you for helping me with this pesky problem.

    I tried your suggestion of replacing MeetingDate with Date.Time.ParseExact(MeetingDate, "yyyy-mm-dd", null).  

    Now my error has changed to this message:

     System.FormatException: String was not recognized as a valid DateTime.

    Any other thoughts on how I can fix this?

    Sunday, June 21, 2015 1:41 PM
  • User281315223 posted

    I tried your suggestion of replacing MeetingDate with Date.Time.ParseExact(MeetingDate, "yyyy-mm-dd", null).  

    You need to make sure to use capitalized 'M' characters to denote months as 'm' refers to minutes.

    Sunday, June 21, 2015 3:36 PM
  • User750344513 posted

    That was just a typo in my reply to your message.  I did use yyyy-MM-dd in my actual code.

    I decided I have been spending way too much time trying to make this work, so I split the webpage into 2 separate pages.  One where you enter an Article title, and the other where you select Committee, Doc and MeetingDate.  

    So, if you don't have a quick way to make my first webpage behave, I will just use the 2 new pages instead.

    Thank you!

    Sunday, June 21, 2015 4:29 PM
  • User281315223 posted

    Could you post an example of exactly what your MeetingDate variable looks like? The only way that the parsing will work properly is if it is a string in the format "yyyy-MM-dd" so if we saw the string, it might make it a bit easier to have things working as expected.

    Monday, June 22, 2015 8:32 AM