SP not returning output

[champagne_charly]

I'm having issues with the following whereby i am not getting a value for the output param.

        Public Function SPExec(SPName As String, NoOutPar As Integer, NoInPar As Integer, ByRef INPARAM() As Object, Optional ByRef arrOutputValues() As Object = Nothing) As Object            Try


                Dim ip As Integer
                Dim op As Integer




                Dim prm As New ADODB.Parameter


                Dim cnn As ADODB.Connection


                Dim cmd As ADODB.Command


                cnn = New ADODB.Connection


                cnn.Open("PROVIDER= sqloledb.2 ;DATA SOURCE=************** ;UID=SA;PWD=**************;INITIAL Catalog= **************;")




                cmd = New ADODB.Command




                With cmd


                    .ActiveConnection = cnn


                    .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc 'adCmdStoredProc


                    .CommandText = SPName






                    'Dim dt As datatype
                    'Dim Sz As Long


                    For ip = 0 To NoInPar - 1




                        If Len(INPARAM(ip)) > 8000 Then




                            .Parameters.Append(.CreateParameter("@param" & ip.ToString, ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, -1, INPARAM(ip))) ' CreateParameter("@param" & ip, ADODB.DataTypeEnum.adLongVarWChar, adParamInput, -1, INPARAM(ip))
                        Else




                            .Parameters.Append(.CreateParameter("@param" & ip.ToString, ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, 8000, INPARAM(ip)))


                        End If


                    Next ip


                    'Create Outputurn parameter for the output value


                    ReDim arrOutputValues(0 To NoOutPar - 1)


                    For op = 0 To NoOutPar - 1


                        'prm = .CreateParameter("@Output" & op.ToString, ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamOutput, 4000)
                        '.Parameters.Append(prm)




                        .Parameters.Append(.CreateParameter("@Output" & op.ToString, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 8000, arrOutputValues(op)))




                    Next




                    .Execute()


                    If NoOutPar >= 1 Then
                        MsgBox("Out Param >1")
                        ' ReDim arrOutputValues(0 To NoOutPar - 1)
                        ip = 0




                        'For ip = 0 To NoInPar - 1
                        '    Debug.Print("**************   Param Index:" & ip & "   **********************************")


                        '    Debug.Print(.Parameters(ip).Value)




                        ' Next


                        Debug.Print("*******************  8  *****************************")
                        Debug.Print(.Parameters(8).Value)
                        Debug.Print("****************  prm  ********************************")
                        '  Debug.Print(prm.Value)
                        Debug.Print("************************************************")
                        For op = 0 To NoOutPar - 1






                            Debug.Print("Get Value array with index of " & op + NoInPar)
                             arrOutputValues(op) = .Parameters(op + NoInPar).Value
                            ' arrOutputValues(op) = .Parameters(op + NoInPar).Value
                        Next




                        MsgBox(arrOutputValues(0))
                    End If




                End With
                cnn.Close()


                cmd = Nothing




            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try




        End Function

Any ideas where I am going wrong? The SP completes correctly and when run from MSSQL execut with the same input param values a vlaue for the Output is shown.


Please excuse all the debugs its jsut me trying to find where the issue lies

 

[champagne_charly]

Turns out the issue was not with the VB side or at least not completely.. However it was that the SP that was being executed returned recordsets also.
Not sure what to do to avoid this happening in the future.

 

[jmcilhinney]

Not sure what to do to avoid this happening in the future.

Perhaps write an SP that actually does what you want. If you would like more information then it would be useful to show us the SQL code and explain what it is that actually want to achieve.

By the way, you have posted this topic in the ADO.NET forum and yet you're not using ADO.NET. Are you aware that you are actually using VB6 data access technology? If you're going to use VB.NET then you should ditch ADO and actually use ADO.NET.

 

[champagne_charly]

Perhaps write an SP that actually does what you want. If you would like more information then it would be useful to show us the SQL code and explain what it is that actually want to achieve.

By the way, you have posted this topic in the ADO.NET forum and yet you're not using ADO.NET. Are you aware that you are actually using VB6 data access technology? If you're going to use VB.NET then you should ditch ADO and actually use ADO.NET.

Thank you for pointing out that i was using the wrong connection method. I was oblivious to the same. The move to ado.net has clarified why I was getting the errors and reading up on the command type also explained how to avoid.
I have now updated this and am updating the use of arrays to collections for ease of use as the dimensional array just wont work.

Thanks