SQL difference between Access and VB

[Guest]

Hi,

I Have an SQL Query which works OK with Access but returns 0 record with VB
..NET. It seems to me that this as something to me with the "like" syntax.

Can somebody help me ?

Thanks so much, here is the example


conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
conn.Open("base2002.mdb")
rs = New ADODB.Recordset

sqlq = "SELECT C_AFFECT.IDENTIFIANT,C_AFFECT.ID_FIL2 " _
& "FROM C_AFFECT LEFT JOIN FIBRE ON C_AFFECT.ID_FIL2=FIBRE.IDENTIFIANT " _
& "WHERE FIBRE.IDENTIFIANT is Null AND C_AFFECT.ID_FIL2 LIKE 'FIB*';"
rs.Open(sqlq, conn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockBatchOptimistic, 1)

 

[David C. Holley]

Got nailed on this one myself. Access uses the asterik * as the wildcard
while pretty much everything else uses the percent sign %

 

[Guest]

Much thanks for your answer but I do use asterisk. I tried % just to see but
it did't change anything, which was quite sure. The problem is that this SQL
command works with Access and do not word inside VB connected to an Access
database.

 

[Rick Brandt]

Much thanks for your answer but I do use asterisk. I tried % just to see but
it did't change anything, which was quite sure. The problem is that this SQL
command works with Access and do not word inside VB connected to an Access
database.

DAO uses the asterisk. ADO uses the percent sign. AFAIK that should have
worked for you.

 

[David C. Holley]

whats the error that you're getting?

Much thanks for your answer but I do use asterisk. I tried % just to see but
it did't change anything, which was quite sure. The problem is that this SQL
command works with Access and do not word inside VB connected to an Access
database.

:

 

[Guest]

Thanks to both of you, it's really a problem of "%" and "*". I can't
understand how wrong I was in my first test of the two wildcards. I should
have done the test again after David answer, and did it after Rick's one.
Much thanks again !

 

[David C. Holley]

You're welcome and you'll find that you'll never again make that
mistake. (Speaking from experience)