Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Moving through recordsets returned from stored procedures

Posted on 2001-07-16
12
Medium Priority
?
289 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 400 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

876 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