VB6 Ado stored procedure retrieve output variable

[lchase]

Hi all,
I am looking for a code snippet demonstrating retrieving a output variable and/or return value from a stored procedure.
I have code to do it with in sql query analyizer but can't find any for vb 6 and ado using an output parameter.
I already have plenty using recordsets.
Thanks in advance
Len

 

[bboffin]

Essentially all you need to do is use an ADODB Command object to pass the parameters to the stored procedure including the OUTPUT parameter (set the Direction of this parameter to adParamOutput) and after executing the stored procedure this parameter will contain what you want.

Dim lobjCmd as New ADODB.Command
With lobjCmd
    .ActiveConnection = mobjConnection ' set up elsewhere
    .CommandType = adCmdStoredProcedure
    .CommandText = "MyStoredProcedure"
    .Parameters.Append .CreateParameter("MyOutputParam",adInteger,adParamOutput)
' add other parameters as required
    .Execute
    Debug.Print .Parameters("MyOutputParam")

End With

 

[lchase]

Thanks that solved one problem for me I was executing the command like
Set adoRecordset = adoCommand.Execute
When I dumped the recordset that part works. How about the return value of a stored procedure. I understand it must be integer and usually contains an error value returned
from sql server.
Here is the demo stored procedure

=========================================
alter procedure getProjectName @SupID char(2),@SName Varchar(50) output
--select * from suppliers
as
declare @errorNum int
set @errorNum =0
--select @SName = supplierid from suppliers
select @SName = Project_Name from Project
where project_no=@SupID
select emp_no,job,project_no from works_on
where project_no=@SupID
Print 'Execution Complete' +@SName
if(@@ERROR <>0)
set @errorNum = @@ERROR
else -- set to 99 for OK
set @errorNum =99
return @errorNum --VAULE I WISH TO RETRIEVE
=====================================
Len

 

[johnwm]

'do whatever to set up as before. When you're ready:

.Execute
Debug.Print .Parameters(&quot;@errorNum&quot

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[lchase]

When I tried that I got this error
#3265
Item cannot be found in the collection
Is it becausse it is a return value and not a parameter.
When I tried to add it as a parameter It told me I had too many.
Here is the ado code:
Dim strOutput As String
Dim strAnswer As String
Dim intRtn As Integer
Set adoCommand = New Command
adoCommand.CommandText = &quot;GetProjectName&quot;
adoCommand.CommandType = adCmdStoredProc
adoCommand.Name = &quot;adoCommand&quot;
'firstPraram
Set adoparam = New Parameter
adoparam.Name = &quot;@SupID&quot;
adoparam.Type = adChar
adoparam.Size = 2
adoparam.Value = txtInput.Text
adoCommand.Parameters.Append adoparam
'second param
Set adoparam2 = New Parameter
adoparam2.Name = &quot;@SName&quot;
adoparam2.Type = adVarChar
adoparam2.Size = 50
adoparam2.Direction = adParamOutput
'adoparam2.Value=
adoCommand.Parameters.Append adoparam2
' Set adoparam3 = New Parameter
' adoparam3.Name = &quot;@errorNum&quot;
' adoparam3.Type = adInteger
' ' adoparam3.Size = 50
' adoparam3.Direction = adParamReturnValue
' 'adoparam2.Value=
' adoCommand.Parameters.Append adoparam3
Set adoCommand.ActiveConnection = adoConnect

' Set adoRecordset = 'use this for recordset return
adoCommand.Execute
'' Debug.Print adoCommand(&quot;@errorNum&quot 'return value
' adoCommand.Parameters.Refresh
' strAnswer = adoCommand.Parameters(0).Value
strAnswer = adoCommand.Parameters(&quot;@SName&quot.Value

'intRtn = adoCommand.re
Set adoRecordset = adoCommand.Execute
txtProjectName = strAnswer
FillList (listbox1)
strOuput = adoparam2.Value
adoRecordset.Close

 

[johnwm]

Did you set up the parameter to receive a return value?

Set param1 = cmd.CreateParameter(&quot;Return&quot;, adInteger, adParamReturnValue)


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[lchase]

John,
here is what I tried are you saying I need to name the variable &quot;Return&quot;
Thanks Len
Set adoparam3 = New Parameter
' adoparam3.Name = &quot;@errorNum&quot;
' adoparam3.Type = adInteger
' adoparam3.Direction = adParamReturnValue
' adoCommand.Parameters.Append adoparam3

 

[lchase]

I found the problem.
If you want to use a return value parameter.
It must be the first added to the parameter collection.
Thanks for the assistance