Moving through recordsets returned from stored procedures

i have the same problem, its not because its populated by a live feed, its just the properties of a recordset created from a stored procedure. maybe somebody knows if this can be changed

Hmm, don't think so, but how about creating a temp table with the SP and using that as the basis of your recordset?

Drittich - do you have an example of how to call the contents of the temporary table into the ASP page?

I am trying something along the following lines but it doesn't work

cmd_Execute_stored_procedure.execute
               
Set rstmgt = Server.CreateObject("ADODB.Recordset")
rstmgt.Open "select * from #resulttable"

try creating your objcluster_sp as a recordset object
set the cursor location to client side
set the
Dim objcluster_sp
set objcluster_sp = server.createobject("ADODB.Recordset")
objcluster_sp.CursorType = adOpenStatic
objcluster_sp.LockType = adLockReadOnly

set objcluster_sp = cmd_Execute_stored_procedure.Execute()


you need a reference to ADOVBS.inc if you want to use these constants, or just change them to the numeric values.
I think that this will get you what you need.

John844 I have tried doing as you said, and this appears to give more success as it lets me movefirst in the stored procedure recordset.

However, it will not loop through it, even though I know there is more than one value for counta (I have run the Stored procedure in Query Analyser to check this)

I am doing the following:

set objcluster_sp = server.createobject("ADODB.Recordset")
objcluster_sp.CursorType = adOpenStatic
objcluster_sp.LockType = adLockReadOnly
               
set objcluster_sp = cmd_Execute_stored_procedure.Execute()
               

objcluster_sp.movefirst
         
do while not objcluster_sp.eof%>
<td width=2>
<%response.write(objcluster_sp("counta"))%>
</td><%
objcluster_sp.movenext
loop%>

But only one value is returned in the <td> tag.

Any more ideas?

are you sure that your sp is returning one recordset?  what does the output from query analyser look Like?

It looks like this...

counta      
-----------
8

(1 row(s) affected)

countbon    
-----------
8

(1 row(s) affected)

counta      
-----------
2

(1 row(s) affected)

countbon    
-----------
2

(1 row(s) affected)

etc ...

your sp is returning multiple recordsets, not a single recordset with rows.

your last post showed it returing 4 recordsets with one row in each

...

you will need to create a temp table to hold your values in the stored procedure.  at the bottom of the sp, you need to return the values from the temp table.

fyi you could make your code work with the existing sp, but it would be very inefficient.

to do that you can use objcluster_sp.NextRecordset to move to the next recordset.  you could then query the next value.  I do not know how to find out how many recordsets are returned though.

I don't recomment appraching your problem this way, but that is ultimately your decision.

on error resume next
dim blnNotDone
blnNotDone = True

while blnNotDone
    'write out a column
    response.write "<td width=2>" & objcluster_sp("counta") & "/<td>" & vbCrLf

    'move to the next recordset which will hold a value for countbon.
    objcluster_sp.NextRecordset

    'move to the next recordset which will hold a value for counta.
    objcluster_sp.NextRecordset

    'trap for error
    if Err.number <> 0 then
        blnNotDone = False
    end if

loop

You are brilliant - you've really helped me out here.

Thank you very much.