Calling a stored proc with ADO

[tsherfy]

We have the following stored procedure that runs fine in SQL Server 2000 Query Analyzer.

/*
** Retrieve next available Request ID
*/
CREATE procedure dbo.sp_GetRequestID2

@output int OUTPUT

as

/* begin transaction */

select @output = (select NextID from RequestID)

update dbo.RequestID set NextID = (NextID + 1)

/* commit */

Return 99
GO

However, when calling the proc from VB with the code below, we are not getting any results. Any ideas?

Dim strTest As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim param1 As Parameter
Dim param2 As Parameter

Set cmd = New ADODB.Command

cmd.ActiveConnection = "Provider=SQEDB.1;Password=mypassword;Persist Security Info=True;User ID=myuser;Initial Catalog=HVDB;Data Source=TESTSERVER"

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_GetRequestID2"

' Set up a return parameter.
Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append param1

' Set up an output parameter.
Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput)
cmd.Parameters.Append param2

Set rs = cmd.Execute

strTest = cmd(1)

Thanks!

 

[chrisinto]

You might try to use adParamInputOutput rather than adParamOutput.
I use this syntax to declare an output param:

CreateParameter("@Id", adInteger, adParamInputOutput, 4, lId)

then retrieve it using

lId = objcmd.Parameters("@Id&quot.Value

Hope this helps

 

[tsherfy]

We have resolved this issue. See thread 183-371365 for the resolution. Thanks!