Hello,
I have installed VB6 and SQL * plus (Oracle 8i). I want to connect VB6 and Oracle. That is, I want to preapare an application where in, if I enter a value in the text box, it should get appended in the table in SQL.

I know how to insert an element into the table using SQL query but do not know how to execute a SQL query using VB6 as front end.:(

So, can someone help me. pLease.......

Also, Suppose I want execute a query like
SELECT name, age
FROM DOCTOR_TABLE
WHERE age>35;

In the above query it list name and age of all the doctors in the table DOCTOR_TABLE whose age is greater than 35.

I know all the SQL query stuff, but unable invoke a query using VB.:(

So, If if anyone know how to connect VB with SQL can help, so that I can continue my work........... :S
:S

before you do what u wish u need to create an odbc dsn connection for ur ora8i database to connect it with vb6 and execute ur query statements. hope u know how to create the dsn.

after creating it,

use the following code to connect to the database from ur vb6 apps:-
**add the reference "Microsoft Activex Data Object 2.7 Library" to ur project. to add it goto project,then reference,scroll down,select it and click ok.**

Dim gCn As New adodb.Connection
Dim str As String

On Error GoTo err1

Set gCn = New adodb.Connection
str = "DSN=<your dsn name>;UID=<your db user id>;PWD=<youe db password>;DBQ="
gCn.ConnectionString = str
gCn.Open

Exit Sub
err1:
Err.Clear
Msgbox "Error in creating connection with the database."
Set gCn = Nothing
End

use the following code to configure ur recordset with the query string that u pass:-

Public Function GetResult(Cstr1 As String, typ As Integer, loc As Integer, clock As Integer) As adodb.Recordset
Dim adrs As adodb.Recordset

Set adrs = New adodb.Recordset

On Error GoTo AdoErr

adrs.CursorLocation = loc
adrs.CursorType = typ
adrs.LockType = clock
adrs.Open Cstr1, gCn

Set GetResult = adrs
Set adrs = Nothing

Exit Function

AdoErr:
MsgBox "Error : " & Err.Description, vbExclamation, "OraDB"
Err.Clear
Set GetResult = Nothing
Resume Next
End Function

use the following code to execute ur query :-

Dim str As String
Dim rs As New adodb.Recordset

str = "SELECT name, age FROM DOCTOR_TABLE WHERE age>35"
Set rs = GetResult(str, adodb.adOpenDynamic, adodb.adUseClient, adodb.adLockOptimistic)

if rs.Recordcount>0 then
msgbox "record found"
else
msgbox "no record found"
endif

It is always recommended to use

Oracle Provider for OLE DB
or
Microsoft OLE DB provider for Oracle
not using the DSN
for connection to Oracle from VB .

This article has been dead for over six months. Start a new discussion instead.