Stored Procedure return parameter question

[alan12345]

Hi Everyone;

I have a question about my code:

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

Dim adoRS As New ADODB.Recordset

Dim ParamI1 As New ADODB.Parameter
Dim ParamI2 As New ADODB.Parameter
Dim ParamO As New ADODB.Parameter
Dim ParamR As New ADODB.Parameter

adoConn.Open "Provider=SQLOLEDB;Data Source=RING01;Initial Catalog=TestRAS;Trusted_Connection=Yes"

adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = "SP_TestExample"
adoCmd.CommandType = CommandTypeEnum.adCmdStoredProc
adoCmd.CommandTimeout = 300

'Define the stored procedure's Return Value
Set ParamR = adoCmd.CreateParameter("@ReturnCode", adInteger, adParamReturnValue)
adoCmd.Parameters.Append ParamR

Set ParamO = adoCmd.CreateParameter("@ReturnDescription", adVarChar, adParamOutput, 100)
adoCmd.Parameters.Append ParamO

' Define the stored procedure's input parameters
Set ParamI1 = adoCmd.CreateParameter("@Name", adVarChar, adParamInput, 30, Null)
adoCmd.Parameters.Append ParamI1

Set ParamI2 = adoCmd.CreateParameter("@assoc", adVarChar, adParamInput, 3, "120")
adoCmd.Parameters.Append ParamI2


adoCmd.Execute ' to get error msg.

If adoCmd.Parameters(0).Value <> 0 Then
MsgBox adoCmd.Parameters(1).Value
Else
List.Clear
Set adoRS = adoCmd.Execute
adoRS.MoveFirst
Do Until adoRS.EOF
List.AddItem adoRS("AR2_Life_CO") & "--" & adoRS("AR2_PREMIUM")
adoRS.MoveNext
Loop
End If

Set adoCmd = Nothing

adoConn.Close
Set adoConn = Nothing


Please notice that I have towards the end, the following:


adoCmd.Execute ' to get error msg.

If adoCmd.Parameters(0).Value <> 0 Then
MsgBox adoCmd.Parameters(1).Value
Else
List.Clear
Set adoRS = adoCmd.Execute
adoRS.MoveFirst
Do Until adoRS.EOF
List.AddItem adoRS("AR2_Life_CO") & "--" & adoRS("AR2_PREMIUM")
adoRS.MoveNext
Loop
End If


I would like to do the Set adoRS = adocmd.execute only once but if I were to do that, and then check the return value adoCmd.Parameters(0).Value and the output parameter adoCmd.Parameters(1).Value they would both be null empty.

When I do a separate adocmd.execute, I get my proper return value (adoCmd.Parameters(0).Value) of 0 and the output parameter (adoCmd.Parameters(1).Value) has a value of "No Errors."

Any idea why that happens?

Thanks!
Alan