Oracle Connection

[vb_newbie_2010]

Hello Everyone,

First time poster I apologize in advance if any of my verbage is not correct.

I am using visual basic 2010, specifically vb.net, on a Windows 7 machine. I have created a form project, in which I attempt to connect to a remotely hosted database (meaning the db exists on a server elsewhere) using the following code, which I just took from a tutorial on connecting to an Oracle db:

Imports System.Data.OleDb

...
Dim myConnection As OleDbConnection
myConnection = New OleDbConnection("Provider=OraOLEDB.Oracle;" & "Data Source=servername: portNumber;" & "UserID=odb;" & "password=odb;" & "database=tnsname")
Try
myConnection.Open()
myConnection.Close()
Catch ex As Exception
MessageBox.Show("Connection Error")
End Try
...

Beleive it or not I continue to have the exception thrown.

I have several questions.

1. If you can answer this one disregard the rest, Would anyone mind providing an example of how exactly I can connect to the db?

2. Do I need to install the oracle client on my loacl machine and add a reference of some type? If so would anyone mind explaining how this may be accomplished, the reference that is?

3. Do I need to use the administrator tools from the OS to add an Oracle driver via the data source administrator? If so I encounter the following error:

The Oracle client and networking components were not found...

Thanks a ton in advance for any help at all you may wish to bestow upon this lost soul

 

[cjard]

Oracle is an odd beast. Connections are often made using TNS, a sort of oracle aliasing system for the true details. To set up a machine for Oracle conenctivity I would do:

Install the oracle data provider (client) that is within 2 revisions of my target db, probably to somewhere like c:\oracle\client\10 (or wherever it suggests)

Ensure that the user that will run the app has read/write permissions to where oracle client is installed (this is a common trip up with web stuff, it seems the user that runs the app in IIS has to have explicit grants to read/write the client directory tree)

Ensure that your tnsnames.ora contains an entry pertaining to the database you wish to use. Search the entire machine for this file because there can be multiples, and which one is in use is often dictated by the most recently installed version of the oracle client
Check also your sqlnet.ora file as it can have defaults in there that can prevent you connecting to oracle

Try a tnsping from the command line: tnsping your_oracle_instance_name
e.g. I have a tns entry for LOCXE that is host=localhost servicename=XE port=1521, so I would tnsping LOCXE. The oracle instance name is not necessarily the name of the tns name you have picked. tnsnam is a local alias used on your machine, in your apps, to refer to a remote service. Any machine you instal your app on must have an oracle client also with that tns name in the tnsnames.ora

For connecting to an oracle db, i get Visual Studio to do it (i dont write boring database code any more)
Open the dataset designer after adding a new dataset to your app. Add a new tableadapter and build a new connection string. Set your provider to oracle, the tnsname goes in the box along with the username and password for the service, then test the conenction

If you get tns:no listener then possibly the tns listener service isnt running. Other errors you may encounter relate to "doesnt know of service listed" =for some reason your oracle instance hasnt registerd with the tns listener, or "could not resolve service in descriptor"=some aspect of tnsnames, or the tcp conenction to the oracle instance is not correct