Connection string to Oracle 10g DB using VB.net

StealthRT picture StealthRT · Jan 26, 2011 · Viewed 38.9k times · Source

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

Answer

Keeper picture Keeper · Jan 26, 2011

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