Missing Output parameters from stored procedure

[avanderlaan]

I am trying to get some return values from a stored procedure but they come back empty. Can someone tell me where I went wrong? The parameters are in the cmd but are empty upon return. The stored procedure executes correctly, ignoring that there is no output. Thank you.

======== Calling code
Dim cmd as New ADODB.command
Dim rs as New ADODB.Recordset
Dim parm As Parameter

cmd.ActiveConnection = serverSideCursorConnection
cmd.CommandText = "storedProc"
cmd.CommandType = adCmdStoredProc
Set parm = cmd.CreateParameter("testId", adInteger, adParamOutput)
cmd.Parameters.Append parm
... more parms
Set rs = cmd.execute

========= Stored procedure
CREATE PROCEDURE dbo.storedProc
@testId int OUTPUT,
... more parms
AS
... Select @testId = testId from table
GO

 

[rdeleon]

I don't know if this will solve your problem, but I ran into a similar problem when I was using a temp table to bring back data.

I included the statement

SET NOCOUNT ON

at the beginning of the procedure and this cleared it up. I do not completely understand why I needed the statement. Something to do with VB getting confused with the data that was returned from SQL Server.

Give it a shot.

Rene'

 

[avanderlaan]

Rene,

This didn't seem to change anything. I have a bit more info now however. In the first cmd parameter, the label is set to &quot;@Return Value&quot; and its value equals the value of my first output parameter. The second parameter has a label that matches my first output parm, but its value equals Null. The third parm is my second output parm and the value is <empty>. The stored procedure does not do a return and there is no return value specified in the parameter collection I created.

The examples I've seen do not specify a return value in the stored proc.

Thanx.

 

[rdeleon]

I think that you have found your own problem. The stored procedure is only getting one of you parms. I think that the RETURN_VALUE parameter is a default (not sure), when you call stored procedures from VB.

Are you using a DataEnvironment object? It sure helps me keep my connections straight and the parms needed for stored procedures.

You might try creating the RETURN_VALUE parm with a null value and then create the rest of your parms.

Rene'

 

[avanderlaan]

Answer to this problem was to use &quot;client-side&quot; cursors in the connection object.