Connection string to Oracle 10g DB using VB.net

Question

Hey all i am VERY new to a Oracle DB and i am trying to connect to it via VB.net 2010. I have been trying the following:

Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim dr As OleDbDataReader

    myConnection = New OleDbConnection("Provider=MSDAORA.1;UserID=xxxx;password=xxxx; database=xxxx")
    'MSDORA is the provider when working with Oracle
    Try
        myConnection.Open()
        'opening the connection
        myCommand = New OleDbCommand("Select * from emp", myConnection)
        'executing the command and assigning it to connection
        dr = myCommand.ExecuteReader()
        While dr.Read()
            'reading from the datareader
            MessageBox.Show("EmpNo" & dr(0))
            MessageBox.Show("EName" & dr(1))
            MessageBox.Show("Job" & dr(2))
            MessageBox.Show("Mgr" & dr(3))
            MessageBox.Show("HireDate" & dr(4))
            'displaying data from the table
        End While
        dr.Close()
        myConnection.Close()
    Catch ee As Exception
    End Try

And i get the error on the Catch ee As Exception line: ORA-12560: TNS:protocol adapter error

I also have a tnsnames.ora file on my computer but i am unsure if i need to use that when connecting (or really, how too in the first place)? Is it needed for the code above?

I am trying to use a DNS-Less connection to the DB. Not sure if that is what it is doing in this or not?

Any help would be great!!! :o)

David

score 3 · Accepted Answer · answered Jan 26 '11 at 15:34

3

There are many ways: the one I use almost every time that doesn't require an entry in TNSNAMES.ORA is this:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

And if you don't need an OleDb connection I think you should use System.Data.OracleClient or any other free provider (like DevArt dotConnect for Oracle Express)

Source: http://www.connectionstrings.com/oracle

answered Jan 26 '11 at 15:34

Keeper

3,516
1
18
29

So like so? myConnection = New OleDbConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=xxx;Password=xxx;") – StealthRT Jan 26 '11 at 15:54
1

If you want to use OleDbConnection just prepend `Provider=MSDAORA.1;` or user an Oracle adapter.Remember to change the host, the port (if you're not using the default one) and the service name. – Keeper Jan 26 '11 at 16:00
Awesome, i got it keeper! THANKS! :) – StealthRT Jan 26 '11 at 16:27

score 1 · Answer 2 · answered Jan 27 '11 at 13:28

1

I always use www.connectionstrings.com/ when I need to create a new connection string to the DB and when connection string format is not on top of my head.

answered Jan 27 '11 at 13:28

Roman Podlinov

23,806
7
41
60

Connection string to Oracle 10g DB using VB.net

2 Answers2