ASP Jscript to obtain Recordset & o/p params

Does anyone know how to obtain the returned recordsets and output parameters
from a stored procedure by using ASP Jscript?
e.g.: I've got a sproc,
create proc testsp
@.strSN varchar(30), --<-- as i/p param for serial #
@.iProdNum int output --<-- as o/p param for a specific reason
as
select * from Inventory
...
set @.iProdNum= (an integer for o/p param)
Therefore, I wrote:
<%@.LANGUAGE="JAVASCRIPT"%>
<!--#include virtual="/Connections/cnInv.asp" -->
<%
var cmdGetItem = Server.CreateObject("ADODB.Command");
cmdGetItem.ActiveConnection = MM_cnInv_STRING;
cmdGetItem.CommandText = "dbo.testsp";
cmdGetItem.CommandType = 4;
cmdGetItem.CommandTimeout = 0;
cmdGetItem.Prepared = true;
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.RETURN_VALUE",
3, 4,4));
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.strSN", 200,
1,30, String(Request.Form("txtSN"))));
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.iProdNum", 3,
2,4));
var oRst=cmdGetItem.Execute();
// now --> oRst <-- holds the returned recordset by (select * from
Inventory)
// but neither --> cmdGetItem.Parameters.Item("@.RETURN_VALUE").Value <--
// nor --> cmdGetItem.Parameters.Item("@.iProdNum").Value <-- contains
nothing
%>
However, I only get the recordset returned by (select * from inventory) but
nothing from @.iProdNum by my ASP Jscript.
Thanks,
LeonardPADO will return output parameters in a separate recordset. You'll need to
use the NextRecordset method after retrieving the SELECT results. I don't
know JScript but try something like the following after processing the query
results:
oRst = oRst.NextRecordset;
Hope this helps.
Dan Guzman
SQL Server MVP
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:eEEeVsqNFHA.3760@.TK2MSFTNGP12.phx.gbl...
> Does anyone know how to obtain the returned recordsets and output
> parameters
> from a stored procedure by using ASP Jscript?
> e.g.: I've got a sproc,
> create proc testsp
> @.strSN varchar(30), --<-- as i/p param for serial #
> @.iProdNum int output --<-- as o/p param for a specific reason
> as
> select * from Inventory
> ...
> set @.iProdNum= (an integer for o/p param)
> Therefore, I wrote:
> <%@.LANGUAGE="JAVASCRIPT"%>
> <!--#include virtual="/Connections/cnInv.asp" -->
> <%
> var cmdGetItem = Server.CreateObject("ADODB.Command");
> cmdGetItem.ActiveConnection = MM_cnInv_STRING;
> cmdGetItem.CommandText = "dbo.testsp";
> cmdGetItem.CommandType = 4;
> cmdGetItem.CommandTimeout = 0;
> cmdGetItem.Prepared = true;
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.RETURN_VALUE",
> 3, 4,4));
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.strSN", 200,
> 1,30, String(Request.Form("txtSN"))));
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.iProdNum", 3,
> 2,4));
> var oRst=cmdGetItem.Execute();
> // now --> oRst <-- holds the returned recordset by (select * from
> Inventory)
> // but neither --> cmdGetItem.Parameters.Item("@.RETURN_VALUE").Value <--
> // nor --> cmdGetItem.Parameters.Item("@.iProdNum").Value <-- contains
> nothing
> %>
> However, I only get the recordset returned by (select * from inventory)
> but
> nothing from @.iProdNum by my ASP Jscript.
> Thanks,
> LeonardP
>|||Dan Guzman wrote:
> ADO will return output parameters in a separate recordset.
Really? I thought you had to use the Parameters collection ...
Are you sure?
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You are right that the parameters collection contains the output values.
What I mean is that the preceding recordset(s) need to be processed before
output parameter values can be retrieved from the collection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:ORIZDhsNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Dan Guzman wrote:
> Really? I thought you had to use the Parameters collection ...
> Are you sure?
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>|||Leonard Poon wrote:
> Does anyone know how to obtain the returned recordsets and output
> parameters from a stored procedure by using ASP Jscript?
>
ADO is ADO - whether it's being called by vbscript or jscript.
To retrieve output or return parameters, you need to either close the
recordset that is returned by your procedure, or retrieve the last record in
that recordset. The output and return parameter values are not sent until
the resultset is completely sent. My practice is to use GetRows to read the
data into an array and close the recordset, allowing me to access the output
and return parameter values. But this is slightly awkward in jscript, whose
arrays are not multidimensional. There is a a way to use GetRows in
jscript - do a google search for jscript and getrows to see how this is
done.
To suppress any extra resultsets from being generated by informational
messages, it is a good practice to use "SET NOCOUNT ON" at the beginnning of
all your procedures.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"