Moving through recordsets returned from stored procedures

I have a stored procedure which contains a loop.

I am then trying to return the results of this stored procedure onto my ASP page into a recordset.

I want to loop through this recordset to obtain the values from the loop in the stored procedure.

However, it will not allow me to move backwards in the recordset because it is populated via a live feed.

Does anyone know if/how this could be done.

I am using the following code on the ASP page

Set cmd_Execute_stored_procedure = Server.CreateObject("ADODB.Command")

cmd_Execute_stored_procedure.ActiveConnection = cnnpayreview
cmd_Execute_stored_procedure.CommandText = "sp_summaryrg"
cmd_Execute_stored_procedure.CommandType = AdCmdStoredProc

set objcluster_sp = cmd_Execute_stored_procedure.Execute()

objcluster_sp.movefirst
do while not objcluster_sp.eof
response.write(objcluster_sp("counta")
objcluster.movenext
loop

...
obichamAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
John844Connect With a Mentor Commented:
here is some code to help you get started with modifying your stored procedure

        --suppress the (1 row(s) affected) messages
        SET NOCOUNT ON

     --Create a temp table
     CREATE TABLE #Temp
     (
         counta  int,
         countbon int
     )


        insert your items into the temp table instead of using SELECT


        at the bottom of your sp add
        SELECT * FROM #Temp

        --turn the (1 row(s) affected) messages back on
        SET NOCOUNT OFF
0
 
makerpCommented:
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
0
 
drittichCommented:
Hmm, don't think so, but how about creating a temp table with the SP and using that as the basis of your recordset?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
obichamAuthor Commented:
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"
0
 
John844Commented:
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.
0
 
obichamAuthor Commented:
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?
0
 
John844Commented:
are you sure that your sp is returning one recordset?  what does the output from query analyser look Like?
0
 
obichamAuthor Commented:
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 ...
0
 
John844Commented:
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

...
0
 
John844Commented:
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.
0
 
John844Commented:
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
0
 
obichamAuthor Commented:
You are brilliant - you've really helped me out here.

Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.