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

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.

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.

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

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

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.

This is probably far too late, but wanted to add this for the record - I had a similar problem and solved it by clicking on the h file in question, look in the 'properties'window on the right and set the 'File Type' to 'Form' (somehow it had been reset to 'h file'