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_IDENTIFI ER 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.EventArg s) Handles Me.Load
Dim oConnSQL As ADODB.Connectio n
oConnSQL = New ADODB.Connectio n
oConnSQL.Connec tionString = "DSN=BarcodeSQL "
oConnSQL.Open()
Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveCo nnection = oConnSQL
oSproc.CommandT ype = ADODB.CommandTy peEnum.adCmdSto redProc
oSproc.CommandT ext = "sprocRetUP C"
Dim oParam1
Dim oParam2
oParam1 = oSproc.CreatePa rameter("sRetUP C",
ADODB.DataTypeE num.adVarChar,
ADODB.Parameter DirectionEnum.a dParamOutput, 50)
oParam2 = oSproc.CreatePa rameter("sUPC", ADODB.DataTypeE num.adVarChar,
ADODB.Parameter DirectionEnum.a dParamInput, 50, "0433960053 96")
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? 8 3227
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_IDENTIFI ER 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.EventArg s) Handles Me.Load
Dim oConnSQL As ADODB.Connectio n
oConnSQL = New ADODB.Connectio n
oConnSQL.Connec tionString = "DSN=BarcodeSQL "
oConnSQL.Open()
Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveCo nnection = oConnSQL
oSproc.CommandT ype = ADODB.CommandTy peEnum.adCmdSto redProc
oSproc.CommandT ext = "sprocRetUP C"
Dim oParam1
Dim oParam2
oParam1 = oSproc.CreatePa rameter("sRetUP C",
ADODB.DataTypeE num.adVarChar,
ADODB.Parameter DirectionEnum.a dParamOutput, 50)
oParam2 = oSproc.CreatePa rameter("sUPC", ADODB.DataTypeE num.adVarChar,
ADODB.Parameter DirectionEnum.a dParamInput, 50, "0433960053 96")
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.Paramete rs.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
Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
I removed the Return @sRetUPC line. I am guessing that I can rely on
the set @sRetUPC line to set the value of the output parameter
>From there, I appended the parameters in the ASP code...like this
oSproc.Paramete rs.Append(oPara m2)
oSproc.Paramete rs.Append(oPara m1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
Then, I added the line oSproc.Execute( )
After that is:
Dim res
res = oSproc.Paramete rs.Item("sRetUP C").Value.toStr ing()
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
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.Paramete rs.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
jbonifacejr (jb*********@ho tmail.com) writes:
Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
I removed the Return @sRetUPC line. I am guessing that I can rely on
the set @sRetUPC line to set the value of the output parameter
>>From there, I appended the parameters in the ASP code...like this
oSproc.Paramete rs.Append(oPara m2)
oSproc.Paramete rs.Append(oPara m1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
Then, I added the line oSproc.Execute( )
After that is:
Dim res
res = oSproc.Paramete rs.Item("sRetUP C").Value.toStr ing()
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
Since I don't even know how your code looks like right now, just two
notes:
1) Use parameter names with leading @. The underlying provider may
prefer that.
2) Use adParamInputOut put for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
If you look at the top post you will see where I put the code I am
using. I also tried to let people know what happened when I tried their
suggestions. But, thanks for the advice...and I'll look at those SQL
Books online.
Jan
Erland Sommarskog wrote:
jbonifacejr (jb*********@ho tmail.com) writes:
Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
I removed the Return @sRetUPC line. I am guessing that I can rely on
the set @sRetUPC line to set the value of the output parameter
>From there, I appended the parameters in the ASP code...like this
oSproc.Paramete rs.Append(oPara m2)
oSproc.Paramete rs.Append(oPara m1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
Then, I added the line oSproc.Execute( )
After that is:
Dim res
res = oSproc.Paramete rs.Item("sRetUP C").Value.toStr ing()
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
Since I don't even know how your code looks like right now, just two
notes:
1) Use parameter names with leading @. The underlying provider may
prefer that.
2) Use adParamInputOut put for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Why are you using stored proc when you can use a function(s)? I always
thought the output parameter was a bit of a hack and clumsy to use.
jbonifacejr wrote:
If you look at the top post you will see where I put the code I am
using. I also tried to let people know what happened when I tried their
suggestions. But, thanks for the advice...and I'll look at those SQL
Books online.
Jan
Erland Sommarskog wrote:
jbonifacejr (jb*********@ho tmail.com) writes:
Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
>
I removed the Return @sRetUPC line. I am guessing that I can rely on
the set @sRetUPC line to set the value of the output parameter
>
>>From there, I appended the parameters in the ASP code...like this
oSproc.Paramete rs.Append(oPara m2)
oSproc.Paramete rs.Append(oPara m1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Then, I added the line oSproc.Execute( )
After that is:
Dim res
res = oSproc.Paramete rs.Item("sRetUP C").Value.toStr ing()
>
This is not working. Do you know how I can get access to the value of
the parameter that is returned?
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
Since I don't even know how your code looks like right now, just two
notes:
1) Use parameter names with leading @. The underlying provider may
prefer that.
2) Use adParamInputOut put for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thanks Erland...I see where I screwed up. I thought I had explained
what was wrong, but I did that in a different thread in another forum.
Anyway, I got this working using classic ASP and ADODB. Now I am going
to try to get it working over ASP.NET and ADO.NET. Wish me luck.
So far, eveerything works except that I am constantly being told that
the stored procedure expects a parameter that was not supplied.
However, The same two parameters are created and added to the
Parameters of the command object.
I'll continue to work on it and see if I can get it to work. Looks like
I need a datareader or some other object. I found a great KB article
that basically shows me everythig I am doing (right and wrong)... http://support.microsoft.com/kb/306574
Erland Sommarskog wrote:
jbonifacejr (jb*********@ho tmail.com) writes:
If you look at the top post you will see where I put the code I am
using.
Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.
Basically, if you only say "not working" without specifying why, and
don't show us the code, don't expect that much help. But that's your call.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
jbonifacejr (jb*********@ho tmail.com) writes:
Thanks Erland...I see where I screwed up. I thought I had explained
what was wrong, but I did that in a different thread in another forum.
Posting the same question independently to two forums is not a nice
thing to. This means that people can waste time on answering your post,
when it has already been answered elsewhere.
Anyway, I got this working using classic ASP and ADODB. Now I am going
to try to get it working over ASP.NET and ADO.NET. Wish me luck.
So far, eveerything works except that I am constantly being told that
the stored procedure expects a parameter that was not supplied.
However, The same two parameters are created and added to the
Parameters of the command object.
Again, without seeing your code it's hard to tell. There is a difference
between ADO and SqlClient though: with ADO, the parameter names are
just local to the application, so if you misspell a parameter name,
you may get away with it. Not so with SqlClient.
I'll continue to work on it and see if I can get it to work. Looks like
I need a datareader or some other object.
Since your procedure has an output parameter, but no result set, the most
conventient method to use is ExecuteNonQuery , in which case you only need
the Command object.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Daniel |
last post by:
hi ng,
i am newbie to sqlserver and my problem seems simple, but i didn't find
information about it:
How can i display the
RETURN @x
value of a stored procedure in the sql analyzer of the sqlserver?
thanks a lot,
d
|
by: adolf garlic |
last post by:
Im trying to return xml from sql.
The xml is made up of different fragments, some using FOR XML ... syntax.
The result is a valid xml doc.
There is a working stored proc that returns the xml
In .net i'm having problems loading this up.
I've now tried installing sqlxml managed classes and the following appears
to work when stepping through,...
|
by: Learner |
last post by:
Hi there,
I have a storec proc that schedules a Sql job and finally it returns
0 then it was successfull and if it returns 1 then its unsuccessful.
Now when i run the stored proc in the query analyzer its returning
SQLServerAgent is not currently running so it cannot be notified of
this action.
Stored Procedure:...
|
by: philip |
last post by:
hello, i am new to asp.net and sql server, and i have 3 questions for
asking:
1. i am writing a store procedure of login validation for my asp.net
application and wondering what the different between RETURN and SELECT
is.
if exists(select * from users where username = @username and password =
@password)
BEGIN
|
by: balaji krishna |
last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i don't know how to return the rows the cursor has opened and I don't know how to handle the return set from the proc in my java code.
My main...
| |
by: Eric Effer |
last post by:
Hi
I am a newbie with vb.net. I am working with vb.net 2.0 and sql server 2005.
I am trying to get the return value from my insert stored proc.Does anyone
know how to do this?
thanks
E
|
by: =?Utf-8?B?Vmlua2k=?= |
last post by:
Hello Everyone,
I can successfully insert and update the oracle database by calling a
oracles stored proc from my .net code. This oracle stored proc is returning
some value. I cannot see that value in my .net code
Below is my .net code
OracleParameter mbrid = new OracleParameter("RET_MBRID", OracleType.Number);
mbrid.Direction =...
|
by: Mick Walker |
last post by:
Hi Everyone,
I am stumped here. I have the following stored proceedure:P
CREATE PROCEDURE .
@SupplierSKU varchar(50),
@RetVal int
AS
Select @Retval = count(*) from dbo.ImportLines
Where = @SupplierSKU
|
by: colmkav |
last post by:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.
Set Cmd = New Command
With Cmd
Set...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |