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"

Arithmetic overflow error converting expression to data type int.

I've got this error message while generate the output with ASP:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
Arithmetic overflow error converting expression to data type int."

it indicate that the error is related to this line:
"rc1.Movenext"

where rc1 is set as objconn.Execute(sql).

Not all outputs result like this, it happens when it has many relationships with other records, especially with those records which also have many relationships with other records.

Can anyone suggest a solution?
I've tried to increase the size of the database file, but it doesn't work.could you perhaps show the query that caused the error?|||Here is the query:

CREATE procedure rp_co_relatedcompanies @.companyregistrationno varchar(20)
as
set nocount on
--used for related companies details

select a.companypersonid,a.personname,a.personaddress1,a. personaddress2,a.personcity,a.personstate,a.person country,
a.personzip,a.personphone,a.personfax,a.personemai l,a.personuniqueid,b.principalbusiness,b.secondryb usiness,
c.relationwithcompany,c.numberofshares, c.classofsharesheld , cast(d.noofissuedshares as int) 'noofissuedshares',
'percentageofsharehold' = case when c.numberofshares =0 then 0 else c.numberofshares/d.noofissuedshares*100 end, c.otherremarks
from companyperson a
left join companystructure d on a.personuniqueid=d.companyregistrationno
left outer join operationsandactivity b on a.personuniqueid = b.companyregistrationno
left outer join relationshipcompanyperson c on a.companypersonid = c.companypersonid
where c.companyregistrationno = @.companyregistrationno and c.relationshipwith = 2
and a.deletionflag=0 order by a.personname
set nocount off
GO|||can you see anything in that query where there might be a problem "converting expression to data type int"

the immediate suspect is cast(d.noofissuedshares as int)

another suspect is c.numberofshares/d.noofissuedshares*100

check those and let us know|||Table DDL would help a lot. Heck, I was betting on the c.companyregistrationno being an INT and the @.companyregistrationno failing the implicit Cast...

Anybody want to start a pool on this? Or another option (not as much fun, but the problem would get solved a lot faster) would be for yllas to post the DDL for all of the tables in this query!

-PatP