VB.NET - How to connect remote oracle database in vb.net.

Asked By kirankumar on 01-Dec-10 02:16 AM
hi,

How to connect remote oracle database in vb.net.

Thanks in advance
kiran.
Sagar P replied to kirankumar on 01-Dec-10 02:19 AM
You just need to specify proper connection string to your connection object like this;

oConn.Open "Provider=msdaora;" & _
"Data Source=ORCL;" & _
"User Id=icsc;" & _
"Password=icsc;server=name"

Check this link for connection strings of oracle;

http://www.connectionstrings.com/oracle

Reena Jain replied to kirankumar on 01-Dec-10 02:22 AM
hi,

here are sample for different connection string

Using TNS
Data Source=TORCL;User Id=myUsername;Password=myPassword;
 
Using integrated security
Data Source=TORCL;Integrated Security=SSPI;
 
Using ODP.NET without tnsnames.ora
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;
 
Using the Easy Connect Naming Method (aka EZ Connect)
The easy connect naming method enables clients to connect to a database without any configuration.
Data Source=username/password@//myserver:1521/my.service.com;
Port 1521 is used if no port number is specified in the connection string.

Make sure that EZCONNECT is enabled in the sqlnet.ora file. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

'//' in data source is optional and is there to enable URL style hostname values
Easy Connect Naming Method to connect to an Instance
This one does not specify a service or a port.
Data Source=username/password@myserver//instancename;
 
Easy Connect Naming Method to connect to a dedicated server instance
This one does not specify a service or a port.
Data Source=username/password@myserver/myservice:dedicated/instancename;

hope this will help you
Danasegarane Arunachalam replied to Sagar P on 01-Dec-10 02:23 AM
Imports System.Data.OleDB
 
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim dr As New OleDbDataReader()
'declaration
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load
myConnection = New OleDbConnection_
("Conection string")
'MSDORA is the provider when working with Oracle
Try
myConnection.Open()
'opening the connection
myCommand = New OleDbCommand("Your Query", myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
While dr.Read()
'Read the values using dr.item("ColumnName")
End While
dr.Close()
myConnection.Close()
Catch e As Exception
End Try
End Sub
End Class
kirankumar replied to Sagar P on 01-Dec-10 02:29 AM
hi sagar,

I used the connection string in the same way what you specified and it's throwing an error "  Oracle error occurred, but error message could not be retrieved from Oracle." 

DL M replied to kirankumar on 01-Dec-10 02:49 AM
You will use classes from the System.Data.OracleClient namespace, e.g. OracleConnection. You should read any ADO.NET tutorial as the structure of the code is the same no matter the data source. Only the details change. Go to http://www.connectionstrings.com/ for the appropriate connection string format.
Sagar P replied to kirankumar on 01-Dec-10 03:37 AM

You should make sure that Oracle client is working fine before you can check any other issues in your .net application. Sometimes it's not so easy to solve the problem from an OLE connection so if you are using OLE connection then use an OracleConnection instead.