Returning Values from SP's in ADO 2

[Sameal]

I am having trouble getting the return values on my stored procedure working in VB using ADO. Currently I have...

---- Start VB Code
Public Function EmployeeSelectByID(intID As Integer)
As Recordset
On Error GoTo Err_EmployeeSelectByID

'Use the global ADO command object.
Set g_objCmd = New ADODB.Command

'Setup the command object.
Set g_objCmd.ActiveConnection = g_objConn
g_objCmd.CommandText = "procEmployeeSelectByID"
g_objCmd.CommandType = adCmdStoredProc

'Append the parameters into the command object.
g_objCmd.Parameters.Append
g_objCmd.CreateParameter("Return", adInteger, _
adParamReturnValue)
g_objCmd.Parameters.Append
g_objCmd.CreateParameter("EmployeeID", adInteger, _
adParamInput, , intID)

'Now execute our command and return recordset result.
Set EmployeeSelectByID = g_objCmd.Execute

MsgBox "Return: " & g_objCmd.Parameters("Return&quot

Exit_EmployeeSelectByID:

'Remove references to objects.
Set g_objCmd = Nothing

Exit Function

Err_EmployeeSelectByID:

LogError "modRead.EmployeeSelectByID()"

End Function
---- End Code

My stored procedure looks like this..

---- Start Stored Procedure
CREATE PROCEDURE procEmployeeSelectByID

@EmployeeID as int

AS

--Declare the output variable
DECLARE @Return int

--Get all the records
SELECT * FROM tblEmployee
WHERE EmployeeID = @EmployeeID

--Get the RecordCount so we can return it to the application
SELECT @Return = @@rowcount

--Look for errors
IF @@error > 0

SELECT @Return = -1
RETURN @Return
GO
---- End Stored Procedure

My problem is that the return value is coming back as "". Does anyone see any errors in the code that would cause this?

 

[moonshadow]

Don't append the parameters. As soon as you say
g_objCmd.CommandType = adCmdStoredProc
ADO automatically connects to the server and asks what parameters are required. It then creates them for you. I think you're creating additional parameters which aren't used.

 

[Sameal]

How would I pass in my intID then? Could you post a short snipped of code as an example to what your suggesting? Also how would I reference my return value?

The VB code and stored procedure return my recordset fine which is then used to populate a form which is working. The only part that is not working is when I reference my Return value, it returns "" instead of the return value.

I executed my stored procedure in the SQL Query Analyzer using the following SQL:

---- Begin SQL
declare @Return int
execute @Return = procEmployeeSelectByID 4
select @Return as 'return'
---- End SQL

When executed it works perfectly. But when trying to implement this into VB(ADO) the return value is lost.

 

[cmmrfrds]

Your parameter setup looks good, but to get both a recordset back from the stored procedure and a return value you need to close the recordset first.

rs.close
MsgBox "Return: " & g_objCmd.Parameters("Return&quot

 

[tlbroadbent]

The Return value in a SQL SP is not used for returning parameters or variables. It is a status or RETURN CODE.

You must declare a parameter variable as OUTPUT when creating the SP and then use the parameter when making the call.

---- Start Stored Procedure
CREATE PROCEDURE procEmployeeSelectByID
@EmployeeID as int,
@Return int OUTPUT

AS

SET NOCOUNT ON

--Get all the records
SELECT * FROM tblEmployee
WHERE EmployeeID = @EmployeeID

--Get the RecordCount so we can return it to the application
SELECT @RETURN = @@rowcount

SET NOCOUNT OFF

GO

Example: Call the SP
Exec procEmployeeSelectByID 100000, @count OUTPUT

I don't use ADO so I can't provide the code for calling the SP from VB. However, this should help you understand how to use OUTPUT parameters. Check SQL Books Online (SQL BOL) for more details. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[tlbroadbent]

Take a look at these resources for samples of ADO, stord procs and OUTPUT params.

http://www.geocities.com/priyadarshu/tech/Ado_vb.htm

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6195&pg=1

http://www.15seconds.com/issue/010718.htm

http://www.vb-zone.com/upload/free/features/vbpj/1999/05may99/ds0599/ds0599.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.