how to establish connection between oracle 10g express edition and v.b 6?

Question

how to establish connection between oracle 10g express edition and v.b 6.

Can any1 please post how to connect oracle 10g express edition to MS V.B 6.

I am trying to connect v.b 6 to oracle. Please post how to connect the database and also post the string to write in V.b 6.

I am using win xp operating system.

Thankyou

Answer 1

Prerequisite
The reader is assumed to be familiar with MS Visual Studio.NET, and also possess an understanding of the rudimentaries of ODP.NET and databases.
Introduction

ODP.NET, which is included in the Oracle.DataAccesss.dll assembly, provides a rich collection of classes that assist in easy database interaction. It uses Oracle's native APIs to offer fast and reliable access to Oracle data and features from any .NET application.

In this how-to we'll look at how to use the OracleConnection class, provided by ODP.NET, to establish a connection to an Oracle database and interact with the database. Here, we'll use a small code fragment to demonstrate how to connect to an Oracle database using ODP.NET.

Further, connection pooling is enabled in ODP.NET (by default). So we'll also look at how you can control the connection pooling parameters provided by ODP.NET.

Requirements

*

Oracle Data Provider for .NET (ODP.NET)

*

Oracle9i Database or later running SQL*Net TCP/IP listener . Refer Oracle By Example(OBE) Series on Oracle9i Database for step-by-step instructions on how to install and set up the Oracle9i database.
* C# or VB.NET installed with Microsoft Visual Studio .NET (including Microsoft .NET Framework 1.0 or later)

Description

When you install ODP.NET, the Oracle Universal Installer automatically registers ODP.NET with the Global Assembly Cache (GAC). The GAC in the .NET framework provides a central place for registering assemblies. These assemblies once registered are then available to all applications for usage, including the development environments like Visual Studio.NET.

The most important class with respect to this how-to is the OracleConnection class. An OracleConnection object represents a connection to an Oracle database. In this how-to we will demonstrate how to connect to an Oracle database and list names of all the employees and their employee number present in a table. Alternatively, you may use any database user account that has connect and resource privileges to connect to the Oracle database.

Prior to connecting to an Oracle Database using ODP.NET, one should add Net Service Names. Specifying the Net Service Names also known as TNS alias is used to identify an Oracle Database Instance.

Add Net Service Names

ODP.NET uses Net Service Names to identify the data source (database) it connects to. The Oracle Net Configuration Assistant Tool, a post installation tool that configures basic network components, can be used to setup the Net Service Names. Please refer to the Oracle9i Net Services Administrator's Guide available at OTN for the details on using this tool to setup the Net Services Names.


Create Required Database Table

To create the required "emptab" table, follow the instructions given below:

1. Start SQL*Plus.
2. Connect to the Oracle database using the desired username, password and connect string. Ensure to use the same parameters later, in your ODP.NET ConnectionString.
3. Paste the following SQL script (Listing 1) on the SQL> prompt:
Listing 1

DROP TABLE emptab;
CREATE TABLE emptab (empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10));
INSERT INTO emptab VALUES ( 1, 'Mark');
INSERT INTO emptab VALUES ( 2, 'Roger');
INSERT INTO emptab VALUES ( 3, 'Crook');
COMMIT;

Code Walk-Through

Include Required Namespaces: It is worthwhile to add references of the namespaces in the 'general declarations' section of the .cs or .vb file, to avoid qualifying their usage later in the script:
C#

using System;

using System.Data;
using Oracle.DataAccess.Client;

Visual Basic .NET

Imports System
Imports System.Data
Imports Oracle.DataAccess.Client

1. Set the connection parameters like the User Id, Password and Data Source:

Visual Basic .NET


' Create the connection object
Dim con As OracleConnection = New OracleConnection()

' Specify the connect string
' NOTE: Modify User Id, Password, Data Source as per your database set up
con.ConnectionString = "User Id=Scott;Password=tiger;Data Source=ora9idb;"

2. Open database connection through ODP.NET:

Visual Basic .NET

Try

' Open the connection
con.Open()
Console.WriteLine("Connection to Oracle database established successfully !")
Console.WriteLine(" ")

Catch ex As Exception

Console.WriteLine(ex.Message)
End Try

3. Create command object to perform a query against the database:

Visual Basic .NET

Try



' Execute command, create OracleDataReader object
Dim reader As OracleDataReader = cmd.ExecuteReader()
While (reader.Read())

' Output Employee Name and Number
Console.WriteLine("Employee Number : " & _
reader.GetDecimal(0) & _
" , " & _
"Employee Name : " & _
reader.GetString(1))

End While
Catch ex As Exception

Console.WriteLine(ex.Message)

Finally

' Dispose OracleCommand object
cmd.Dispose()

' Close and Dispose OracleConnection object
con.Close()
con.Dispose()

End Try

Setup and Run the How-To

1. Open Visual Studio.NET.

2. Create a Console Application Project:

Visual Basic .NET

Create a Console Application Project in Visual Basic .NET. Module1.vb is added to the
project by default.

3. Ensure that your project contains references to the System, Oracle.DataAccess and System.Data namespaces. Add references to these namespaces if they do not exist.

4. Copy the code:

Answer 2

All version of oracle express edition are free. you need no license to use oracle express 10g.

Answer 3

Dim wstemp As Workspace
Dim dbtemp As Database
Dim rstemp As Recordset

Set wstemp = DBEngine.Workspaces(0)
Set dbtemp = wstemp.OpenDatabase("", False, False, "ODBC;DSN=Oracle;USR=scott;PWD=tiger")
Set rstemp = dbtemp.OpenRecordset(myquery.Text, dbOpenDynaset, dbSQLPassThrough)
howmany = 0
Combo1.Clear
Do Until rstemp.EOF
msgbox rstemp(0)
rstemp.MoveNext
howmany = howmany + 1
Loop