DESPERATE - Need help getting a SQL stored procedure output parameter to my .NET app

microsoft.public.sqlserver.programming

30 Nov 2006 7:17 PM

jbonifacejr

Hi. I'm sorry to bother all of you, but I have spent two days looking
at code samples all over the internet, and I can not get a single one
of them to work for me. I am simply trying to get a stored procedure
output parameter to be stored in a variable in my ASP code.

The error I am getting is: Item can not be found in the collection
corresponding to the requested name or ordinal.

Here is my Stored Procedure code.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
ALTER PROCEDURE [dbo].[sprocRetUPC]
    @sUPC varchar(50),
    @sRetUPC varchar(50) OUTPUT

AS

BEGIN
    SET NOCOUNT ON;
    SET @sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =

@sUPC)
    RETURN @sRetUPC

END

Here is my ASP.NET code.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

Dim oConnSQL As ADODB.Connection

oConnSQL = New ADODB.Connection
oConnSQL.ConnectionString = "DSN=BarcodeSQL"
oConnSQL.Open()

Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveConnection = oConnSQL
oSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
oSproc.CommandText = "sprocRetUPC"

Dim oParam1
Dim oParam2
oParam1 = oSproc.CreateParameter("sRetUPC",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 50)
oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")

Dim res
res = oSproc("sRetUPC")

Response.Write(res.ToString())

End Sub

If I put the line -
              oSproc.Execute()

above the "Dim res" line, I end up with the following error:
Procedure or function 'sprocRetUPC' expects parameter '@sUPC', which
was not supplied. I thought that oParam2 was the parameter. I was also
under the assumption that the return parameter has to be declared
first. What am I doing wrong here?