slightly OT: calling Oracle SP from VB

From: Cynic <asdf>
Date: Mon, 21 Oct 2002 12:24:33 -0400
Message-ID: <ur8ai1b4sflnac@corp.supernews.com>

I have posted a couple of times already about this issue, but after an entire weekend of working on this, I still am stumped. I have simplified my problem and now know exactly when it doesn't work:

Here's my test proc:
CREATE OR REPLACE PROCEDURE MY_PROC

   (P_PART_NO        IN NUMBER,
    P_PART_REV       IN VARCHAR2,
    R_PART_NO       IN OUT NUMBER,
    R_PART_REV       IN OUT VARCHAR2)

IS

BEGIN   R_PART_NO := P_PART_NO;
  R_PART_REV := P_PART_REV; END MY_PROC; In order for my vb code to work, I can't assign a value to the IN OUT params, so basically I have to treat them like OUT params. Otherwise, on the Command.Execute method, I get an unhelpful error message, "errors have occurred".

Here's my code:

---

    Dim adoConn As ADODB.Connection
    Dim cmdSQL As New ADODB.Command

    Set adoConn = New ADODB.Connection
    sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

    With adoConn

       .ConnectionString = sConnect
       .CursorLocation = adUseClient
       .Open

    End With

    With cmdSQL

        .CommandTimeout = 120
        .ActiveConnection = adoConn
        .CommandText = "MY_PROC"
        .CommandType = adCmdStoredProc

    End With

    Dim pPartNo As Integer
    Dim pPartRev As String
    Dim rPartNo As Integer
    Dim rPartRev As String

    cmdSQL.Parameters.Refresh
    MsgBox cmdSQL.Parameters.Count ' confirms 4 params

    pPartNo = 23813
    cmdSQL.Parameters(0).Value = pPartNo     pPartRev = "mypartrev"
    cmdSQL.Parameters(1).Value = pPartRev

' **** NOTE: HERE IS WHERE I RUN INTO PROBLEMS. EVEN THOUGH
' **** THESE PARAMS ARE IN/OUT, IF I UNCOMMENT THESE, THE
' **** COMMAND.EXECUTE METHOD BOMBS.
'cmdSQL.Parameters(2).Value = rPartNo
'cmdSQL.Parameters(3).Value = rPartRev

' << used to confirm the param vals, type and direction. works fine.
    Dim s As String
    Dim t As String
    Dim d As String
    Dim iCountParams As Integer
    For iCountParams = 0 To (cmdSQL.Parameters.Count - 1)

        s = s & cmdSQL.Parameters(iCountParams).Name & " = " & cmdSQL.Parameters(iCountParams).Value & vbCrLf

        t = t & cmdSQL.Parameters(iCountParams).Name & " = " & cmdSQL.Parameters(iCountParams).Type & vbCrLf

        d = d & cmdSQL.Parameters(iCountParams).Name & " = " & cmdSQL.Parameters(iCountParams).Direction & vbCrLf

    Next iCountParams

    MsgBox s
    MsgBox t
    MsgBox d

' >>

' this cmdSQL.Execute method just gives me a vb error - "errors have
occurred"

' unless, of course, I comment the 2 lines above that assign values to
the in/out variables.

    cmdSQL.Execute

    MsgBox cmdSQL.Parameters(2).Value & " - " & cmdSQL.Parameters(3).Value

    adoConn.Close
    Set adoConn = Nothing

---

Received on Mon Oct 21 2002 - 11:24:33 CDT