Link to home
Start Free TrialLog in
Avatar of Able22
Able22

asked on

Switching ADO Connection from SQL SERVER to Oracle

Hi,

I am having trouble getting data returned from my ADO connection.

My ADO connection in VB 6 looks like this:
  conn.Open 'OracleDC', 'test', 'test'

Note: 'OracleDC' is an ODBC connection I've set up using the Oracle 10g driver.

The connection actually opens and sql statements can be executed on the connection however, no data is being returned when it should.  

I noticed some things in my connection object which look odd to me but that could be because I'm not that familiiar with setting up ADO connections.

Provider=MSDASQL.1;Password=test;User ID=test;Data Source=OracleDC;Extended Properties="DSN=OracleDC;UID=test;PWD=test;DBQ=TESTDB;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"

Is it correct that it's using the Provider=MSDASQL provider?  If not, I don't know how to change this since i'm not setting it

Please let me know if I need to provide more information.

Thanks.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

MSDASQL.1 is the driver to connect to SQLServer.  Try changing it to: MSDAORA.1

That is the Microsoft data provider for Oracle.  I would recommend using an Oracle driver.  I can't suggest one without knowing more about the app.

Check out:
http://www.orafaq.com/faqmsvb.htm#CONNECT
Avatar of Able22

ASKER

The user has to set up an ODBC then that is used to make the connection.

For whatever reason, MSDASQL.1 is being set as the provider and I don't know how to get the provider to be MSDAORA.1

The ODBC is defined using the Oracle 10g driver
this should answer you concerns about the MSDAORA Data Provider for ORACLE:

.NET Framework Data Provider for Oracle For Oracle data sources.

The .NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later.
Note   The .NET Framework Data Provider for Oracle is not included in the .NET Framework version 1.0. If you require the .NET Framework Data Provider for Oracle and are using the .NET Framework version 1.0, you can download the .NET Framework Data Provider for Oracle at http://msdn.microsoft.com/downloads.

AW
 
by the way, this statement:

"The user has to set up an ODBC then that is used to make the connection.

For whatever reason, MSDASQL.1 is being set as the provider..."

is nonsense.  You should NOT be using the SQL Server Data Provider when attempting to get data from an ORACLE Database.

AW
Avatar of Able22

ASKER

I'm not sure why you wrote the last comment because that is my question.  This is new to me so I am not sure how to make this any clearer

conn.Open "OracleDC, test, test"  - this line is what is set in VB 6.0 code to establish the ADO connection.  Where 'OracleDC' is the defined ODBC

This is what I see the above Connection string resolved to when I interrogate the ADO connection object at runtime:

Provider=MSDASQL.1;Password=test;User ID=test;Data Source=OracleDC;Extended Properties="DSN=OracleDC;UID=test;PWD=test;DBQ=TESTDB;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"

So, I am trying to get help understanding why this is happening when I've used an Oracle driver to establish my ODBC

I think I'm starting to get it.  The DSN you created says that it's using the Oracle ODBC driver but it is somehow resolving to the SQDASQL driver?  If that isn't it, I'm still confused.

I suggest you delete and recreate the DSN from scratch and test the connection outside the app.  Please post back results.
Why not confirm your connection string with this website, pretty useful site, got for lots of DBs

http://www.connectionstrings.com/

ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial