Connecting to an oracle database from visual basic 2005

Hi,
The best way to obtain connection string is
Drop data adapter and dataset on the Windows form.
Create a dataset by attaching dataadapter.  In the properties of dataset you should be able to view the connection string

Hope it helps

No go, same error. I tried several connect strings on that website as well as the connect string from the data adapter, and that error :

 Request for the permission of type 'System.Data.Odbc.OdbcPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed

is still there.

First Check the status of your database, whather it is on, and are you able to connect from sql-prompt.
second do you need to set some enviornment parameter like oracle_sid, oracle_home etc.
Are you working on same machine where db is present or some other.

I am working on a workstation that does not host the database.  One thing of note, My access to the database is read only.  I know I can connect to the database and it is running because

1) I can open the application that uses it
2) if the database could not accept connections half the office would be beating down my door
3) I can successfully create a connection under the data Connections in the server explorer

I have tried many different connect strings and all with the same error.
I thought the sid was set using the "data source = sid" which has already be tried.

Ok I made an entry in the system DSN as follows:

Data Source Name : cutacms
Description : <blank>
User Name: <user name>
Server: cutacms

I used Microsoft ODBC for Oracle driver

cutacms is defined in tnsnames.ora

The connection string setup I used is as follows:

Dim connection As Odbc.OdbcConnection
Dim sConnString As String = "Provider=OraOLEDB.Oracle;Data Source=cutacms;User Id=<user name>;Password=<password>;"
connection = New Odbc.OdbcConnection(sConnString)
connection.Open()

It is still giving the same error.

It certainly is possible to connect to Oracle databases from ODBC-based applications, like VB.  I'm no expert on VB, but I have created and used ODBC-based connections to various Oracle databases from Oracle8 - Oracle11.  I have used both the Microsoft ODBC driver for Oracle as well as the Oracle Client since I've found that some applications worked better with one than the other.

One possible problem may be that Oracle8 now is considered rather old, and maybe the ODBC client you are using is not compatible with Oracle 8.  Do you have an Oracle8 client that you can install?  If yes,  you will need to configure SQL*Net in the Oracle client before you can use the Oracle ODBC driver.  Use the wizard, and supply the host name (or IP address) the database name (or SID) and the port (1521 is the default port for Oracle).  Then create a System DNS using the Oracle ODBC driver that is part of the Oracle Client install.

Oops!  I meant a "System DSN", not a "SYSTEM DNS".

OK,
Here is another way of testing your DSN.
Steps
1.  Right Click on Desktop to create new text file.
2.  Rename it as tstdsn.udl.  You will see the icon is changed
3.  Double click on tstdsn.udl
4.  Data Link Properties PopUp will open.  Make sure you are on Connection Tab
5.  Select Use data source name radio button
6.  From the drop down select the name of your DSN i.e. cutacms
7.  Now, Enter information to log on to Server, i.e. Username and Password.  Remember these username and password are the same while you connect to DB from sql prompt
8.  Click on Test Connection

If your connection is not succesful. You have either not made ur DSN setup properly or you have to test the oracle connection from SQL Prompt

Hope this helps

I made the file, and the test was successful, so the problem has to be in my code somewhere. My hands are tied with using Oracle 8 unfortuantely and I cannot install anything else on my workstation as it may conflict with the current program we use to interface with the database.

Know what little I know about VB my question is now

1) am I using the right import statement
2) am I using the right provider in my connection string
3) is there anything else I could be missing?

Here is what I currently have after numerous tests and trials:

Imports System.Data.odbc

Dim connection As Odbc.OdbcConnection
        Dim sConnString As String = "Provider=OraOLEDB.Oracle;Data Source=cutacms;User Id=uid;Password=pwd;"
        connection = New Odbc.OdbcConnection(sConnString)
        connection.Open()

***UPDATE***
I got the security error fixed buy going into .net 2.0 and setting the all_code segment to full control, but I still having connection issues, now I am getting:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

As I mentioned before I can connect to the database by making a data connection in server explorer, so I went to the properties and copied the connect string like it was suggested above and still getting that error. As a refresher, I am using the following string and credentials:

Data Source Name : cutacms
Description : <blank>
User Name: <user name>
Server: cutacms

I used Microsoft ODBC for Oracle driver

cutacms is defined in tnsnames.ora

The connection string setup I used is as follows:

Dim connection As Odbc.OdbcConnection
Dim sConnString As String = "Provider=OraOLEDB.Oracle;Data Source=cutacms;User Id=<user name>;Password=<password>;"
connection = New Odbc.OdbcConnection(sConnString)
connection.Open()

I have also tried the string "Provider=MSDAORA;Data Source=cutacms;User ID=uid;Password = pwd;"

OK,
Let me know if  cutacms is then name of database you are trying to connect to?

cutacms is the sid of the database as defined by tnsnames.ora. I copied part of the file to list it below just in case it will help

 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = logistics-svr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CUTACMS)

Can you put IP address instead of host name.

Also did you tried to test DSN as steps mentioned in 11.05.2008 at 10:33PM EST, ID: 22892201

Yes the DSN is working correctly. subbing in the ip address does not work either.

OK,
So you are able to connect via DSN and it seems its syntax issue.
Please refer to link http://www.vbdotnetforums.com/oracle/3883-oracle-connection-string-syntax.html

Are you able to connect database using ADODB object instead of odbc?

I got it. I had to change the connect string to "Driver={Microsoft ODBC for Oracle};Server=cutacms;Uid=uid;Pwd=pwd;"

So, to summarize this is what fixed this issue:
1) Create a DSN
2) full full access to the all_code segment in .net 2.0
3) change the connect string to the listed above (which happened to be the last connect string listed on the connectionstrings website left for me to use)


Man, working with SQL server is so much easier. Thanks for all the help, Im sure Ill be back soon with other issues.

Great!!
I am glad that I could help