Skip to main content

Oracle and VB 6.0 Connectivity (Part 1)

Here is a basic VB application called voter information system demonstrated which explains the concepts quite clearly. The first step required to establish connectivity is preparing the back end and the front end of the project. These steps are divided into two parts first the back end that is the oracle and front end that is the Visual Basic 6.0.

Oracle
Firstly we need to set up the database. Create the tables, triggers and procedures and database in the oracle.
Create tables:
Example:
create table voter(id number(5) primary key, name varchar2(25),address varchar2(50),age number(3),gender varchar2(6),dob varchar2(30));
create table votertemp(id number(5) primary key, name varchar2(25),address varchar2(50),age number(3),gender varchar2(6),dob varchar2(30));

(we will be using the next table in the triggers that we will be implementing)

Visual Basic 6.0
Develop the VB application as in this example given below. Design the necessary forms for the application. The next part is establishing the connectivity.


//VB CODE FOR VOTER INFO
Dim conv As ADODB.Connection
Dim rsv As ADODB.Recordset
Dim cmdv As ADODB.Command
Private Sub Form_Load()
Set conv = New ADODB.Connection
Set rsv = New ADODB.Recordset
Set cmdv = New ADODB.Command
conv.Open ""
End Sub
Set up connectivity using data source
1) Open the control panel -> Administrative tools -> Data sources
2) System DSN -> Add -> MS ODBC for oracle -> Ok
3) DSN = voter
4) Username = scott -> ok “scott is login name for oracle”

Set up VB
1) Right click adodc control -> properties.

2) Use connection string ->build.
3) Select driver MS OLE DB provider for ODBC drivers -> next.
4) Select DSN table name voter from list.
5) Type username and password “scott and tiger” which ever used for logging in oracle.
6) Allowing saving password -> test connection. -> if test connection successful then proceed else the check again the above steps.
7) Type catalog name same as table name.

8) Copy the string generated in use connection string.
9) Paste it in the connection to open.
10) Eg. conv.Open "Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=voter;Initial Catalog=voter"

Now the code will look like this:

Dim conv As ADODB.Connection
Dim rsv As ADODB.Recordset
Dim cmdv As ADODB.Command


Private Sub Form_Load()
Set conv = New ADODB.Connection
Set rsv = New ADODB.Recordset
Set cmdv = New ADODB.Command
conv.Open "Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=voter;Initial Catalog=voter"
End Sub

You have successfully set up connection with the Oracle Back End and VB Front End. Adding and retrieving records we will see in next part.
Part 2

Comments

  1. I tried the same steps but i am getting an error i.e.Microsoft Data Link Error - Test Connection Failed because of an error in initializing provider.[Microsoft][ODBC driver for Oracle][Oracle]
    Please help me.

    ReplyDelete
  2. highly appreciated...........

    ReplyDelete
  3. highly appreciated......

    ReplyDelete
  4. hi..i am Megha.I am in TE.I have a mini project to create a database having frontend VB with oracle Sql backend.
    please could you suggest some suitable and fairly easy topic.Also can you elaborate more on how to create the connectivity?
    Thank you!:)

    ReplyDelete
  5. Hi ur from GH Raisoni Right?????
    good explanation... it helped me a lot
    but wat about for windows 7 i dont get administritive tools option plz help me

    ReplyDelete
  6. "I tried the same steps but i am getting an error i.e.Microsoft Data Link Error - Test Connection Failed because of an error in initializing provider.[Microsoft][ODBC driver for Oracle][Oracle]
    Please help me. "

    - Hey try not entering the oracle server name:

    just enter your id and password it works for me .

    ReplyDelete
  7. Thanks...it helps me a lot...

    ReplyDelete
  8. pls give me solution friends if anybody know ---->>>>>
    when i click test connection i got error msg " test connection failed because of an error in intializing provider.
    unspecified error "

    ReplyDelete
  9. hey plz remove this bird coz i cant read properly

    ReplyDelete
  10. nishanthanbca@gmail.comMarch 17, 2013 at 8:32 AM

    Hai, I am Nishanthan . I,'m pursuing BCA from bharatiar university. I follow the same step given above. It's correctly working, no any errors occur. Thanking you very much for the author ...

    ReplyDelete
  11. hey iam avinash getting an error like
    [Microsoft][ODBC driver for Oracle][oracle]ora-00942;table or view does not exist

    ReplyDelete
  12. This comment has been removed by a blog administrator.

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle and VB 6.0 Connectivity (Part 2)

The first part defined how to establish connectivity between the back end and the front end of the project. Next we will see how to implement triggers.
Oracle
We need to Create the triggers in the oracle.
1) type ed in sql
2) it opens a notepad window
3) type the trigger as in the below example
4) exit the notepad with saving
5)and then type "/"
6) this will show a message as trigger created!
7) It should not show trigger created with compilation errors. If this message is shown check your syntax of trigger.

Example:
create or replace trigger votertrig
after update or delete on voter
for each row
begin
insert into votertemp values(:old.id,:old.name,:old.address,:old.age,:old.gender,:old.dob);
end;

Whenever we perform an update or delete operation the old values get stored into the votertemp table and the changed data gets reflected into the permanent table

Visual Basic 6.0
In VB we do not have to do anything as triggers are automatically fired whenever an update or delete operation…

Oracle and VB 6.0 Connectivity (Part 3)

Now we will see how to insert values into table a table and to view them.

Oracle
Initially we had created table in part1.

Visual Basic 6.0
Now we wil insert data into the table through visual basic.

The code for inserting will be as follows
When you double click on add button it will open your code window it will have this code!

Private Sub Command1_Click()
Dim gen As String
If (Option1.Value = True) Then
gen = "male"
ElseIf (Option2.Value = True) Then
gen = "female"
End If
rsv.Open "insert into voter values(" & Text5.Text & ",'" & Text2.Text & "','" & Text3.Text & "','" & Combo1.Text & "','" & gen & "','" & DTPicker1.Value & "')", conv, adOpenDynamic, adLockOptimistic
If (rsv.State = 1) Then
rsv.Close
End If
MsgBox ("Data Entered")
End Sub

This will add the data entered into the fields into the oracle …