Database Command String 1

[lothos12345]

have a Visual Basic.Net application and for the data interface I am using Access I am feeding it a command string as follows:

VarTemp = "SELECT IFIELD1, IFIELD2, IFIELD3, IFIELD4, IMAGELIST, IDNUM FROM INDEXDB1 WHERE (IFIELD1 LIKE 'BN*') OR (IFIELD1 LIKE 'BR*') OR (IFIELD1 LIKE 'ES*') OR (IFIELD1 LIKE 'IG*') OR (IFIELD1 LIKE 'WZ*')"


This always returns nothing however when I put this same string in an Access query it returns what I expect it to return.

Also from the application when I feed it the sql command:

VarTemp = "SELECT IFIELD1, IFIELD2, IFIELD3, IFIELD4, IMAGELIST, IDNUM FROM INDEXDB1"

It works as expected too. The issue I have is when I use the where clause and only when I use it from the program. There is something wrong with how I am feeding the where clause from the program though I am not sure what. Any help offered is greatly appreciated.

 

[gmmastros]

Use percent ( % ) instead of asterik ( * ).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Qik3Coder]

gmmastros, are you going on the assumption that the class that does the connectivity between .Net and the Access program works like SQL and not like Acess?

Access: *
SQL: %


lothos, did you try it with just one of the parameters, like "...WHERE(IFIELD1 LIKE 'BN*') '

to see if that is returning anything? you may need to either:
1. put parenthesis around the whole where statement
or
2.

VarTemp = "SELECT IFIELD1, IFIELD2, IFIELD3, IFIELD4, IMAGELIST, IDNUM 
FROM INDEXDB1 
WHERE Left(IFIELD1,2) in ('BN', 'BR', 'ES', 'IG', 'WZ')"

just my thoughts

SWM Programmer Seeking 3d Modeling work. Either for long term commitment or just friendship. Likes long hours in front of a flat screen with heavy doses of Mountain Dew.

 

[gmmastros]

Qik3Coder,

ADO prefers percent even with an Access database. I've seen this same question on multiple forums from multiple users.

I didn't notice anything wrong with the query, so the percent vs. asterik problem is the likely culprit. Also, considering that it is a VERY easy suggestion to try, it made the most sense.

Of course, if I am wrong, I'm sure lothos12345 will let us know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Qik3Coder]

Me: the class that does the connectivity between .Net and the Access program works like SQL

I wasn't trying to contradict you, I was asking. I haven't used ADO much and was asking for reference.





SWM Programmer Seeking 3d Modeling work. Either for long term commitment or just friendship. Likes long hours in front of a flat screen with heavy doses of Mountain Dew.

 

[gmmastros]

Understood. And I apologize if I mis-interpreted your tone.

Here's the reference you were looking for:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0601.asp

The pattern-matching characters we looked at last month were provided through DAO. Rather than using the asterisk (*) and question mark (?) symbols as wildcards, ADO requires that you use the percent sign (%) to match multiple characters and the underscore (_) to match a single character.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Qik3Coder]

hehe, was asking for "personal reference" or rather "just so i know", was not asking for a link...

Thank you though

SWM Programmer Seeking 3d Modeling work. Either for long term commitment or just friendship. Likes long hours in front of a flat screen with heavy doses of Mountain Dew.