[ADO] return value of stored proc. in ASP
Ch'ti man
Hi,
My problem is:
I have to get multiple result sets from a stored procedure (SYBASE) plus a
return value. I have writen this in ASP under IIS4 :
=======begining of the ASP file===========
<HTML><BODY>
<!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->
<%
Dim j,k,l
set cn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
set RS = Server.CreateObject("ADODB.Recordset")
cn.Open "ODBC_CON", "user", "password"
set cmd.ActiveConnection = cn
'cmd.CommandText = "testFred"
cmd.CommandText = "{RV = call testFred(p1)}"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("RV", adInteger,
adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("p1", adInteger, adParamInput)
' Set value of Param1 of the default collection to 22
cmd("p1") = 25
cmd.Execute
RS.Open cmd
if RS.EOF = -1 then
%>
No results!
<%
else
%>
return value before reading the record sets = <% Response.Write
cmd("RV") %><P>
<%
j=0
Do
j=j+1
%>
RecordSet # <%=j%><P>
<%
Do While Not RS.EOF
For i = 0 to RS.Fields.Count - 1
%>
<% = RS(i).Name %> : <% = RS(i) %><BR>
<%
Next
RS.MoveNext
Loop
%>
At this time, return value = <% Response.Write cmd("RV")
%><P>
<%
Set RS = RS.NextRecordset
Loop Until RS.State = adStateClosed
%>
At the end, the return value = <% Response.Write cmd("RV") %>
<%
end if
%>
</BODY>
</HTML>
=======end of the ASP file===========
If the stored proc. is made of 2 "select..." and a "return 1" instruction, I
should get all the records for the 2 select plus the return value which is
1.
When I execute, I get the records from the 2 record sets but the return
value is "empty"!
With 1 select and a return value, it is OK. I get both. As soon as I have
more than 1 select, it is wrong because I have to use the "NextRecordSet"
property which makes it go wrong... it seems !
Can anyone help me (fel...@atos-group.com) ?
Thanks.
Fred
--
-= remove NOSPAM from my address if you want to email me =-
-= enlevez le NOSPAM de mon adresse si vous voulez m'envoyer un email =-
Niels Berglund
The problem is that you have to close the recordset before you can get the
return value. If you do that it'll work fine.
Another way of doing it is to use client cursors. If you do that, then you
can have the recordset open and still read the return value. However, that
won't work if you use multiple recordsets. You also need to use the
recordset.Open syntax instead of Command.Execute for client cursors to work.
Later...
Niels
Ch'ti man wrote in message
<14953E9BAF17D1119268...@hermes.atos-group.com>...