Problem with accessing MS Access Date field with C++/CLI

[Dirkus Maximus]

I am talking to a MS Access database using C++/CLI. All is well untill I try to retrieve Date data: When the day is single digit, it refuses to see it (but doesn't throw an error) . My query goes:

String^ query = "SELECT * FROM MyTable WHERE (aNumberField = " + aValue +
"AND aDateField = #" + aDate + "#) ORDER BY aNumberField";

'aDate' in the above is a string in the format dd/mm/yyyy This format is also enforced in the Date field of the MS Access database. I also physically add a zero at the start of any one digit day field, so the string looks like "05/12/2010" instead of "5/12/2010". But that doesn't help. Remember that the above works fine with 2-digit day fields, i.e. a date like "15/12/2010" works fine.

[Luc Pattyn]

I would never do it that way. Unless you have set some CultureInfo stuff for the thread, it will use an implicit ToString() that applies the datetime formatting rules your user has set out through "Regional Settings". However user-defined settings should apply to the GUI only, not to formatting in files or databases. You really should fix the datetime formatting by explicit code, so I recommend something along these lines:

String^ query = "... #" + aDate.ToString("dd/MM/yyyy") + "#...";

:)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

[Dirkus Maximus]

Thanks Luc. Yes, I tried many solutions along that line, such as:

System::DateTime^ tUpdated = DateTime::Parse(myDateString, System::Globalization::CultureInfo::CreateSpecificCulture("en-AU")->DateTimeFormat);
String^ thisString = String::Concat("#",tUpdated->ToShortDateString(),"#"); // my 'MS Access' Date is a Short Date type

When debugging, the resulting string shows properly formatted. I'm starting to think maybe I need to rebuild the Access Database.:sigh:

modified on Monday, August 23, 2010 10:34 PM

[Luc Pattyn]

Dirkus Maximus wrote:

tUpdated->ToShortDateString

That does not make any sense. ToLongDate() and ToShortDate() adhere to your user's preferences, hence are not the right tool for database operations. I am not familiar with a "MS Access Short Date" type, I only know of "date/time" type, which matches perfectly with a DateTime in my experience. Here is a bit of C# code I have used a lot; one could do the same things in C++/CLI:

/// <summary>
/// Extracts the DateTime value from a date field. Returns DateTime.MinValue when empty.
/// </summary>
/// <param name="row"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public static DateTime GetDateTime(DataRow row, string fieldName) {
object field=row[fieldName];
if (field is DBNull) return DateTime.MinValue;
return (DateTime)field;
}

:)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

[Dirkus Maximus]

Thanks again Luc, that is good information. But I understand that, in the end, you can only query a database using a text string. I can narrow my problem down to the following: I do a query

"SELECT someDate FROM myTable WHERE someField = 34"

and I get a return from the DB I confirm, using a GetType(), that it is of type DateTime I do a 'ToString()' and that returns

06/07/2010 12:00:00 AM

Now I query the Database with

"SELECT * FROM MyTable WHERE someDate = #06/07/2010 12:00:00 AM#"

It doesn't return anything. While if do another query with a 2-digit day field, such as #16/07/2010 12:00:00 AM#, it works. *** Update *** The following work-around worked:

"SELECT someDate FROM myTable WHERE (someDate >= #6/07/2010#) AND (someDate <= #7/07/2010#)"

Any suggestions why this works and the previous one didn't?

modified on Tuesday, August 24, 2010 3:56 AM

[Luc Pattyn]

The proper way to handle dates and times would be to use SqlParameter, unfortunately that does not work for Access, so yes you need to convert DateTime values to string literals.

Dirkus Maximus wrote:

I do a 'ToString()' and that returns 06/07/2010 12:00:00 AM

You seem to still not understand the concept of regional settings; ToString() shows the data formatted like the user (you) said he wanted it, see the Control Panel ("Regional Settings" or "Dates and Times"); it is unrelated to the format your database requires. I found some more C# code that should be useful:

/// <summary>
/// Formats an immediate date value
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static string Date(DateTime date) {
return date.ToString("#yyyy-MM-dd#");
}

Do you see how it enforces a format independent of the user's Control Panel settings? (and different from what you think it should be). And I use it like this:

string query="... WHERE myDateField = "+Date(myDateTime)+"...";

:)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

[Dirkus Maximus]

Hi Luc

Luc Pattyn wrote:

ToString() shows the data formatted like the user (you) said he wanted it, see the Control Panel ("Regional Settings" or "Dates and Times")

Now I got you (little did I know). :-O Turned out I forgot that MS Access uses the JET engine. That's what happens when you leave a gap between researching what database to use and the actual implementation. At http://technet.microsoft.com/en-us/library/cc966377.aspx, I found the following:

Note that this date literal must always be expressed in MM/DD/YY order. To avoid the ambiguity of the meaning of stored queries, Microsoft Jet doesn't follow the international date format settings specified in the user's Control Panel.

So, given my example given earlier, all I needed to do was:

"SELECT * FROM MyTable WHERE someDate = #7/6/10#"

As I understand now, it would be better to work with DateTime types in the implementation, and convert them to properly formatted Strings in queries. Anyway, many thanks for all your effort.

[Luc Pattyn]

Dirkus Maximus wrote:

Microsoft Jet doesn't follow the international date format settings specified in the user's Control Panel.

That is correct, it holds true for each and every database; databases don't care about human users and their formatting preferences. After all, their data needs to be portable across the world.

Dirkus Maximus wrote:

Note that this date literal must always be expressed in MM/DD/YY order.

That is a surprise to me. I have always used yyyy-MM-dd and that has served me well, probably because it is unambiguous and easy to understand, so I suspect the MSDN statement is overly strict, in real life JET seems to accept more than one format. What remains is your now puzzling statement a date like "15/12/2010" works fine at the end of your very first message in this thread. It clearly follows dd/MM/yyyy which is yet another format, and yet you said it worked well. I don't have an official reference, however some pages (such as this[^] one) claim JET is quite forgiving, but when in doubt looks for m/d/y. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

[Luc Pattyn]

Hi Dirkus, I now have expanded a pre-existing article [^]of mine on DateTime, by adding the essentials of what we discussed today. Maybe it is of further interest to you. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

[Dirkus Maximus]

Great article Luc - very informative. I ended up having a DateTime representation of the Date instead of just a String.

// store date as DateTime object after splitting the string and converting to ints
DateTime^ dt = gcnew DateTime(yearField, monthField, dayField);

And when I need to insert it in the DB, I use the format you advocate:

// to insert into the DB, i use
String^ query = "INSERT INTO myTable (..., someDateField, ...) Values(..., dt->ToString("yyyy'/'MM'/'dd"), ..."

To retrieve it back, I use what the JET documentation says, i.e. I convert it into the MM/DD/YY form first.

array <String^>^ strArr = fromDate->Split('/');
int dayField = Convert::ToInt32(strArr[0]);
int monthField = Convert::ToInt32(strArr[1]);
String^ yearField = Convert::ToString(strArr[2]);
// the year field only takes the last 2 digits
yearField = yearField->Substring(2,2);
String^ formattedDate = String::Concat(" #",Convert::ToString(monthField),"/", Convert::ToString(dayField),"/", yearField,"# ");
query = "SELECT * FROM myTable WHERE someDate = " + formattedDate + ";

It's all a bit convoluted, but this method is working well now. I don't know why (as described in my first post) this 'one digit' day didn't work while the '2-digit' one did - though I did forget to mention that I'm using quite an early version of MS Access (2003), so this problem may be fixed by now. Just one other thing, from an MSDN forum I learned that
JET SQL supports different types. You can declare parameter type by using PARAMETERS clause. The OleDB provider in ADO.Net does not support named parameters.

[Luc Pattyn]

Dirkus Maximus wrote:

Great article Luc - very informative

Thanks.

Dirkus Maximus wrote:

It's all a bit convoluted

Yes it is. I don't understand why you would do inserts and selects differently. The simple dt->ToString("yyyy'/'MM'/'dd") or dt->ToString("yyyy-MM-dd") should work here too. And you could incorporate the # signs in the format string.

Dirkus Maximus wrote:

I don't know why (as described in my first post) this 'one digit' day didn't work while the '2-digit' one did

I have strong doubts your 2-digit dates did work correctly all the time. The way I see it, Access (or ADO.NET) tries to interpret your date literals as mm/dd/yy unless they are clearly something else (as with my yyyy-mm-dd). However as soon as the first number exceeds 12, they understand it can't be mm/dd/yy, so they look for dd/mm/yy and IMO that is why it seemed to work, but not for 1-digit days.

Dirkus Maximus wrote:

JET SQL supports different types. You can declare parameter type by using PARAMETERS clause. The OleDB provider in ADO.Net does not support named parameters.

AFAICR I've never seen anyone do Access without OLEDB. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

modified on Wednesday, August 25, 2010 10:52 PM

[Dirkus Maximus]

Luc Pattyn wrote:

I don't understand why you would do inserts and selects differently

Yes, I made things much too difficult. There's no need for splitting strings and all that. When I get a DataType back from the database and need to display it in text, I just go

String^ text = ((DateTime)datarow[4]).ToString("dd MMM yyyy")

And to put a text like the above into a query, I convert it back with

String^ query = "SELECT * FROM myTable WHERE myDate = #" + text + "#)";

Things become embarrasingly clear once you get to see the whole picture :-O

Luc Pattyn wrote:

I have strong doubts your 2-digit dates did work correctly all the time

That sounds plausible. Thanks Luc

modified on Thursday, August 26, 2010 11:05 PM