How to call the Stored Procedure in Oracle by using VB 6.0

[qn18]

I am new in Visual Basic programming. I have the following problem, hopefully you can help me to solve it. Thank you very much.

Suppose that I have a Contact table and a Stored Procedure that called sp_AddContact, they are both created in Oracle. When users input the ClientID, CompanyName, Address, Telephone from GUI, these informations will be added into the Contact table by calling the sp_AddContact Stored Procedure. Could you please show me how to WRITE the program to call the Stored Procedure in Oracle to insert the data into the Contact table? (I am using VB 6.0)

Thanks
QN

[JohnAtWork]

I'll look it up and post it in a little while; a great book that is absolutely a must-have for you:

Oracle Programming with Visual Basic by Nick Snowden
http://www.amazon.com/exec/obidos/ASIN/0782123228/qid%3D951245516/102-6309532-6586467

ALso, what kind of connection are you using?
DAO, ADO, ODBC direct?

[qn18]

ALso, what kind of connection are you using?
DAO, ADO, ODBC direct?
--
Hi John

I am using ODBC to connect

[JohnAtWork]

Do you have the syntax for the Stored Procedure?

This is pretty important.

I'm assuming it's as follows:

sp_AddContact(ClientID, CompanyName, Address, Telephone)

Now where is the data coming form on the GUI? Text boxes in your VB application? Assume they're called txt1 - txt4

If so, the code will look like the following:

Private qryAddClient as Querydef

Private Sub spAddClient()

Dim strSQL as String
On Error GoTo ErrorHandler

strSQL = "{call OracleInstance.sp_AddContact( " & txt1 & ", " & txt2 & ", " & txt3 & ", " & txt4 & ")}"

Set qryAddCLient = cn.CreateQueryDef(" ", strSQL)

Exit Sub

ErrorHandler:
If Err = 3146 Then
MsgBox "Error creating query : " & Errors(0).Description
Else
MsgBox "Error creating query : " & Err.Description
End If

End Sub


I swiped this from a different example in a book (I don't work much with SPs at this point; it's my next jump) so this might fail miserably.

One thing: OracleInstance is your Oracle instance; I.e. don't type OracleInstance

Let me know if this works out for you.

One last thing: cn will be your connection string to the database. I didn't declare it in the module at all. .

Edited by JohnAtWork on 02-23-2000 at 04:24 PM