Can someone explain why my code is not working

Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code


Here is the code in my stored proc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@sArtist varchar(50),
@sRetArtist bigint OUTPUT
AS
BEGIN
SET @sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@sArtist)
END


I am calling it like this....

Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global

sProc.Parameters.Append(sProc.CreateParameter("@sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))


sProc.Parameters.Append(sProc.CreateParameter("@sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))

sProc.Execute()
iArtSer = sProc("@sRetArtist).Value

.

Relevant Pages

  • Re: Can someone tell me where to post a code question for the following
    ... am unable to get a return value from a stored procedure in .NET using ... the following Sproc and .NET code ... Dim iArtSer As Integer ... Dim sProc As ADODB.Command ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Retrieving SPROC return value using SPT
    ... You cannot use the parenthesis in calling the stored procedure. ... Your parameter must be declared as OUTPUT inside your stored procedure. ... It still not updated after return from> sproc. ... The "@" seems to be sufficient to tell> SQL>> that it's an output parameter. ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: SqlDataAdapter - can it be (re)used for 2 SELECT statements
    ... dataadapter for 2 different calls to the same sproc, ... the stored procedure parameter ... //create our DataAdapter object and use it to fill the dataset object ...
    (microsoft.public.dotnet.framework.aspnet)
  • Can someone tell me where to post a code question for the following
    ... am unable to get a return value from a stored procedure in .NET using ... the following Sproc and .NET code ... Dim iArtSer As Integer ... Dim sProc As ADODB.Command ...
    (microsoft.public.dotnet.languages.vb)
  • Im so confused
    ... normally from Query Analyzer rather than calling it from any other code so I ... > system stored procedure sp_grantdbaccess checks that there is not an ... > there is not an enclosing transaction. ... Here's my sproc and the ...
    (microsoft.public.sqlserver.security)