DSN-less Oracle Connection String

[Nicodemas]

I've been searching various sites and have come upon various solutions to connect to Oracle from ASP. But, unfortunately none of them work and all i am left behind is frustration. I have provided all the versions of drivers used, various connection strings used, and the various steps taken to connect.

Environment: Windows 2000/IIS5
Oracle 9i Client: 9.2.0.1.0
Oracle 9i ODBC Driver: 9.2.0.4.0
Oracle Provider for OLE DB: 9.2.0.2.0
Microsoft ODBC for Oracle (MSORCL32.DLL): 2.573.9001.0
MDAC: 2.8

1. Changed system environment variables to "C:\Winnt\Temp" and have given full rights access to IUSR_M/C and IWAM_M/C.
2. Given full access rights to to the users IUSR & IWAM to the Oracle Directory.
3. Path for Oracle Bin directory exists.
4. Created a DNS by the name "MyOracleData" in ODBC Data Sources.
5. Created an entry in TNS "MyOracleData".
6. Changing the ODBC DSN Name and TNS name didn't make a difference. So, kept them same for easier testing.
7. Created a Database project from Visual Interdev using the DSN created. I am able to see all the tables, SPs and all data.

Following are the list of connection strings used:
1. ConnectionString = "Driver={Microsoft ODBC for Oracle};DSN=MyOracleData;UID=MyUserID;PWD=MyPassword;"
2. ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=MyOracleData;UID=MyUserID;PWD=MyPassword;"
3. ConnectionString = "Driver={Microsoft OLE DB for Oracle};Data Source=MyOracleData;UID=MyUserID;PWD=MyPassword;"
4. ConnectionString = "DRIVER={Oracle ODBC Driver};DSN=MyOracleData;User ID=MyUserID;Password=MyPassword;"
5. ConnectionString = "Provider=msdaora;Data Source=MyOracleData.ode.state.oh.us;User ID=MyUserID;Password=MyPassword;"
6. ConnectionString = "Provider=MSDAORA;Data Source=MyOracleData;User ID=MyUserID;Password=MyPassword;"
7. ConnectionString = "Provider=MSDASQL;DSN=MyOracleData;UID=MyUserID;PWD=MyPassword;"
8. ConnectionString = "Provider=MSDASQL;DSN=MyOracleData;User ID=MyUserID;Password=MyPassword;"
9 .ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyOracleData;User ID=MyUserID;Password=MyPassword;PLSQLSet=1"
10.ConnectionString = "Provider=Provider=MSDAORA.1;Data Source=MyOracleData;UID=MyUserID;PWD=MyPassword;"

Following are the different errors i get:
1. [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
2. Specified driver could not be loaded due to system error 5 (Oracle in OraHome92)
3. [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
4. Provider cannot be found. It may not be properly installed
5. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.

[rdoekes]

Hi Rob,

I found two different types of OleDB connections. This one is from Microsoft:

oConn.Open "Provider=msdaora;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword"

and this one is from Oracle

oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword"

see: http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForOracleFromMicrosoft

Hope this helps,
-Rogier Doekes