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

Solution 1

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

Solution 2

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.

Comments

[StealthRT]

So like so? myConnection = New OleDbConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HO‌​ST=MyHost)(PORT=1521‌​)))(CONNECT_DATA=(SE‌​RVER=DEDICATED)(SERV‌​ICE_NAME=MyOracleSID‌​)));User Id=xxx;Password=xxx;")

[Keeper]

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.

[StealthRT]

Awesome, i got it keeper! THANKS! :)