Oracle Stored Procedure using ADO(VB)..............?????????????????

With CPw1
    .ActiveConnection = Conn
    .CommandText = "{call .......... (?,{.............})}"
    .CommandType = adCmdText '= adCmdStoredProc
    .Parameters.Append .CreateParameter(, adVarChar, adParamInputOutput)
End With

Cpw1.CommandText = "{call .......... (?,{.............})}"
                                             ^                 ^
                                             |                 |
                                           HELP          HELP

Hope the following helps...

Regards,
Matt Houseman

'***************************************************************************
**
'*  Application Name    :   <MY_APPLICATION>
'*
'*  Application Area    :   Unit Test
'*
'*  Form Name           :   frmFindMo (find_mo.frm)
'*
'*  CopyRight:          :   <MY_CLIENT>
'*
'*  Description:        :   This application tests the stored procedures:
'*                          1. pkg_period.find_mo
'*
'*
'*
'*   SubRoutines        :   Command1_Click
'*
'*
'* $History: $
'*
'***************************************************************************
**
Sub Command1_Click()

    Dim mConn As ADODB.Connection
    Dim mrsSelect As ADODB.Recordset
    Dim mCmd As ADODB.Command
    Dim msSelect As String
    Dim mCmdPrm As New ADODB.Parameter
    Dim sConnect As String

    '*******************************
    '* Open a database connection. *
    '*******************************
    sConnect = "Data Source=<MY_DS>;User ID=<MY_UID>;Password=<MY_PWD>;"
    Set mCnn = New ADODB.Connection
    With mCnn
        .CommandTimeout = 10
        .CursorLocation = adUseClient
        .Provider = "MSDAORA"
        .Open sConnect
    End With

    '****************************************
    '* Create the stored procedure context. *
    '****************************************
    msSelect = "{call pkg_period.find_mo (?, { resultset 1000, per_id,
per_nm } ) }"
    Set mCmd = New ADODB.Command

    '*********************************
    '* Create the parameter context. *
    '*********************************
    With mCmd
        .CommandText = msSelect
        .CommandType = adCmdText
        .ActiveConnection = mCnn
        Set mCmdPrm = .CreateParameter("IN_qty", adInteger, adParamInput, 1,
1)
        .Parameters.Append mCmdPrm
    End With

    '**********************************
    '* Dispatch the stored procedure. *
    '**********************************
    Set mrsSelect = New ADODB.Recordset
    Set mrsSelect = mCmd.Execute

    '********************
    '* Display results. *
    '********************
    MsgBox mrsSelect.Fields(1)
    mrsSelect.MoveNext

End Sub

For an in-depth article on retrieving ADO resultsets from Oracle have a
look at http://www.digitaltier.com/knowledge/

Jason

Sent via Deja.com http://www.deja.com/
Before you buy.