|
|
Posted by Gert-Jan Strik on 11/30/06 19:34
jbonifacejr wrote:
>
> 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 value returned to
> the ASP from a stored procedure. 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?
Just a few pointers here:
- creating a parameter will just create a parameter. To use it, you need
to add it to the command object using oSProc.Parameters.Append
- in a stored procedure you can only use the RETURN keyword to return an
integer, so @sRetUPC is out of the question
- if you want to use the value of the output parameter, then you should
access it through the Parameters collection of the Command object. The
syntax you are currently using refers to the resultset, but the stored
procedure does not have one
HTH,
Gert-Jan
[Back to original message]
|