locked
item cannot be found in the collection corresponding to the requested name or ordinal in VBA RRS feed

  • Question

  • Guys,

    I'm trying call an oracle stored procedure in vba. But when I tried catch the output parameters I've got the error in my recordset: "item cannot be found in the collection corresponding to the requested name or ordinal". Can Anyone help me? What is wrong? Below is my code.

    Thanks..!!

       On Error Resume Next
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim MyCommand As New ADODB.Command

        Err.Number = 0
        cn.CursorLocation = adUseClient
        cn.ConnectionString = ConnectionString.N3
        'Abre Conexao
        cn.Open

        If Err.Number = 0 Then

            Set MyCommand = New ADODB.Command

            With MyCommand
                Set .ActiveConnection = cn

                .CommandText = "sp_new_data"
                .CommandType = adCmdStoredProc

                .Parameters.Append .CreateParameter("vs_system", adVarChar, adParamInput, , "system")
                .Parameters.Append .CreateParameter("vs_operator", adVarChar, adParamInput, , "Alex")
                .Parameters.Append .CreateParameter("vs_Msg_Error", adVarChar, adParamOutput, 256)

                rs.Open MyCommand, , adOpenDynamic, adLockBatchOptimistic

                Set rs = .Execute

               MsgBox "Value is: " & rs(0)

            End With

        rs.Close

        Else

            MsgBox ("Database is not avalible. Errror: " & Err.Description)

        End If

        cn.Close


        End Sub

                                                                                                                
    Wednesday, October 17, 2012 11:46 AM

All replies

  • Try to add name of package to command text

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved


    Wednesday, October 17, 2012 7:33 PM
    Answerer
  • I solving this problem... It was missing the field size, I changed it and worked... ;)

    .Parameters.Append .CreateParameter("vs_system", adVarChar, adParamInput, 8, "system")
    .Parameters.Append .CreateParameter("vs_operator", adVarChar, adParamInput, 8, "Alex")
    .Parameters.Append .CreateParameter("vs_Msg_Error", adVarChar, adParamOutput, 500)

    Thanks..

    Wednesday, October 17, 2012 8:05 PM