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"

asp / sql server dates in recordsets

Hey,

I've got an Classic ASP app pulling some records out of a DB, which
worked fine when it was Access. When we upped to SQL Server, all of a
sudden, when the Recordsets came back, all the dates were empty, even
though the data was showing up in the DB.

About a year ago (only code asp apps once and a while), I remember
seeing this and finding something that just had something to do with the
order in which something was being referenced, but I just can't for the
life of me remember what it was.

The Dates are set as datetime, length of 8.

Anyone know why this happens and a solution?

many thanks,

rob"rob" <rob@.pleaseremovespamcherny.com> wrote in message
news:XYadnScIEbMPLpvdRVn-uA@.giganews.com...
> Hey,
> I've got an Classic ASP app pulling some records out of a DB, which
> worked fine when it was Access. When we upped to SQL Server, all of a
> sudden, when the Recordsets came back, all the dates were empty, even
> though the data was showing up in the DB.
> About a year ago (only code asp apps once and a while), I remember
> seeing this and finding something that just had something to do with the
> order in which something was being referenced, but I just can't for the
> life of me remember what it was.
> The Dates are set as datetime, length of 8.
> Anyone know why this happens and a solution?
> many thanks,
> rob

It's not really possible to give a good answer without more information. Do
you have an example of a query which returns data in Query Analyzer, but not
in ASP?

You might also look at www.aspfaq.com, if you haven't already - there are
quite a lot of articles about handling dates.

Simon