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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Moving b/w recordsets after calling stored procedure

 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
JDmears3
Asked:
JDmears3
  • 3
  • 3
1 Solution
 
bjrcreationsCommented:
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
 
JDmears3Author Commented:
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
 
bjrcreationsCommented:
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
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!

 
JDmears3Author Commented:
I tried swapping the selects as noted above, but it didn't change anything as far as the recordsets are concerned.
0
 
JDmears3Author Commented:
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
 
bjrcreationsCommented:
Sorry my solution didn't work. Good luck on your project and thanks for posting the appropriate solution!

bjrcreations
0
 
SpazMODicCommented:
PAQed, with points refunded (120)

SpazMODic
EE Moderator
0

Featured Post

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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now