DAO.3464 Datatype mismatch in Criteria Expression

[Stefan Bolleininger]

Hi,

i'm stuck with a little SQL query, which i can use within a Repository in an mySQL database but not on an local stored EAP-file.  

i know ther is some differences between both ways, but i cannot get my wrong implementation within this query for an EAP-file.

to declare the Dateformat i use:

string datestart2 = date1.ToString("yyyy-MM-dd");

which gives me the correct way for SQL-servers.

The query:

SELECT CreatedDate FROM t_object WHERE Object_Type='Requirement' AND CreatedDate BETWEEN '"+ datestart2 +"' AND '"+ dateend2 +"'

Can anyone help me with that?

Greetings!

[qwerty]

Code: [Select]

SELECT CreatedDate FROM t_object WHERE Object_Type='Requirement' AND CreatedDate > #2012-01-01#  AND CreatedDate < #2012-01-31#
q.

[Stefan Bolleininger]

Thank you for your fast response.

Afterwards i remembered about an Article in geerts blog with #WC# but I can't get both ways EAP and DBMS to work correctly.

With '-Tags i get trough my sql database
with # i can get through the local EAP.

But no solution for both ways. - Is there something i need to prepare on the database?

Thanks for your help!

[qwerty]

The #-tag is something MS Access requires to denote date values (just google for "ms access date sql"). If you use above in Repository.Execute this will work (or just paste it in the SQL window of the advanced find). But in another context EA might mis-interprete the #-tag.

q.

[Geert Bellekens]

The #WC# can only be used when going through the EA search engine (via the GUI).
I think there's also some kind of shortcut for server specific parts of the query, but I'm not sure how that worked again.
And i'm even less sure that will work through Repository.SQLQuery.
You can however get the database type from the repository and based on that execute the correct sql.

Geert

[Stefan Bolleininger]

hi,

okay it may be a bit dirty but here is my idea about it:

Code: [Select]

string eapfile = Repository.ConnectionString.ToLower();
if (eapfile.Contains(".eap"))
{                 
"SELECT CreatedDate FROM t_object WHERE Object_Type='Requirement' AND CreatedDate > #" + usedate + "# AND CreatedDate < #" + nextday + "#"
}
else
{
 "SELECT CreatedDate FROM t_object WHERE Object_Type='Requirement' AND CreatedDate > '" + usedate + "' AND CreatedDate < '" + nextday + "'"
}
The SQL Syntax is not correct in this snippet but may bring up some ideas for you.

This decides between EAP-files ( local repository) or others(may be all kind of database)

[Geert Bellekens]

But what then with .eap shortcut files (that only contain the connectionstring to the database)?

Geert

[Stefan Bolleininger]

Good question,

i have no idea which connectionstring will we given by the repository.

What would be your suggestion?

[qwerty]

Just try it out and see what ConnectionString you get in case of shortcut.

BTW: your case above is the only way to do it. You have to create individual SQLs for each DB variant. That sucks...

q.

[Geert Bellekens]

I think I once did it by checking the size of the eap file. There's a significant difference between an (empty) mdb eap file and a simple shortcut eap file.

Geert