SQL Question - OleDB from C# Application

[wfs]

Hi All,
I'm tring to perform various queries against an Access 2003 database from a C# app via OleDB. The 'standard' SQL syntax doesn't seem to work ?

For instance if I have a table;

First_Name
-------------
JOHN
John


SELECT *
FROM T_TEST
where FIRST_NAME like 'Jo%'

- returns no rows

SELECT *
FROM T_TEST
where FIRST_NAME like 'Jo*'

- returns both rows. It doesn't seem to to care about the differences between upper and lower case.

any idea what the syntax should be.

Thanks

Bill

 

[MSAccessRookie]

Hi All,
I'm tring to perform various queries against an Access 2003 database from a C# app via OleDB. The 'standard' SQL syntax doesn't seem to work ?

For instance if I have a table;

First_Name
-------------
JOHN
John


SELECT *
FROM T_TEST
where FIRST_NAME like 'Jo%'

- returns no rows

SELECT *
FROM T_TEST
where FIRST_NAME like 'Jo*'

- returns both rows. It doesn't seem to to care about the differences between upper and lower case.

any idea what the syntax should be.

Thanks

Bill

I believe that the first code block returns no rows because the % is being interpreted as a search for a single character.

SELECT * 
FROM T_TEST
where FIRST_NAME like 'Jo%'

It will probably succeed if the table contains words like "JOB", "JOE", "JOG", or "JOY".

As for the second issue, I seem to remember Oracle applications having similar problems reading the parameter line and text files and returning the values as Uppercase only, but cannot say if this is related to your issue. I do not recall how (if at all) the issue was resolved.

 

[wfs]

it appears that Access 2003 is intrinsically case insensitive (?) - so I converted everything to upper case

(that was the intended plan anyway - I just hadn't bothered to get round to it up until that point...)

SELECT *
FROM T_TEST
where FIRST_NAME like 'Jo%'

this works from an OleDb or odbc connection - just not from a direct ms access query. (again case insensitive)

- I'll add a task to reformat the names etc back to 'proper case' for display purposes. But this can wait a few days...

Thanks All