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

Validations in Visual Basic 6.0

This topic will demonstrate how to perform client side validations in Visual Basic 6.0. It will guide you with a step by step procedure. 1) Firstly in the below image suppose you want to accept only integer numbers as id in the textbox next to ID label in the form from the user you can perform client side validations in the following way: These are the ascii codes that i am using for acepting only integer values from user. Back space - 8 0 to 9 Integers - 48 to 57 Delete - 127 This is how the function will look. Private Sub Text2_KeyPress(KeyAscii As Integer) If Not ((KeyAscii >= 48 And KeyAscii <= 57) Or KeyAscii = 127 Or KeyAscii = 8) Then MsgBox "enter proper value" KeyAscii = 0 End If End Sub Double click on the text box which you want to validate. Next select the event as keypress from top right list box. In this case the name of text box is text1. This is the function for accepting input as integer value only from the user.

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