oledbprovider vs odbc provider ???

[serge calderara]

Dear all,

Does any one have any idea why an sql statment with INNER
JOIN syntax is working well with odbcprovider but not with
Oledbprovider when accessing an access 2000 database?

here is the code I have used with oledbprovider which fails

Dim monodbc As New
OleDb.OleDbConn ection 'Odbc.OdbcConne ction
monodbc.Connect ionString = ("Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet
OLEDB:Database Locking Mode=1;Data Source='D:\Nomo s Net
Suite\Runtime\C onfig\NetDBConf .mdb';Jet OLEDB:Engine
Type=5;Provider ='Microsoft.Jet .OLEDB.4.0';Jet OLEDB:System
database=;Jet OLEDB:SFP=False ;persist security
info=False;Exte nded Properties=;Mod e=Share Deny None;Jet
OLEDB:Encrypt Database=False; Jet OLEDB:Create System
Database=False; Jet OLEDB:Don't Copy Locale on
Compact=False;J et OLEDB:Compact Without Replica
Repair=False;Us er ID=Admin;Jet OLEDB:Global Bulk
Transactions=1" )

'"MaxBufferSize =2048;FIL=MS
Access;DSN=TRYM E;PageTimeout=5 ;UID=admin;DBQ= D:\Nomos Net
Suite\Runtime\C onfig\NetDBConf .mdb;DriverId=2 5"

monodbc.Open()
Dim momo As OleDb.OleDbData Adapter
momo = New OleDb.OleDbData Adapter
Dim sds As OleDb.OleDbComm and

sds = New OleDb.OleDbComm and(Query, monodbc)
momo.SelectComm and = sds
Return momo.SelectComm and.ExecuteRead er()

if I use exactly the same thing but with odbcprovider no
problem works ok.

regards
serge

[Carlos J. Quintero]

You have not posted the query text or the error message, but:

- The .NET Data Provider for OLE DB uses the Jet OLEDB Data Provider
- The .NET Data Provider for ODBC uses the Access ODBC Driver

The Access ODBC Driver and Jet OLEDB Data Provider are diferent components
that can parse differently the SQL statements (or to have its own bugs). A
known issue is that while the Access ODBC Driver accepts ODBC syntax (for
outer joins, for example), the Jet OLEDB Data Provider does not (other OLEDB
Providers accept ODBC syntax, though).

So, you could reduce your query text to a minimum and post it here for
examination of others...

Carlos Quintero

"serge calderara" <se************ *@maillefer.net > escribió en el mensaje
news:05******** *************** *****@phx.gbl.. .

Dear all,

Does any one have any idea why an sql statment with INNER
JOIN syntax is working well with odbcprovider but not with
Oledbprovider when accessing an access 2000 database?

here is the code I have used with oledbprovider which fails

Dim monodbc As New
OleDb.OleDbConn ection 'Odbc.OdbcConne ction
monodbc.Connect ionString = ("Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet
OLEDB:Database Locking Mode=1;Data Source='D:\Nomo s Net
Suite\Runtime\C onfig\NetDBConf .mdb';Jet OLEDB:Engine
Type=5;Provider ='Microsoft.Jet .OLEDB.4.0';Jet OLEDB:System
database=;Jet OLEDB:SFP=False ;persist security
info=False;Exte nded Properties=;Mod e=Share Deny None;Jet
OLEDB:Encrypt Database=False; Jet OLEDB:Create System
Database=False; Jet OLEDB:Don't Copy Locale on
Compact=False;J et OLEDB:Compact Without Replica
Repair=False;Us er ID=Admin;Jet OLEDB:Global Bulk
Transactions=1" )

'"MaxBufferSize =2048;FIL=MS
Access;DSN=TRYM E;PageTimeout=5 ;UID=admin;DBQ= D:\Nomos Net
Suite\Runtime\C onfig\NetDBConf .mdb;DriverId=2 5"

monodbc.Open()
Dim momo As OleDb.OleDbData Adapter
momo = New OleDb.OleDbData Adapter
Dim sds As OleDb.OleDbComm and

sds = New OleDb.OleDbComm and(Query, monodbc)
momo.SelectComm and = sds
Return momo.SelectComm and.ExecuteRead er()

if I use exactly the same thing but with odbcprovider no
problem works ok.

regards
serge

[calderara serge]

here is the text queery I am using and that I forgot to join here:

SELECT USER_PARAM.*, LANGUAGE.NAME
FROM USER_PARAM INNER JOIN [LANGUAGE] ON USER_PARAM.LANG UAGE_ID =
LANGUAGE.ID;

thnaks for your comments
regards
serge

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[calderara serge]

We have made some more testing on passing SQL synthax with inner join
statement and the only way we make it work was to use an odbcprovider.
No way to make it work with oledbprovider.

Do u have any idea if it is a know issue as we use to call it "bug".

thanks for your reply
regards
Serge
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Carlos J. Quintero]

I have tested it on my system and my results are:

1) If you use that query, it only works with the ODBC Driver. Using the
OLEDB Provider, it returns an error opening the recordset. This seems to be
your case.
2) If you use LANGUAGE2 as the name of the table instead of LANGUAGE, it
works fine with both providers.
3) If you use [LANGUAGE] in each occurrence of this word in the statement,
that is:

SELECT USER_PARAM.*, [LANGUAGE].NAME
FROM USER_PARAM INNER JOIN [LANGUAGE] ON USER_PARAM.LANG UAGE_ID =
[LANGUAGE].ID;

instead of your original statement (which misses 2 occurrences), it works
fine with both providers.

HTH,

Carlos Quintero

"calderara serge" <se************ *@maillefer.net > escribió en el mensaje
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..

here is the text queery I am using and that I forgot to join here:

SELECT USER_PARAM.*, LANGUAGE.NAME
FROM USER_PARAM INNER JOIN [LANGUAGE] ON USER_PARAM.LANG UAGE_ID =
LANGUAGE.ID;

thnaks for your comments
regards
serge

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[calderara serge]

Hello carlos,

thnaks so much it works ok now.
But I would like to clarify my mind.

Does it means that LANGUAGE is a reserved word somehow in .NEt and it
cannot be used as a table name?

Why if I just make a simple querry to LANGUAGE table only like SELECT *
FROM LANGUAGE it works ok?

thnaks for your answer
serge

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Paul Clement]

On Fri, 31 Oct 2003 05:12:55 -0800, calderara serge <se************ *@maillefer.net > wrote:

¤
¤ Hello carlos,
¤
¤ thnaks so much it works ok now.
¤ But I would like to clarify my mind.
¤
¤ Does it means that LANGUAGE is a reserved word somehow in .NEt and it
¤ cannot be used as a table name?
¤
¤ Why if I just make a simple querry to LANGUAGE table only like SELECT *
¤ FROM LANGUAGE it works ok?

Language is a reserved word in both Jet and ODBC, but apparently is being ignored in your ODBC
example:

http://support.microsoft.com/default...b;en-us;321266
http://msdn.microsoft.com/library/de...dappcpr_17.asp

You can use Language as a table name but as Carlos indicated it must be enclosed within brackets for
every occurrence in the SQL statement. It's typically best not to use reserved words to avoid these
types of problems.
Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)