Can someone explain why this stored proc does not work?

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).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> 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
>|||Hi
"jbonifacejr" wrote:

> 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
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John