1

I have this problem :

i have a small management program witch c# and DB Access 2016. My keyboard and my system language is in Italian . My DB Access is in English

In query I send a System.DateTime.Today.ToString("dd/MM/yyyy") date format that is for example 

string update = "UPDATE RICHIESTA_IT SET RICHIESTA_IT.stato_approvazione = YES, RICHIESTA_IT.data_approvazione = #" + System.DateTime.Today.ToString("dd/MM/yyyy") + "# WHERE(((RICHIESTA_IT.ID_RichiestaIT) = " + iD_Richiesta + "))";
MessageBox.Show(update);
//qa is my object that help me to connection,query,disconnet to DB ..
qa.runNonQuery(update);

// without parametric query
// #06/04/2016#

but in access db I see 

04/06/2016

This is a big problem when i read this date.

How to resolve it ?

I tried to set a mask .. but I have failed to resolve...

Gustav
  • 53,498
  • 7
  • 29
  • 55
rul3z
  • 173
  • 1
  • 16
  • Are you trying to embed that string as a date literal in an INSERT or UPDATE statement? – Gord Thompson Apr 06 '16 at 16:46
  • No, because I think that i have an exception when I use INSERT statement. Type-safe different – rul3z Apr 06 '16 at 18:15
  • What is the date format in the regional settings on your computer? By default Access should use that. – SunKnight0 Apr 06 '16 at 20:12
  • In control panel i have setting italian language ad keyboard. What and where is regional setting?! – rul3z Apr 06 '16 at 22:34
  • Please [edit] your question to show us the segment of your code where you are attempting to use this `System.DateTime.Today.ToString("dd/MM/yyyy")` value. – Gord Thompson Apr 06 '16 at 23:05
  • edit done. I m sure that i send a correct date format ("dd/MM/yyyy") because I show in msgbox ... @GordThompson thanks for your trust :D :D .. I joke :) – rul3z Apr 07 '16 at 07:48
  • 1
    Control Panel > Region and Language - check the "Date and time formats" on the "Formats" tab. You will probably find that these are set wrong, so update them then close and restart MS Access. – Davy C Apr 07 '16 at 07:52
  • @DavyC thanks, date format was setting good ;) :) – rul3z Apr 07 '16 at 08:19

1 Answers1

3

The problem is that you are not using parameters.

You tried to guess the format internally used by MS Access and failed. If you use #...# date literals, they must use the yyyy-MM-dd or the MM/dd/yyyy format (I'd prefer the former, since it's unabiguous). This is how #...# date literals are defined. You cannot use a localized (e.g. Italian) format here.

To solve your immediate problem, you can use

... #" + System.DateTime.Today.ToString("yyyy-MM-dd") + "# ...

or

... #" + System.DateTime.Today.ToString(@"MM\/dd\/yyyy") + "# ...

But please, consider using parameterized queries. It's not hard and you avoid all those ugly date format/decimal point/string escaping problems.

Community
  • 1
  • 1
Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • Working good! Thanks! Why running good despite you set first _MM_ and after _dd_ ?? Infact with `(@"MM\/dd\/yyyy")` running good meantime `(@"dd\/MM\/yyyy")` set before month and after day .. Why ? PS: i know parametrized queries thanks. ;) – rul3z Apr 07 '16 at 08:12
  • @Heinzi: The _yyyy-mm-dd format_ is understood by any version of Access, not only "recent" versions. – Gustav Apr 07 '16 at 08:21
  • @rul3z: In cases like this, _always_ use the _yyyy-mm-dd_ (or mm-dd-yyyy) sequence as the _dd-mm-yyyy_ sequence will fail when _dd_ can be read as a month. – Gustav Apr 07 '16 at 08:26
  • @Gustav Thanks.. But i ask why `(@"MM\/dd\/yyyy")` working good and why `(@"dd\/MM\/yyyy") ` not . I want write in db `27/04/2016` and not `04/27/2016` but if use `(@"MM\/dd\/yyyy")` access set `27/04/2016` meantime `(@"dd\/MM\/yyyy") ` access set `04/27/2016` .. It reversed! my ask is why .. Nothing what.. if I understand why I 'm not mistaken the most , if I ask as 'how' I don't learn :) – rul3z Apr 07 '16 at 08:42
  • 2
    You must distinguish between the date _value_ and the date _format_. The value is used in the code. To _display_ it for the user, you apply a format, typically that of your system settings. To _concatenate_ it into an SQL string, you must format the value as a string expression understood by SQL. This format is typically yyyy-mm-dd and never dd-mm-yyyy. To avoid this trouble, use parameters as already noted. – Gustav Apr 07 '16 at 08:51
  • @Gustav: I changed my answer. I *seem* to remember that this feature was not available in the very early versions (which required the VBA literal syntax #MM/dd/yyyy#), but I might be mistaken. Anyway, it works with Access 2000 (just tested it) and anything before that should be "old enough" not to matter (in addition, I don't have an older version available to prove my point). – Heinzi Apr 07 '16 at 09:54