Solved

Moving b/w recordsets after calling stored procedure

Posted on 2003-12-01
8
279 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Button on Table, name table1 not working 4 43
ASP Classic, check if Querystring is UCase or LCase 10 36
2012 SQL to JSON Select 5 45
JQuery on multiple lines 3 50
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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

710 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