Oracle and Visual Basic using ADO

Level:
Level2

This VB6 tutorial explains how you can access an Oracle database from within Visual Basic. It is a bit short as it was thrown together from a post on the vbforums.com page. However if you check out the Sample VB6 and Oracle Source Code that goes along with it. You should be able to easily understand what is going on. Read on and enjoy as you develop your Visual Basic Oracle application.

Oracle databases have been around for years, and although they are not as popular as their Microsoft counterpart, many business rely on Oracle backends for all their needs. Because of this, we must know how to interface with an Oracle database from within our VB6 application. This VB6 tutorial will walk us through exactly how to do this.

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.

Set dbConn = New ADODB.Connection
With dbConn
    .Provider = "OraOLEDB.Oracle"
    .Properties("Data Source") = "DatabaseName"
    .Properties("User Id") = "someuser"
    .Properties("Password") = "somepassword"
    .Open
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.

Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = dbConn
With Cmd
    .Parameters.Append .CreateParameter(, adVarChar, adParamOutput, 50)
    .Parameters.Append .CreateParameter(, adNumeric, adParamOutput)
End With

Now is where things start being specific to our Oracle database. Getting a result set back from an Oracle SP is not as simple as it is in SQL Server. The results must come back to the calling program in something called a reference cursor (ref cursor). This will discuss what a ref cursor is and how to implement them and get data back.

Oracle creates an implicit cursor for every select query (I think that is the same for any database system). The cursor is simple the recordset results. If you are not going to use that result set for anything else (ie: to generate another query to execute) then you do not need to declare a cursor. But to get the result set out of Oracle you need something that is called a ref cursor. This ref cursor is more or less the same as and ADO recordset. You declare the ref cursor in code some where on the Oracle database, that ref cursor (sort of a structure in .Net) is then listed as an In and Out parameter of the SP.

You generate the select statement you want to run then open the ref cursor you created as follows:

    Open cRefCur For 
       Select ....... (columns form whatever tables)
         From (table names)
         Where (conditions and Joins).
Standard SQL here with one big exception since if using Oracle 8i or earlier, Oracle at that release level did not support the Inner and Outer Join statements. You must use the Oracle version of them. Inners are just and equal sign as in Sales.ProductID = Prodcuts.ProductID. The Outer join is a lot messier, outer joins use the same equals sign and also a plus (+) sign on the deficient side of the equal sign.

This is the way to create the cursor:

First we create a package that will hold all the different return types:
CREATE OR REPLACE PACKAGE cv_types AS
  
	TYPE WellData IS RECORD(  
		WellName		Varchar2(50),
		ResultsCount	Number
	);
	TYPE CV_WEllData IS REF CURSOR RETURN WellData;
    
End;
/
Next we create a stored procedure that will use that ref cursor declared above:
(This procedure does not have any inputs, only output paramters).
Create Or Replace Procedure WellCounting (    
	pWellName   OUT	VARCHAR2,
    pCount		OUT	NUMBER,
    rsWellData	IN OUT cv_types.CV_WEllData)

AS
	
BEGIN
	Open rsWellData For
		Select 
			Wells.WELLNAME,Count(RESULTS.WELLID) 
		Into 
			pWellName,
			pCount
		From 
			Wells, Results 
		Where 
			Wells.WellID = Results.WellID
		 group by 
		 	WEllName;
            
EXCEPTION 
  WHEN OTHERS THEN         
      ROLLBACK WORK;
      RAISE;

End WellCounting;
/
We can then call the stored procedure from VB as shown in the included VB Projects.

An example of a stored procedure with input parameters is here:
Create Or Replace Procedure OneWellCount (    
	pWellID		IN  Number,
	pWellName   OUT	VARCHAR2,
    pCount		OUT	NUMBER,
    rsWellData	IN OUT cv_types.CV_WEllData
    )

AS
BEGIN
	Open rsWellData For
		Select 
			Wells.WELLNAME,Count(RESULTS.WELLID) 
		Into 
			pWellName,
			pCount
		From 
			Wells, Results 
		Where 
			Wells.WellID = pWellID And 
			Wells.WellID = Results.WellID
		 group by 
		 	WEllName;
EXCEPTION 
  WHEN OTHERS THEN         
      ROLLBACK WORK;
      RAISE;

End OneWellCount;
/
We can also test these procedures (and ref cursors) from the SQL*Plus prompt by doing the following:
  1. Enter the command SET SERVEROUTPUT ON;
  2. Now we set up variables to hold data going into and out of the SP:
    Assuming we are using the first SP displayed, the we will need 3 variables:
      VARIABLE P1   VARCHAR2(50) This is because the field we are returning is 50 chars
      VARIABLE P2   Number           This is a number coming back from the SP;
      VARIABLE P3   REFCURSOR     This will hold the result set that is coming back
  3. From the SQL prompt enter:
      EXECUTE WellCounting( :P1, :P2, :P3);
  4. If the procedures completes successfully we can now display the output.
    The variable P1 and P2 will hold the last Well Name and number of results
    for that well name. The variable P3 will hold the complete recordset that
    is being returned. To display that result in SQL*Plus enter:
      Print P3
Download the Oracle Example VB6 Code.

If you enjoyed this post, subscribe for updates (it's free)

about project

i want project of airlines passenger database management saystem with oracle as back end.plz hlp me.

Cannot find provider name

While I am trying to create a conection and open I am receing error like "Provider cannot be found. Ot may not be properly installed."

It may be like that I am not providing the correct provider name. Could anyone let me know where should I get this provider name in my machine?

dbms project on music store management

hey
I am in third year IT engg, we have to do a mini project in dbms.. topic name is music store management... we have to do project using pl/sql, vb.net and oracle 11g... can u please provide me the project with E-R diagram and abstract as well...
please...

I get a project INVENTORY

I get a project INVENTORY SYSTEM MANAGEMENT from my collage. if any one have it, Plz mail me. plz plz plz plz plz
my mail id is- sudipsays@gmail.com.

project on online shopping cart

guys...i have to do mini project on online shopping cart applicaions...for front end i have to use vb6..and back end i have to use oracle database...if anyone know vb codes for this project and connection for oracle and vb6 pls mail me...pls..pls

error 13

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Set OraDatabase = OraSession.OpenDatabase("mydb" & CStr(mydb), "username/password" & CStr(username) / CStr(password), 0&)
'Set OraDatabase = OraSession.OpenDatabase("mydb" & CStr(a), "username/password" & CStr(b) / CStr(c), 0&)

a = "" & (mydb)
b = "" & (username)
c = "" & (password)

Set OraDatabase = OraSession.OpenDatabase(CStr(a), CStr(b) / CStr(c), 0)

need project of jwellery shop

hey hi guys,
i need a project for jwellery shop...using front end as .net and back end as ms access... plzz i need the synopsis..so send it to me in 5 days....plz its a humble request..and if possible den send me the project also on my email-id...i.e pawarrasika92@gmail.com
plzzz its a request...
yours friend

thank you

i found here.. many thank dude !

student query database

hi i need a project on student query database in vb as front end and oracle as back end
plz if u have send me 2
najavith@gmail.com

i need a project

i need a project with vb as front end and ms access as back end for post office management system.pls help me

post office mang.sys. project(urgent)

I am doing a project on post office management system. The front end is Visual Basic 6.0 and back end is Oracle/SQL Database 10G. I have some problem in connecting with database,and 'object required' error is occuring. And data is not entered in the database. So,anyone can plzzzzz help me for this. And also tell me coding of ADD,Search (according to any particular feild) button coding.
PLZ HELP..ITS URGENT..

not responding

hey i try your sample project when i run it,nothing happens just stock then back to normal that the grid is desame no data...how to solve this problem...i have a project but the error is 12560 tns:protocol adapater blabla...
please help me...urgent...i am crazy on this problem...and i dont know what the solution is...i install oracle dbase 11g universal installer...
what else i install????and about tnsnames listener i dont know how to setup...i am newbie in oracle...please help me please please...T_T

urgent help pls

I'm doing project in vb6. I'm doing visual sql engine for oracle. I have an error on compile i.e. can't find library. In tat i give ok means References-vsqlprog.vbp window opens in tat it showing MISSING: Oracle InProc Server 3.0 Type Library

vb database connectivity

plz help.. i just want to create students attendance for eng clge...in vb using database access 2007..can anyone help me..plz..plz..plz..

vb database connectivity

plz help.. i just want to create students attendance for eng clge...in vb using database access 2007..can anyone help me..plz..plz..plz..

i think only access 2003 is

i think only access 2003 is compatible in vb6....^_^

patient record management system in hospital

please respond to me

I want a vb source code with Ms Access for patient record management system in hospital .
I am a 3rd year university student.
please send by email address : nigusu02@gmail.com

i need a library management

i need a library management system project with vb as front end as oracle as back end for submission as project in my final yea pls help me plz its urgent yar.. plz anyone help me sir/madam.. plz ur reply send my mail id (sasema1947@gmail.com) i am waiting ur reply...... thanks

vb6 and oracle project

I am an MCA student doing project on Customer Response Management with VB6 as front end and Oracle9i as back end.
I need help regarding alphabetic validation. Can u plz help me as soon as possible?

m waiting

m waiting

m waitting

m waitting

need vb frontend and backend SQL project

I need a project having vb frontend and backend as SQL ..........
I cant find it.........
So plz will u make me available any project on d given id.........
plz its very urgent ......

vb and oracle

please give me vb-oracle mini project for library management
i m in 3rd year I.T engineering

project

hiiiiiiii..........
i want source code of petrol pump management system in vb and sql back end.
plzzzzzz

vb n oracle project

i need any kind of freedownload of project having vb as front end and oracle as

VB help Pliz

Please Help
Am doing a class project of a Bar Management System with Vb 6 as front end and Sql as back end.
Its to include:
log in form
adding of new drinks
balacing stock (calculations of closing, opening, total, sold and amount in cash)
Employee Details
and calculating gross profit (given dates e.g between 12th/dec/2009 and 12th/mar/2010)
How do i go around this project and its interim report
Thanks

PLS ADVISE:

pls if you have free time if ever..pls send it to my email..
engabutero90@yahoo.com
some details about oracle..so that i can print it and study it..
plssssssss..thanks admin....

PLS ADVISE:

HI...
anyone can give me advise...?
what are the different techniques of working oracle programm?
i really want to know more about the oracle...
and i really like how to make different codes...
pls...what is the first steps?and so on?
plsssss...thanks....admin...

vb 6.0 project

i need a project with vb as front end as oracle as back end for submission as project in my final year pls help me

plz its urgent yar..
plz anyone help me na..
thoda jeha das hi devo ki kida banana hai..

this is my email id: mespecialchild@yahoo.com
es te mail kardo g project..
jina vi hega hai tuhade kol

me waiting g.. :(

vb to orcl

i am trying to access it from front end by this code (vb to oracle)rs.open "select *from t.name where no=' " &text1.text&"' dt=' "cstr(dtpicker1.value)&" ' ",con,adopenkeyset,adlockreadonly,adcmdtext

hi

i need a project with vb as front end as oracle as back end for submission as project in my final year pls help me

what kind of project u

what kind of project u need,,,,,,,,,,,,,,specify the project title

ticket reservation system-mini project

pls send me any kind of TICKET RESERVATION SYSTEM PROJECT with source code and er dia

Crystal report(8) calling from VB6 -Oracle 10g

I am facing with difficulty to open crystal report trough VB project which Db is a oracle 10g.
But it was working fine with Oracle 9i.
any comments please pst asap.
Thanks.

vb

i need to access the data's which is already stored in oracle database from my vb how it is possible.... please reply...with prior codings if possible....

computer science

Hello sir/madam
i am doing final year in computerscience i wnt some projects using sql plus back end and vb front end and also send the explanation of the project please very urgent. plz it should contain a profile of a student with in take of his/her pic .

Hello sir/madam

i am doing final year in computerscience i wnt some projects using oracle and vb and also send the explanation of the project please very urgent

VB project

Hi!!!!!!!!!!!!!!
i want a project using vb as front end and oracle as back end.... can i get?????

Connectivity with Oracle 10G

Sir,

Please tell me how we can connect our tables, which are created in oracle 10G, with VB 6.0

Please reply me. I had a lot of Problems regarding connectivity.

I want a basic project on VB using Oracle as backend.

Dear Editor
i am just start to learn VB but i have a knowledge of ORACLE and want to do a little project on the basis of Oracle as a back end, as i am told you i am not well in VB , so can you provide me a small project which is based on VB as front end using Oracle as backend.

It's your pardon to provide me .....!
Thanking you !

i want to make EXE of vb project containing oracle tables

I made a project on "opd management system" using front-end as vb6 and back-end as oracle 10g express edition. I coded very well and create vb connectivity with oracle tables on my PC. But when i run it on my college computer to show my work it displayed a connectivity error. Plz help me as soon as possible. I hope you understand my problem...

about vb & oracle connectivity project

hey , the error u r getting is cause u hav created database tables on ur pc.. bt ur college pc doesnt hav such tables.. so it failed 2 connect 2 ur college pc's ora database... u hav create the same table on col pc

use ADO for connectivity

use ADO for connectivity

Inserting values in sql database

I was working on idea to develop my own table creation UI
for sql.............
i could retrive data..........
but was stuck at inserting into table ...

help

i want project

I am doing diploma . I want a project based on either vb or oracle but little bit easier. we r six stident in group. In oracle i known only sql*plus and in vb i known vb6. And i notonly want project but i also want its description.plz help................ .I will be very thankful to u

info

hey guys if u want i can provide u a full project on a management system if u r intrsted then send me a mail

VB Project help

Hi

I can help you for creating an application in vb ..

First explain , what type of project you are going to make.

you can contact me by mail muhdshereefpa@gmail.com

Thanks

Shereef

please help for sc/st boy

Respected Sir,

I having caste certificate from SC/ST and NT (Nomadic Tribe). I wanting to do vb(Visual Basic) program for engineerng college. I need help since we are backward class. please give us the porject details on railway reservation tooling for my computer lab.

thank you sirjee,
Bhijoy G,
dahisar (west)
Near IOC petrol pumping

Help I'm CS student

Help me I have master MSC(IT) program for reservation.
I WANT IT NOW! PLZ CAN YOU HELP ME I send teh tstuf too u

pls send me shop project

pls send me shop project (vb6)
tank you

i need project wid vb 6 as frontend and oracle 10g as backend

i need project wid vb 6 as frontend and oracle 10g as backend .. i hav to submit it on 20th april... plz help me.. its too urgent..

Need a project urgent

Sir,i need a project for I.P urgently...it must have V.B as front end and Oracle as back end...plz help.

U can rply me at sonku007@gmail.com...

Array return from oracle to vb

I want to get an array from oracle procedure.I can't please help me. Note i want to return array. Not cursor.!

vb-oracle project topics

sir....
i need some latest project topics on vb-oracle urgently.... i m in 4th sem of mca. and i have to submit it as my 4th sem project. i don't want to do the common projects like students, library, etc managent systems.. so sir plz send me some latest topics.. plz...
thanking u.... pranjal

informatics practices for class12

Hiiiiiiiii! i am a student of class 12. i have got project to make income and expence record in vb using connectivity for my board examination so please kindly help me in completing my project . i have not started till now please tell me how to make form and how to do coding using connectivity please hurry up because i have to submit theproject on 25 jan 2010

railway resrvation

hello sir/mam
I am doing MSC(IT)2nd yr i want project in frontend vb and backend oracle as soon as possible
plz.... help me

you fool, dont you have ur

you fool, dont you have ur own brain ......apna dimag use kar

oracle n vb6

hello
i need a project orgently vb6 as front end and oracle as back end .........i hv to deposite it in the 3rd week of jan 2010...........can u help me............iam totally confused.........wat to do or not to do...........plz.pzl
plz
plz help me

Projects

Send me your requirements i can make the project (heartone.satya@gmail.com)

I submit my project in end

I submit my project in end of nov. my project is student information in our collage. front end is VB6 and back end is oracle

project

please give me vb-oracle project

car workshop management

plz make my project.plz i had to submit it on 3 rd feb 2010

project on music shop manaement system with vb & sql plus code

hey!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! i wana it fast till 30 jan morning.........................................
PLZ MAKE IT FAST
PLZ
PLZ
PLZ
PLZ
PLZ
PLZ

hiiii can u really hlp me2make project. . .

hiii m new 2diz forum can u hlp me 2make a project

doubt on input box

i m getting error when i cancel input box...... pls pls help me in this

i will make your project,,,

i will make your project,,, a complete project... with adodb and that to a amazing project.//
tell me how much you can pay for it?
conditions apply.....

how much

How much u expect??

school management project

i am studying in class 12 and i need school management project for my board file
i need vb in front end and oracle in back end ( linked with vb data controls only)
can anyone help me

please help me

mail me on ajeetsngh49@gmail.com

hey i m also doing the same

hey i m also doing the same project.... i m in 12
but i am getting error when i cancel input box.. pls help me

ip and oracle

iiiiiiii want a project on vb and oracle using ado on hotel management / airlines / railway.It's EMERGENCY!!!!

oracle 10g diveloper

hello
i have a problem in oracle 10g diveloper when i run the form it is apear just it's soursecode not
show me the result,please anser me more clear

please teach me how to create a log in page in vb.net

please teach me how to create a log in page in vb.net

i have to do a project -vb

i have to do a project -vb as front end ..and oracle as back end...i am a student in 12th. my topic is airline reservation system...please someone do help me with the code as fast as possible..

ORACLE/SQL COMMANDS

hey guys... I am a student doing his diploma need major help....... (PEOJECT).....
Vb as Front end and Oracle/sql as backend.....

i have to present a form like this.....

REG NO: ______
NAME:________
ASSGNMENT I:____ ASSIGNMENT II : ______ ASSIGNMENT III : ______ AVG:_______
INTERNAL I :_______ INTERNAL II :______ MODEL :_______ AVG:________
ATTENDANCE:_____ MARK : _____
INTERNAL MARK:_______ >>

ADD UPDATE DELETE SHOW :________

SOME ONE HELP ME........................

Oracle

hi,
don't worry!
first u design the form.
then use ADO control to connect with your database.
normal code is here

vb and oracle

I will make projects for you

program for daily attendance using vb,sql,pl/sql nd connectivity

i want coding of it

Project

I ll do ur pjt...

project topics using oracle and vb

i am doing final year in computerscience i wnt some projects using oracle and vb

Project

i ll do ur project..... pls send ur requirement to rajaprabhucse@yahoo.com and k_rajaprabhu@yahoo.com

hi i need a project with vb

hi
i need a project with vb as front end as oracle as back end (short and simple to understand).

projects

hi.. could u please tell me wat type of projects can i do based on oracle and visual basics.....

Problem - search

I want to search in Oracle database and want output in VB 6 form...

could u plz help~!

please respond me

splease tell me how to make the login page in vb6.please give me the code

pjt

Tel ur id i ll upload the coding....

i need a project that which

i need a project that which has Visual basic as front end and oracle as its backend.please can u help on this

Project

Hello Sir/Mam

I am in class 12th cbse ............. i need a project that which has Visual basic as front end and oracle as its backend........it should have minimum 8 forms,

so plz mail me at rohit_hot42@yahoo.com

thanking you
waiting for the reply

I Need a project with vb as a front end oracle as back end

plz porvoide3 me with it iam a high school student

need of projects in visualbasics using oracle code

hi
i am a m.sc maths studnt and i need a project in computerscience.hence i need vbprojects with oracle codes suitable for a pg student. so pl help me wit a simple free projects and send it to my mail id"bubbly_sen@rediff.com" as soon as possible
thank u
bye

thank's for your help!

thank's for your help! Sometimes, even an expert developer, forgets sintaxes!

I don't think Oracle is

I don't think Oracle is antiquated or less than SQL. However, It seems that its hard to find many Visual Basic programmers that utilize Oracle as their back end database. This is probably not surprising - considering that it is MICROSOFT Visual Basic we're talking about.

Oracle is something we must learn to deal with?

This article makes it sound like Oracle is just an antiquated DB technology that we have to deal with as if it were FORTRAN or Amiga machines. I don't know the stats, but I'd say it's a safe bet Oracle far outpaces SQL server as an enterprise database product for fortune 500 companies as well as most of the mid to large field of companies. There may be a larger count of total SQL server installations out there, but oracle makes up more core data systems. The insinuation that SQL server is top dog and Oracle is just some tool we have to learn to deal with is plain laughable.