• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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.
0
Able22
Asked:
Able22
  • 3
  • 2
  • 2
  • +1
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
0
 
Able22Author Commented:
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
0
 
Arthur_WoodCommented:
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
 
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Arthur_WoodCommented:
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
0
 
Able22Author Commented:
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

0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
rockiroadsCommented:
Why not confirm your connection string with this website, pretty useful site, got for lots of DBs

http://www.connectionstrings.com/

0
 
Arthur_WoodCommented:
the DSN named ORACLEDC should be changed to use the MSDAORA Data Provider.  Open the DSN in the Control Panel>Administrative Tools>Data Sources (ODBC).  Select the OracleDC DSN, and then choose Configure.  There you can change the DataProvider that is currently assigned, to the Oracel DataProvider - it is currently assigned the SQL Server DataProvider - that is where your problem lies.

AW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now