Connect to ORACLE DATABASE through ODBC connection....

Posted on 2006-04-21
Medium Priority
Last Modified: 2006-11-18
Hi experts !
Ok, let 's go... :

I want to connect to a ORACLE database through an ODBC connection :

I've imported "Imports System.Data.Odbc", doing this :

MyDbServerConnection.ConnectionString = Str

            MySelectCmd.Connection = MyDbServerConnection
            'MySelectCmd.CommandText = "select * from test_table"

            MyAdapter.SelectCommand = MySelectCmd

            Dim ds As New DataSet
            MyAdapter.Fill(ds, "test_table")

            DataGrid1.DataSource = ds
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        End Try

I've tried Str with this :

DSN=DiaDia;user id=[User];password=[PASS ]
Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=[User];pwd=[PASS ]
Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=[User];pwd=[PASS ]

With errors....

Maybe I' would have to use  : Imports System.Data.OleDb  ?  ? ?

 Dim oCon As New OleDbConnection
        Dim oAdapter As New OleDbDataAdapter
        Dim oCmd As New OleDbCommand
        oCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"

            oCmd.Connection = oCon
            oCmd.CommandText = "Select * from Test_Table"

            oAdapter.SelectCommand = oCmd

            Dim ds As New DataSet
            MyAdapter.Fill(ds, "test_table")

            DataGrid1.DataSource = ds
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        End Try

Thanks !!
Question by:franklinan
1 Comment

Accepted Solution

Naveen Swamy earned 2000 total points
ID: 16509511
Dim oOleDbConnection As OleDb.OleDbConnection
Dim sConnString As String = _
         "Provider=OraOLEDB.Oracle;" & _
         "Data Source=MyOracleDB;" & _
         "User ID=myUsername;" & _
oOleDbConnection = New OleDb.OleDbConnection(sConnString)


Dim oODBCConnection As Odbc.OdbcConnection
Dim sConnString As String = _
         "Driver={Microsoft ODBC for Oracle};" & _
         "Server=OracleServer.world;" & _
         "Uid=myUsername;" & _
oODBCConnection = New Odbc.OdbcConnection(sConnString)


Imports System.Data.OracleClient
Dim oOracleConn As OracleConnection = New OracleConnection()
oOracleConn.ConnectionString = "Data Source=Oracle8i;" & _
                               "Integrated Security=SSPI";

Also there is ODP.NET from Oracle. SHould be very compatible to .NET




 Standard Security:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"

"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw;","admin", "" 

"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="

 OLE DB, OleDbConnection (.NET)

 Standard security:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=asdasd;"

 Workgroup (system database):
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;","admin", "" 

 With password:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;","admin", "" 


 New version:
"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"

 Old version:
"Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

 OLE DB, OleDbConnection (.NET)

 Standard security:
"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"
This one's from Microsoft, the following are from Oracle
 Standard Security:
"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"

 Trusted Connection:
"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"

 OracleConnection (.NET)

"Data Source=Oracle8i;Integrated Security=yes";
This one works only with Oracle 8i release 3 or later
 Declare the OracleConnection:

using System.Data.OracleClient;
OracleConnection oOracleConn = new OracleConnection();
oOracleConn.ConnectionString = "my connectionstring";

Imports System.Data.OracleClient
Dim oOracleConn As OracleConnection = New OracleConnection()
oOracleConn.ConnectionString = "my connectionstring"

 Data Shape

 MS Data Shape:
"Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;user id=username;password=mypw"


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month9 days, 8 hours left to enroll

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question