How to connect vb 6.0 with oracle

[Mewad]

Is it possible to connect vb 6.0 with oracle, if yes than how?. I’m making a project in my last year of bsc-it, and I’m using vb 6.0. I don’t know much about the connection. Please give me the step by step procedure of connection. , I’ve never done connection in vb 6.0. please, I need your help. my project contains of 200 marks. Your small help will be very helpful for me.

[SalVatore]

For connecting vb 6.0 to oracle you need a module and the sub procedure. The following code will make your connection to oracle very easily, just write the following module and sub procedure in your program:
Option Explicit
Public rs As New ADODB.Recordset
Public mdkcmd As New ADODB.Command
Public conn As New ADODB.Connection
Public myobj As Object

Public Sub OpenConn()
With conn
.CursorLocation = adUseClient
.ConnectionString = "dsn=MizMoORCL;uid=MizMo;pwd=Pooh"
.Open
End With
End Sub

In the Load Event of my Main Form, I open the Connection:
Private Sub Form_Load()
OpenConn
End Sub

THE MAIN FORM:
My main form consists of five Command Buttons and an Exit button. Each Command Button will open the subsequent forms within the application. In the Click Event of these buttons, I simply show the respective form.
Private Sub cmdKirbyCustomer_Click()
frmKirbyCustomer.Show
End Sub

THE APPLICATION FORMS:
CUSTOMER, STOCK, ORDERS, ITEM AND HISTORY
In the Form Load Event of each subsequent form I choose my recordset options, open my Recordset off of the established Connection and fill the Text Boxes on the form with the appropriate data from the Fields in my Recordset using a sub procedure called "FillFields":

Private Sub Form_Load()
With rs
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CacheSize = 50
.Source = "Select * from Customer"
.ActiveConnection = "dsn=MizMoORCL;uid=MizMo;pwd=Pooh"
.Open
End With
FillFields
End Sub

THE FILLFIELDS PROCEDURE:
Public Sub FillFields()
txtCustomerID.Text = rs.Fields("CustomerID") & ""
txtCompanyName = rs.Fields("CompanyName") & ""
txtLastName = rs.Fields("LastName") & ""
txtFirstName = rs.Fields("FirstName") & ""
txtAddress = rs.Fields("Address") & ""
txtCity = rs.Fields("City") & ""
txtState = rs.Fields("State") & ""
txtZip = rs.Fields("ZipCode") & ""
txtPhone = rs.Fields("Phone") & ""
txtFax = rs.Fields("Fax") & ""
End Sub

[Ronchi]

Following are the code which is most important for connection. You have to include this code in your program for connection:
Dim ConnString as string
ConnString = "Provider=MSDAORA.1 ; Password=myPassword; User ID=myUser ; Data Source = ORCL; Persist Security Info=True"
'// Where ORCL is the Oracle Service you are trying to connect
Dim objConn as new ADODB.Connection
objConn.open ConnString

[Dwarner]

We cancreate a connection using ADO connection object. To access an Oracle database it is very similar to how you access any other database. We can simply use an ADO Connection object. We set the provider to be our Oracle provider and setup our connection string and password.
1. Set dbConn = New ADODB.Connection
2. With dbConn
3. .Provider = "OraOLEDB.Oracle"
4. .Properties("Data Source") = "DatabaseName"
5. .Properties("User Id") = "someuser"
6. .Properties("Password") = "somepassword"
7. .Open
8. End With
After we setup the connection all we do next is setup an ADO Command object that will be used with our oracle database. This is the same things we do for any Visual Basic database application.
1. Set Cmd = New ADODB.Command
2. Set Cmd.ActiveConnection = dbConn
3. With Cmd
4. .Parameters.Append .CreateParameter(, adVarChar, adParamOutput, 50)
5. .Parameters.Append .CreateParameter(, adNumeric, adParamOutput)
6. End Wit
You generate the select statement you want to run then open the ref cursor you created as follows:
1. Open cRefCur For
2. Select ....... (columns form whatever tables)
3. From (table names)
4. Where (conditions and Joins).
This is the way to create the cursor:

First we create a package that will hold all the different return types:
1. CREATE OR REPLACE PACKAGE cv_types AS
2.
3. TYPE WellData IS RECORD(
4. WellName Varchar2(50),
5. ResultsCount Number
6. );
7. TYPE CV_WEllData IS REF CURSOR RETURN WellData;
8.
9. End;
10. /
Next we create a stored procedure that will use that ref cursor declared above:
(This procedure does not have any inputs, only output paramters).
1. Create Or Replace Procedure WellCounting (
2. pWellName OUT VARCHAR2,
3. pCount OUT NUMBER,
4. rsWellData IN OUT cv_types.CV_WEllData)
5.
6. AS
7.
8. BEGIN
9. Open rsWellData For
10. Select
11. Wells.WELLNAME,Count(RESULTS.WELLID)
12. Into
13. pWellName,
14. pCount
15. From
16. Wells, Results
17. Where
18. Wells.WellID = Results.WellID
19. group by
20. WEllName;
21.
22. EXCEPTION
23. WHEN OTHERS THEN
24. ROLLBACK WORK;
25. RAISE;
26.
27. End WellCounting;
28. /

[Author]

You can do this by using ADO object. First, you need to Install Oracle client software and make sure you are able to connect to the database (test the connection from SQL*Plus)

Then the VB code is pretty simple. This is an example using ADO:
Dim C As New ADODB.Connection
Dim Rec As New ADODB.Recordset
Dim SQL As String

'Open the connection
C.Open "Driver={Microsoft ODBC for Oracle};Server=your_database_alias; Uid=your_username;Pwd=your_password;"

'Sql command
SQL = "SELECT * FROM your_table where <some_condition>"

'Open the recordset
Rec.Open SQL, C, adOpenForwardOnly, adLockReadOnly

'Process the records
Do Until Rec.EOF
'do something
Loop

Rec.Close
Set Rec = Nothing
C.Close

[Aleksandra]

Yes, you can connect vb 6.0. to oracle by using the following code, not sure but try this.
Code Snippet:

Set m_dbConn = New ADODB.Connection

With m_dbConn
.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=oplprod; User ID=myid; Password=mypwd;"
.ConnectionTimeout = 10
.CursorLocation = adUseClient
.Open
End With
Hope it works for you.