Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Moving b/w recordsets after calling stored procedure

Posted on 2003-12-01
8
Medium Priority
?
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 3

Expert Comment

by:bjrcreations
ID: 9854443
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
ID: 9854638
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
ID: 9855112
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:JDmears3
ID: 9861267
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
ID: 9866626
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
ID: 9867287
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
ID: 9887993
PAQed, with points refunded (120)

SpazMODic
EE Moderator
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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