Calling a stored proc from VB

[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=SQLOLEDB.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!

 

[cmmrfrds]

Are you getting any results? How about return code.

debug.print cmd(0)

Do you know that it is executing the SP.

Normally, you should have dbo on the SP unless you are logged in as the owner or sa.


cmd.CommandText = "dbo.sp_GetRequestID2"

 

[essa2000]

Use SET NOCOUNT ON in the beginning of SQL Statements after the keyword AS and use SET instead of Select.

CREATE procedure dbo.sp_GetRequestID2

@output int OUTPUT

as

SET NOCOUNT ON

/* begin transaction */

SET @output = (select NextID from RequestID)

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

/* commit */

Return 99
GO


Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[tsherfy]

Thanks to all who responded. We were able to correct our mistakes based on Muhammed's post.