help in SQL *plus and VB6 conection

[Anupkumar]

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

[choudhuryshouvi]

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

[debasisdas]

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 .