VB6 + Oracle Connection

By Amitav Biswas

(MBA,MCA, B.Sc. (H) in Computer Science)

Teacher/ HOD, Computer Sci. Dept.,Behala College & Vivekananda College

1st Technique(Using Data Form Wizard)

Connect to remote system using ODBC

Logon Windows XP/ Win 7 Select Start > Settings > Control Panel In the Control Panel Window, open

“Administrative Tools - Data Sources (ODBC)”

Click the “User DSN” tab Click the “Add..” Button

In the “Create New Data Source”, select “Microsoft ODBC for Oracle”

In the “Microsoft ODBC for Oracle Setup” dialog, enter the following:Database Source Name: ora8iDescription: <optional>Username: <optional>Server Name: <Optional>

Click OK

Create table under remote system Start “Oracle -> OraHome80 ->

Application Development -> SQL PlusUser Name: ScottPassword: TigerHost String: <Optional>

Creation of Sample Table to Test:

SQL> create table Student (S_ID number(8),StuName varchar2(20),Marks Number(8));

SQL> insert into Student (S_ID, StuName, Marks) values (12345, ‘XYZ’, 85);

SQL> commit;SQL> exit;

Access the remote database under Visual Basic 6 using Data Form Wizard:

Starts Visual Basic 6 Open a new Standard .EXE project

under the new tab From the Add-Ins menu, select Add-In

Manager, double click VB 6 Data Form Wizard and click OK

Under Add-Ins menu, select Data Form Wizard

Click Next on the after finish reading the Introduction screen

Under the Data-Type Screen, choose Remote(ODBC), click Next

Under Connection Information, ODBC Connect Data:

DSN: ora8iUser Name: ScottPassword: Tiger

Single Record Form Layout: 1 Under Form Layout, Name the

form: “Customerform” and check Single record, under Binding Type, select ADO Code, click Next

(For Grid (DataSheet) Form Layout, create another Data Form and select the Grid option after this exercise)

Under Record Source, select Student Click the double arrow to bring all the

available fields of All_Customer_Table to the selected field, click Next

Under Control Selection, make sure all the available controls are checked, and click Next, click Finish

Select Project1 Properties from the Project Menu, under Startup Object, select Customerform and click OK

Run the program (Menu Run—Start or Press F5)

(If any error occur during the run-time mode, just end the application (not VB) and start it again!)

Layout:

To Add a Record: Click the Add Button Type all the information to the fields in

specified format Click the Update Button

To Browse the Records: Click the left and right arrow at the

bottom of the form To Delete a Record:

Select the desired record to delete Click the Delete Button Click the Refresh Button to refresh the

table

To Modify a Record: Select the desired record to modify Click the Edit button, modify the data Click the Update Button

Click Close to quit

Note..!! In the design mode, double click on the items

(buttons, labels, textboxes) and investigate the corresponding code

(Especially code in the following procedures: Form_Load, adoPrimaryRS_MoveComplete, cmdAdd_Click, cmdDelete_Click, cmdRefresh_Click, cmdEdit_Click, cmdUpdate_Click, cmdNext_Click, SetButtons)

Repeat the above steps using the Grid (DataSheet) Form Layout

2nd Technique(Using Coding)

[Connecting to- Oracle Using ADO Object]

Know it First.!! ADO –ActiveX data Object OLEDB- Object Linking and

Embedding, Database ADODC- ActiveX data object database

control ADODB- ActiveX Data Objects for Data

Base

Adding References..

Adding Components..

After Adding Component..

Form Layout Design

Adding Module..

Code for Module:Public cn As New ADODB.Connection

 Public rs As New ADODB.Recordset

Coding for “Save” Button:

Private Sub Command1_Click()If rs.State = 1 Then rs.Closers.CursorLocation = adUseClientcn.Open "Provider = msdaora.1;user id=scott;password=tiger"rs.Open "select * from Stu", cn, adOpenStatic, adLockBatchOptimistic

Continuation..rs.AddNewrs(0) = Text1.Textrs(1) = Text2.Textrs.UpdateBatchMsgBox "New Student was added Successfully..!!", vbInformation, "Process Completed"

End Sub

Coding for “Clear” Button:

Private Sub Command2_Click()Text1.Text = " "Text2.Text = " "

End Sub

Coding for “Exit” Button:

Private Sub Command3_Click()Unload Me

End Sub

Sample Output: