Multiple Recordsets Possible - Count How Many Exist 1

[tklear]

I have a stored procedure that returns multiple recordsets for example:

select employeeId from employee
select termID from term

The number of records can vary depending on which stored procedure is called (sp name is passed as a parameter into the ASP page).

In my ASP page I would like to know how many recordsets are present. Is there any way to do this? Basically if there is more than one I want to redirect to another page. I know I can page through the recordsets with:

Set rst = rst.nextRecordset

However, this gives an error if there isn't another recordset. I can't seem to trap that error either but maybe someone has a method for doing that.

Any way that I can determine if there is more than one recordset returned would be fine.
Thanks.

 

[onpnt]

I don't follow. how are you creating the objects for a unknown amount of recordsets to create?



_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[shaddow]

I think he needs to loop throu all the recordsets of the querry or stored proc.

http://www.devguru.com/Technologies/ado/quickref/recordset_nextrecordset.html

and the code

While Not objRecordset.State = adStateClosed
   While Not objRecordset.Eof
    'display the recordset
   Wend
    'get the next one
   Set objRecordset = objRecordset.NextRecordset
Wend

________
George, M

 

[tklear]

George, that is close to what I need but I'm getting an error no matter how many recordsets I have.

Here's my test code:

dim intRecordsetCount
intRecordsetCount =0

' ASP did not recognize adStateClosed variable
While Not rst.State = 0
intRecordsetCount = intRecordsetCount + 1
response.write intRecordsetCount & "!"
While Not rst.Eof
'display the recordset
response.write "record "
rst.MoveNext
Wend
'get the next one
Set rst = rst.NextRecordset
Wend

I get this error:
1!record record record record record record record record record 2!record record record record record record record record record
Microsoft VBScript runtime error '800a01a8'

Object required

/list/sortList.asp, line 47

As you can see, it does loop through so I could count but it still gives an error when it runs out of recordsets.

I tried adding:
on error resume next
to the top of the code to ignore the error. This caused the page to hang - I'm guessing it couldn't get out of the While loop because the state was never closed.

I'm close but not quite there. Any ideas?

Thanks.

 

[shaddow]

yes that is due to the fact that if the nextrecordest does not exists then all the other methods does not exists also.
We will overiride this using IsObject method.
tell if this work if not i'll make an example by twomorrow and check what method it's suited to this

While IsObject(rst)  
    intRecordsetCount = intRecordsetCount + 1
    response.write intRecordsetCount & "!"
   While Not rst.Eof
    'display the recordset
     response.write "record   "
     rst.MoveNext
   Wend
   'get the next one
   Set rst = rst.NextRecordset
Wend

________
George, M

 

[tklear]

OK, this time I get the following error:

1!record record record record record record record record record 2!record record record record record record record record record 3!
Microsoft VBScript runtime error '800a01a8'

Object required

/list/sortList.asp, line 51 (this is the line with While Not rst.EOF)

Looks like it allows the nextrecordset command, passes as an object for the isObject function, but fails as an object when .eof property is called.

Just substituted this:
While not rst is nothing
for the isObject function and it worked.

Thanks for the idea!

 

[tklear]

Shaddow,
Don't suppose you have time to look at this post of mine:

thread333-714340 has to do with ADSI.changePassword

You are good so I thought I'd ask.

Thanks again for the solution on this one!

Terry

 

[Funka]

i don't recall if there is a property of the RS object to tell you if there is another RS available or not; it has been a while since i've used them. i may recall reading something about how that is handled by the SQL provider rather than the RS object, so you may not have a way to know ahead of time.

to do this quick and dirty you can just try and catch for the error state when moving to the nextRecordset, as you had indeed hinted at in one of your posts.

Dim intError
intError = 0 ' optimistic setting!

' ignore any/all errors until told otherwise
On Error Resume Next

' attempt a single action
Set rst = rst.NextRecordset

' store the state of the intrinsic "Err" object, which
' reflects the the success or failure of our last action
intError = Err.Number

' resume normal errors
On Error Goto 0

' now handle the error
If intError <> 0 Then
Response.Write &quot;egads! an error occurred!&quot;
Response.Write &quot; perhaps the next rs is not there?&quot;
Response.Write &quot; the error code is &quot; & intError
Else
' action was a success. process accordingly.
...
End If

note that accessing the &quot;Err&quot; object only pertains to your very last action, so quickly check for the error and then quickly turn back on regular error handling.

see

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsobjErr.asp

for more info on the Err object


best of luck to you!
-f!

 

[tklear]

Thanks everyone for your help. I've been successful in determining if I have multiple recordsets.

There doesn't seem to be a property that can be called, however, the error trapping ideas provided allow me to count the occurrences while suppressing the error.

Thanks again!