Solved

Moving b/w recordsets after calling stored procedure

Posted on 2003-12-01
8
273 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating TABS in ASP Classic NOT using DIV 22 63
How to speed up an ASP/MS SQL web application 6 70
how to hide hopscotch tour popup page reload? 2 63
API not working 33 56
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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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…

920 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