Hi Readers,
I am blogging on www.knytesh.com. So you can catch up for more details on above address.

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

13 comments:

Anonymous said...

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.

Anonymous said...

highly appreciated...........

Anonymous said...

highly appreciated......

Megha said...

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!:)

akash pokharkar said...

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

Saurav Chatterjee সৌরভ said...

"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 .

Sonam said...

Thanks...it helps me a lot...

chokkalingam said...

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 "

Anonymous said...

thanks

shital said...

hey plz remove this bird coz i cant read properly

nishanthanbca@gmail.com said...

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 ...

avinash said...

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

sanjay varma said...

Appreciation for nice Updates, I found something new and folks can get useful info about BEST ONLINE TRAINING

Post a Comment

Share
Subscribe
Loading...

About Me

My photo
Pune, Maharashtra, India
Web Consultant at Rapid Circle.

www.about.me/niteshpatare
www.knytesh.com