Solved

Moving b/w recordsets after calling stored procedure

Posted on 2003-12-01
8
272 Views
Last Modified: 2012-08-13
 I have a stored procedure that returns a recordset and has an output parameter that I used to format the page.

Currently, I loop through the recordset's information, then call objRS.NextRecordset and check the value of the output parameter.

Ideally, I'd like to have the value of the output parameter before I loop through the recordset.  Is there anyway to have the output parameter accessible before the recordset or is there a PreviousRecordset method?

Also, why do I have to call objRS.NextRecordset anyway since the parameter is part of the command object?
0
Comment
Question by:JDmears3
  • 3
  • 3
8 Comments
 
LVL 3

Expert Comment

by:bjrcreations
Comment Utility
You only need to use objRS.NextRecordset if you are opening more than one record result set with SQL, for example, in a compound command statement, like "SELECT * FROM table1;SELECT * FROM table2". With that said, please post some code so this issue is easier to troubleshoot.
0
 

Author Comment

by:JDmears3
Comment Utility
Here is the stored proc:
CREATE PROCEDURE sp_uscholar_commmenu
@pid char(9),
@retValue int OUTPUT

AS

if exists(Select 1 from uscholar_admin where pid = @pid)
begin
SELECT title,id FROM uscholar_scholarship us;
SELECT @retValue = 1
end

else
begin
SELECT title, us.id FROM uscholar_committee_scholarship ucs, uscholar_scholarship us
WHERE us.id = ucs.id and zpid = @pid
SELECT @retValue = 0
end

GO



Here is the asp code:

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


objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = Ed_Data3
objCmd.CommandText = "sp_uscholar_commmenu"

objCmd.Parameters.Append objCmd.CreateParameter("@pid", adChar, adParamInput, 9, Session("pid"))
objCmd.Parameters.Append objCmd.CreateParameter("@retValue", adInteger, adParamOutput)

Set objRS = objCmd.Execute

'Do some stuff w/ recordset...

objRS.NextRecordset  'this is requred otherwise, retValue is empty after next statement
retValue = objCmd.Parameters("@retValue")
If retValue = 1 Then
0
 
LVL 3

Expert Comment

by:bjrcreations
Comment Utility
You have to use objRS.NextRecordset because @retValue is in your second SELECT command in each if statement. Try moving this value to be the first SELECT command like this:

-----------------------

if exists(Select 1 from uscholar_admin where pid = @pid)
begin
SELECT @retValue = 1
SELECT title,id FROM uscholar_scholarship us;
end

else
begin
SELECT @retValue = 0
SELECT title, us.id FROM uscholar_committee_scholarship ucs, uscholar_scholarship us
WHERE us.id = ucs.id and zpid = @pid
end

-----------------------

Then, if you need to gather the results of the second SELECT statement, use objRS.NextRecordset. That might solve your problem.

bjrcreations
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:JDmears3
Comment Utility
I tried swapping the selects as noted above, but it didn't change anything as far as the recordsets are concerned.
0
 

Author Comment

by:JDmears3
Comment Utility
Ok, I found the answer to my question, see this URL:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q224/5/91.ASP&NoWebContent=1

You either have to close the recordset, which is in effect what nextrecordset does above or use a client side cursor.
0
 
LVL 3

Expert Comment

by:bjrcreations
Comment Utility
Sorry my solution didn't work. Good luck on your project and thanks for posting the appropriate solution!

bjrcreations
0
 

Accepted Solution

by:
SpazMODic earned 0 total points
Comment Utility
PAQed, with points refunded (120)

SpazMODic
EE Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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 information …
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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

7 Experts available now in Live!

Get 1:1 Help Now