Solved

Moving through recordsets returned from stored procedures

Posted on 2001-07-16
12
276 Views
Last Modified: 2012-08-13
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

...
0
Comment
Question by:obicham
12 Comments
 
LVL 10

Expert Comment

by:makerp
ID: 6285367
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
 
LVL 8

Expert Comment

by:drittich
ID: 6285391
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
 

Author Comment

by:obicham
ID: 6285958
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 7

Expert Comment

by:John844
ID: 6286022
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
 

Author Comment

by:obicham
ID: 6286384
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
 
LVL 7

Expert Comment

by:John844
ID: 6286551
are you sure that your sp is returning one recordset?  what does the output from query analyser look Like?
0
 

Author Comment

by:obicham
ID: 6286569
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
 
LVL 7

Expert Comment

by:John844
ID: 6287017
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
 
LVL 7

Expert Comment

by:John844
ID: 6287023
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
 
LVL 7

Accepted Solution

by:
John844 earned 100 total points
ID: 6287057
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
 
LVL 7

Expert Comment

by:John844
ID: 6287305
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
 

Author Comment

by:obicham
ID: 6288844
You are brilliant - you've really helped me out here.

Thank you very much.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question