473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

returning from sp(cross)

CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18) output
AS
select * from users
return "10"
GO
****************************
<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
'cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
Response.Write xx
%>

dont return any value .. what is the problem?

Jul 21 '05 #1
11 2315
The first statement in the sp should be SET NOCOUNT ON to avoid that the
message indicating the number of rows affected by the t-sql statement be
returned as part of the result. If you do not want to do this, then you have
to use recordset method NEXTRECORDSET to get the result you are interested
in. To access output parameters and return value, you have process the
recordset or cancel it first, because SQL Server returns output parameter and
return code values in the last packet it returns to the client.

AMB

"Savas Ates" wrote:
CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18) output
AS
select * from users
return "10"
GO
****************************
<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
'cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
Response.Write xx
%>

dont return any value .. what is the problem?


Jul 21 '05 #2
I'm sorry it seems like I'm replying to you Alejandro, but I don't see the
OP for some reason.
Alejandro Mesa wrote:

"Savas Ates" wrote:
CREATE PROCEDURE st_deneme @userid numeric (18) ,
Numeric and Decimal datatypes should have two attributes: Precision and
scale. You've only provided one attribute, precision (18), so scale will be
assumed to be zero by default. Is that what you intended? The more accepted
way of declaring this variable would be:

@userid numeric (18,0)
@result numeric
(18) output AS
select * from users
return "10"
GO
****************************
What is the purpose of the output parameter? You're not assigning a value to
it in your procedure. You might find this post to be of value:

http://www.google.com/groups?hl=en&l...TNGP10.phx.gbl


<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
Why are you providing "100" as the length of this parameter? It doesn't
matter since the length argument is ignored for numeric parameters, but it
leads me to suspect that you don't understand the purpose of these
arguments. Go to msdn.microsoft.com/library and find the ADO documentation
(drill down into the Data Access node in the TOC). look up the
CreateParameter method.

Numeric and Decimal parameters must have their Precision and NumericScale
properties set before you assign a value to them. This cannot be done in the
CreateParameter statement. See below.

cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
'cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
In addition to creating the parameter objects, you have to append them to
the Parameters collection. See below.
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
This is too soon to try and get a value from your output parameter. You
haven't even executed the procedure yet!

Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
You must close the recordset before attempting to read the value of the
output parameter (output and return values are not sent to the client until
all the records for all the resultsets generated by the procedure are sent.
his makes it important to use SET NOCOUNT ON as suggested by Alejandro.)
Response.Write xx
%>

In addition to what Alejandro said, it's usually correct to create and
append the return parameter first, even if you aren't planning to use it. I
usually call it RETURN_VALUE to differentiate it from the other parameters.
Here is what it should look like, assuming that you will modify your
procedure to return the value via the output parameter rather than the
return statement:

With cmd.Parameters
.Append cmd.CreateParameter("RETURN_VALUE", _
adInteger, adParamReturnValue)
set param =.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =.CreateParameter ("@result", adNumeric, _
adParamOutput)
param.Precision=18
param.NumericScale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx

I've written a free tool to generate the vbscript code needed to execute a
stored procedure. You may find it helpful. It's available here:
http://www.thrasherwebdesign.com/ind...s&hp=links.asp

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 21 '05 #3
CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18) output
AS
SET NOCOUNT ON
select * from users
return "10"
GO

/***********************************************
<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"

cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
'cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
Response.Write xx
%>

it writes *Response.Write objRS.Fields("email").Value&"<br>"* (the value
of this row)

but not return value? why?


Jul 21 '05 #4
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="10"
GO

***************************************


<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%

Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"

With cmd.Parameters
.Append cmd.CreateParameter("@result", _
adInteger, adParamReturnValue)
set param =cmd.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =cmd.CreateParameter ("@result", adNumeric, _
adParamOutput)
param.Precision=18
param.NumericScale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx
Response.End %>
Jul 21 '05 #5
What on earth does "doesnt work" mean?

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:eT**************@TK2MSFTNGP14.phx.gbl...
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="10"
GO

***************************************


<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%

Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"

With cmd.Parameters
.Append cmd.CreateParameter("@result", _
adInteger, adParamReturnValue)
set param =cmd.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =cmd.CreateParameter ("@result", adNumeric, _
adParamOutput)
param.Precision=18
param.NumericScale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx
Response.End %>

Jul 21 '05 #6
Does it work in Query Analyzer?

Jeff
"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:eT**************@TK2MSFTNGP14.phx.gbl...
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="10"
GO

***************************************


<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%

Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"

With cmd.Parameters
.Append cmd.CreateParameter("@result", _
adInteger, adParamReturnValue)
set param =cmd.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =cmd.CreateParameter ("@result", adNumeric, _
adParamOutput)
param.Precision=18
param.NumericScale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx
Response.End %>

Jul 21 '05 #7
Response.Write objRS.Fields("email").Value this value writes

but
Response.Write xx is null or nothing
Jul 21 '05 #8
Perhaps you need to rearrange the order in which you're doing thing here:

xx=cmd.Parameters ("@result").Value ''before cmd.Execute?
Set objRS = cmd.Execute
Response.Write xx

Ray at home
"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:uV**************@TK2MSFTNGP11.phx.gbl...
CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18)
output AS
select * from users
return "10"
GO
****************************
<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
'cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
Response.Write xx
%>

dont return any value .. what is the problem?


Jul 21 '05 #9
Yes, you have to spin through the whole result set, before the output param
will become available.

While not objRS.EFO

objRS.MoveNext

wend

"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:uz**************@tk2msftngp13.phx.gbl...
Response.Write objRS.Fields("email").Value this value writes

but
Response.Write xx is null or nothing

Jul 21 '05 #10
Savas,
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="10"
GO
Your sp will never reach the point where you set @result. The statement is
after the return. You should set it before the return.

CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
set @result=10
return 0
GO

AMB

"Savas Ates" wrote:
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="10"
GO

***************************************


<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%

Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"

With cmd.Parameters
.Append cmd.CreateParameter("@result", _
adInteger, adParamReturnValue)
set param =cmd.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =cmd.CreateParameter ("@result", adNumeric, _
adParamOutput)
param.Precision=18
param.NumericScale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx
Response.End %>

Jul 21 '05 #11
I'm reposting this because it appears that Savas never saw it.
"Savas Ates" wrote:
CREATE PROCEDURE st_deneme @userid numeric (18) ,
Numeric and Decimal datatypes should have two attributes: Precision and
scale. You've only provided one attribute, precision (18), so scale will be
assumed to be zero by default. Is that what you intended? The more accepted
way of declaring this variable would be:

@userid numeric (18,0)
@result numeric
(18) output AS
select * from users
return "10"
GO
****************************
What is the purpose of the output parameter? You're not assigning a value to
it in your procedure. You might find this post to be of value:

http://www.google.com/groups?hl=en&l...TNGP10.phx.gbl


<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
Why are you providing "100" as the length of this parameter? It doesn't
matter since the length argument is ignored for numeric parameters, but it
leads me to suspect that you don't understand the purpose of these
arguments. Go to msdn.microsoft.com/library and find the ADO documentation
(drill down into the Data Access node in the TOC). look up the
CreateParameter method.

Numeric and Decimal parameters must have their Precision and NumericScale
properties set before you assign a value to them. This cannot be done in the
CreateParameter statement. See below.

cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
'cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
In addition to creating the parameter objects, you have to append them to
the Parameters collection. See below.
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
This is too soon to try and get a value from your output parameter. You
haven't even executed the procedure yet!

Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
You must close the recordset before attempting to read the value of the
output parameter (output and return values are not sent to the client until
all the records for all the resultsets generated by the procedure are sent.
his makes it important to use SET NOCOUNT ON as suggested by Alejandro.)
Response.Write xx
%>

In addition to what Alejandro said, it's usually correct to create and
append the return parameter first, even if you aren't planning to use it. I
usually call it RETURN_VALUE to differentiate it from the other parameters.
Here is what it should look like, assuming that you will modify your
procedure to return the value via the output parameter rather than the
return statement:

With cmd.Parameters
.Append cmd.CreateParameter("RETURN_VALUE", _
adInteger, adParamReturnValue)
set param =.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =.CreateParameter ("@result", adNumeric, _
adParamOutput)
param.Precision=18
param.NumericScale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx

I've written a free tool to generate the vbscript code needed to execute a
stored procedure. You may find it helpful. It's available here:
http://www.thrasherwebdesign.com/ind...s&hp=links.asp

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 21 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: briforge | last post by:
I am writing a program for the Palm OS in c++. I have already posted this to a palm development forum but I'm desperate for a fix, so I hope this cross-post isn't offensive. My program is...
3
by: David Busby | last post by:
List, What are the recommended work arounds for cross database foreign keys? As I understand it transactions are not atomic with the TCL method. I have a situation that requires a master database...
0
by: George Durzi | last post by:
cross posted in datagrid group. Inside <columns/> in my datagrid, I have the following template column <asp:templatecolumn HeaderText="To Be Completed By"> <itemtemplate> <asp:Label...
4
by: __Stephen | last post by:
Sorry about the cross-post. "Life would be so much easier it we didn't have end users" :) I have an ASP.NET app that delivers a cross tab report to our Accounting Department. CFO just demanded...
1
by: xFiver | last post by:
Hey gang, I'm having a tough time wrapping my head around this problem. More than anything, I'm looking for the "why" of why this isn't working. THE SETUP: I have a Cross-Tab query (we'll...
1
by: Curious Trigger | last post by:
Hi there, programming with Visual Studio 2005 and ASP.NET 2.0 I want to open a modal dialog from Default.aspx. I searched the net and many newsgroups but I couldn't find any solution. First I...
5
by: Mo | last post by:
I am trying to set a text box value when data is received from the com port (barcode reader). I am getting the following error when I try to set the text box TXNumber after data is received ...
4
by: =?Utf-8?B?a2lzaG9y?= | last post by:
Hi, has any one used webservices for returning custom objects other than datasets like custom classes and their internal classes ?. What problems you have faced if any ? is there any limitation...
1
by: PatrickW | last post by:
Hi, I am trying to set up a Updatable Subscriptions for Transactional Replication with SQL 2005 (queued updating). I have a stored procedure "SYS_spShellTest" which is replicated to the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.