Input string is not in correct format

Question

User-797751191 posted

Hi

  I have below code . In txtPostingDate it is showing "14-07-2019"

if (!string.IsNullOrWhiteSpace(txtPostingDate.Text))
                {
                    PostingDate = DateTime.ParseExact(txtPostingDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture);
                    cmd0.Parameters.AddWithValue("@PostingDate", Convert.ToDateTime(PostingDate.ToString("yyyy-MM-dd")));
                }
                else
                {
                    cmd0.Parameters.AddWithValue("@PostingDate", "1753-01-01");
                }

<script>
$(function () {
$(".datepicker").datepicker(
{
dateFormat: 'dd-mm-yy',
maxDate: new Date
});
});
</script>

Thanks

Answer 1

User475983607 posted

The parameter method should be...

cmd0.Parameters.AddWithValue("@PostingDate", PostingDate);

You've asked this question several times now.   Why do you keep converting a string to a date, date to a string, and string to a date?  Convert the user's input to a DateTime and you're done.

I recommend reading the reference documentation it really helps...

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.addwithvalue?view=netframework-4.8

Also it seems you do not understand the difference between a date string and a DateTime type.

https://docs.microsoft.com/en-us/dotnet/api/system.datetime?view=netframework-4.8

Answer 2

User-797751191 posted

Hi mgebhard

  Is the below code correct . Can u pls guide what DataTime.ParseExact will do & how does this works

"dd-MM-yyyy", CultureInfo.InvariantCulture

var PostingDate = new DateTime(1753, 01, 01);
PostingDate = DateTime.ParseExact(txtPostingDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture);
                    cmd.Parameters.AddWithValue("@PostingDate", PostingDate);

Thanks

Answer 3

User475983607 posted

jsshivalik

Is the below code correct . Can u pls guide what DataTime.ParseExact will do & how does this works

"dd-MM-yyyy", CultureInfo.InvariantCulture

DateTime.ParseExact()  and CultureInfo.InvariantCulture work exactly as written in the reference documentation.  There is no need to copy and paste this information on the forum.

https://docs.microsoft.com/en-us/dotnet/api/system.datetime.parseexact?view=netframework-4.8

https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo.invariantculture?view=netframework-4.8

As suggested in your similar threads, use Datetime.TryParse() which does not throw an exception when the data format is unexpected.  This allows you to return an error message to the user if the date is not formatted correctly or cast the input string to a DateTime type which can be passed to SQL as a parameter. 

Also use standard ASP Validation controls to validate user input at the time the user makes the entry.  Validating user input is a fundamental concept and a programming problem that must be solved in any application that accepts user input. 

You need to keep in mind that HTTP (web sites) is a text based protocol.  It is up to you to write code that does not allow the user to enter invalid formatted data.  If your application expects a dd-MM-yyyy format then force the user to enter the date as such.

Lastly, when asking for community assistance, be specific.  The error message often tells us which line of code threw the exception.  Being that you change between posts, it is helpful to always provide the updated code and steps to reproduce the error. We can only see the content that you share.

Answer 4

User-797751191 posted

Hi mgebhard

  I have date in textbox (txtDate) = '10/07/2019' (dd-mm-yyyy). I am trying below code . I want to know if Date in txtDate <= 12 or > 12 , Will the below code work fine. 

Datetime dt = date.Parse(txtDate.text);

cmd.Parameters.AddWithValue("@PDate", dt);

Answer 5

User475983607 posted

Hi mgebhard

  I have date in textbox (txtDate) = '10/07/2019' (dd-mm-yyyy). I am trying below code . I want to know if Date in txtDate <= 12 or > 12 , Will the below code work fine. 

Datetime dt = date.Parse(txtDate.text);

cmd.Parameters.AddWithValue("@PDate", dt);

This is a trick question.  The result of Date.Parse() depends on the current culture as explained in your similar threads and the openly published documentation.

https://docs.microsoft.com/en-us/dotnet/api/system.datetime.parse?view=netframework-4.8

Answer 6

User-797751191 posted

Hi mgebhard

  What exactly i should write , so that it should work fine in both cases of Date. if 12 or > 12.  Current culture depends on client or server

Thanks

Answer 7

User475983607 posted

  What exactly i should write , so that it should work fine in both cases of Date. if 12 or > 12.  

I do not understand what you are asking.  Should we assume you are you asking how to accept different culture date formats; MM/dd/yyyy and dd/MM/yyyy?

Current culture depends on client or server

Only you know the answer to this question.  Is your application configured to use a specific culture?

It seems like you are NOT reading the reference documentation yet want someone to write code without knowing anything about the application requirements.

Answer 8

User-797751191 posted

Hi

  My requirement is that txtDate value should get saved in Sql Database . In Database i have Datetime field. User input date in format dd-mm-yy - '10-07'2019'

I want Whether date is 10-07-2019 or 20-07-2019 it should get saved in Sql database

Thanks

Answer 9

User753101303 posted

Usally you have a problem when above day 12 when using mistaknly a wrong culture (ie 12-07-2019 is mistakenly saved as December 7th when you wanted July 12 but it is unnoticed). Then 13-07-2019 fails as 13 is not a valid month.

If your site is intended for a single culture start by changing your web.config file so that you are using the correct culture without having to be explicit everywhere:

<configuration>
<system.web>
    <globalization
       culture="xx-yy"
       uiCulture="xx-yy"
    />
  </system.web>
</configuration>

Answer 10

User-797751191 posted

Hi Patrice

   Just guide me below is the code

<script>
            $(function () {
                $(".datepicker").datepicker(
                     {
                         dateFormat: 'dd-mm-yy',
                         maxDate: new Date
                     });
            });
        </script>

In textbox when user enters some date it shows like this 09-07-2019. Now i want to save this date in sql.
 Date is before 12th or after 12th in both cases it should work

I am trying like below , It it correct

DateTime PostingDate = DateTime.ParseExact(txtPostingDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture);
                    cmd.Parameters.AddWithValue("@PostingDate", Convert.ToDateTime(PostingDate.ToString("yyyy-MM-dd")));

If it is correct i want to know what CultureInfo.InvariantCulture does.

Secondly if i write below line like this will it not work

cmd.Parameters.AddWithValue("@PostingDate", PostingDate);

Thanks

Answer 11

User475983607 posted

If we use today's date, the date picker configuration you shared above formats the text input as 17-07-19.  Your ParseExact() method expects 17-07-2019.  The date formats do not "exactly" match.

jsshivalik

i want to know what CultureInfo.InvariantCulture does

This information is openly published at the following link.  I feel it is unnecessary to copy and paste the information but if doing so gets you to read the information...

https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo.invariantculture?view=netframework-4.8

Remarks

The invariant culture is culture-insensitive; it is associated with the English language but not with any country/region. You specify the invariant culture by name by using an empty string ("") in the call to a CultureInfo instantiation method. CultureInfo.InvariantCulture also retrieves an instance of the invariant culture. It can be used in almost any method in the System.Globalization namespace that requires a culture. The objects returned by properties such as CompareInfo, DateTimeFormat, and NumberFormat also reflect the string comparison and formatting conventions of the invariant culture.

Unlike culture-sensitive data, which is subject to change by user customization or by updates to the .NET Framework or the operating system, invariant culture data is stable over time and across installed cultures and cannot be customized by users. This makes the invariant culture particularly useful for operations that require culture-independent results, such as formatting and parsing operations that persist formatted data, or sorting and ordering operations that require that data be displayed in a fixed order regardless of culture.

jsshivalik

Secondly if i write below line like this will it not work

cmd.Parameters.AddWithValue("@PostingDate", PostingDate);

It should work as long as @PostingDate and PostingDate are DateTime types.  Again, this type of information is openly covered in the documentation.

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.addwithvalue?view=netframework-4.8

Remarks

AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value.

For SqlDbTypeXml enumeration values, you can use a string, an XML value, an XmlReaderderived type instance, or a SqlXml object.

Answer 12

User753101303 posted

Configure first the culture you want ("en-IN" ?) in your web.config file and then start with :

DateTime PostingDate = DateTime.Parse(txtPostingDate.Text); // uses the culture you configured in web.config, better than telling this everywhere
cmd.Parameters.AddWithValue("@PostingDate", PostingDate);

This code :
- works for any possible valid value allowed by the current culture (configured in the web.config file or programmatically defined for the current http request)
- it includes the yyyy-MM-dd format which should work for all cultures. This is the format used by date pickers and I expect this is what you actually have here even if the UI is showing dd-MM-yyyy
- fails if the string is empty (but it seems you are testing this case earlier)
- fails if the string is not empty but invalid for this culture (ie "abc" or "13/02/2019"  for "en-US") - iif it can happen use TryParse instead

Answer 13

User-797751191 posted

Hi Patrice

  Just guide if i do like below code will it work correctly whether the date is 12 or greater than 12 . ISince i have only 1 form i don't want to do anything in web.config

DateTime PostingDate = DateTime.ParseExact(txtPostingDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture);
                    cmd.Parameters.AddWithValue("@PostingDate", Convert.ToDateTime(PostingDate.ToString("yyyy-MM-dd")));

Thanks

Answer 14

User475983607 posted

As explained above, the code snippet will NOT work because you coded the data picker to send dd-MM-yy format whcih does not match the ParseExact() format of dd-MM-YYYY.

DateTime PostingDate = DateTime.ParseExact(txtPostingDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture);

Also explained several times now, this code does not make sense becasue you are taking a DateTime and converting it to a string then back to a DateTime.

cmd.Parameters.AddWithValue("@PostingDate", Convert.ToDateTime(PostingDate.ToString("yyyy-MM-dd")));

It is unclear why you continue use the same code expecting different results.