returning from sp(cross)

Savas Ates

CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18) output
AS
select * from users
return "10"
GO
****************************


<%
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&" "
Response.Write xx
%>

dont return any value .. what is the problem?

Jul 21 '05 #1

Subscribe Post Reply

2281

Alejandro Mesa

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
****************************


<%
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&" "
Response.Write xx
%>

dont return any value .. what is the problem?

Jul 21 '05 #2

Bob Barrows [MVP]

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&" "
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&" "
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

Savas Ates

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

/***********************************************


<%
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&" "
Response.Write xx
%>

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

but not return value? why?

Jul 21 '05 #4

Savas Ates

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

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



<%

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&" "
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx
Response.End %>

Jul 21 '05 #5

Aaron [SQL Server MVP]

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

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



<%

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&" "
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx
Response.End %>

Jul 21 '05 #6

Jeff Dillon

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

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



<%

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&" "
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx
Response.End %>

Jul 21 '05 #7

Savas Ates

Response.Write objRS.Fields("email").Value this value writes

but
Response.Write xx is null or nothing

Jul 21 '05 #8

Ray Costanzo [MVP]

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
****************************


<%
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&" "
Response.Write xx
%>

dont return any value .. what is the problem?

Jul 21 '05 #9

Jeff Dillon

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

Alejandro Mesa

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

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



<%

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&" "
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx
Response.End %>

Jul 21 '05 #11

Bob Barrows [MVP]

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&" "
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
%>

Jul 21 '05 #12

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...

C / C++

Cross database foreign key workaround?

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...

PostgreSQL Database

OnEditCommand - .FindControl Returning Null

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...

ASP.NET

Deliver Cross Tab output to Users

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...

ASP.NET

HELP: Cross-Tabs, Stored Procedures and other Wonders of the World

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...

Microsoft Access / VBA

window.showModalDialog() works perfect, but returning to parent-page doesnot

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...

ASP.NET

Cross Thread Exception after reading Asynchronous Serial Port

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 ...

C# / C Sharp

webservices returning custom types

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...

.NET Framework

SQL 2005: Transactional Replication > Executing sp on subscribershould execute on publisher

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...

Microsoft SQL Server

Access Europe meeting on Wed 2 August - An Access Developer Guide to creating applications in Blazor

by: isladogs | last post by:

The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...

Microsoft Access / VBA

How to Use CodiumAI's pr-agent?

by: erikbower65 | last post by:

Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...

General

Speedup your spring application

by: linyimin | last post by:

Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...

General

Access Europe meeting on Wed 6 Sept - Database Analyzer and other tools

by: isladogs | last post by:

The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...

Microsoft Access / VBA

How to insert a new record in a database

by: Taofi | last post by:

I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...

DB2 Database

Cancel Print Action if Sub form has No Records

by: DJRhino1175 | last post by:

When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...

Microsoft Access / VBA

To verify entry of a dropdown

by: DJRhino | last post by:

Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...

Microsoft Access / VBA

How to calculate date of retirement from date of birth

by: Mushico | last post by:

How to calculate date of retirement from date of birth

Data Management

Windows 11 and Access Combo Box Issue

by: DJRhino | last post by:

Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

Microsoft Access / VBA

Similar topics