Solved

Moving through recordsets returned from stored procedures

Posted on 2001-07-16
12
266 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now