multiple recordsets returned

BCullenward · July 28th, 2006, 01:18 PM

I have a stored procedure that returns 5-6 recordsets and want to be able to dynamically access the one I need in case the stored procedure ever changes.

Basically what I am trying to do is this:

Code:

set ors = cmd.execute

for each recordset in ors
   for each field in ors.fields
      if ors.fields(field).Name = "The Field Name I Need" THEN
         'Something good happens
         found = true
         exit for
      END IF
   NEXT
   if not found then
      ors.NextRecordset
   else
      exit for
   end if
next

Can anyone offer any assistance or a point in the right direction on how I can get a count of how many recordsets there are after the stored procedure executes?

Thanks

dparsons · July 28th, 2006, 02:14 PM

Why not just do a rowcount on your recordset object? That will tell you the number of rows returned from your sp.

"The one language all programmers understand is profanity."

BCullenward · July 28th, 2006, 02:19 PM

There are 4 or 5 different recordsets. Ie. 5 tables are returned, I can access the data on each by doing ors.NextRecordset however, I don't care how many rows are in each recordset, I need to know how many recordsets there are. If someone added 3 tables tomorrow that would be returned I don't want my code to break.

dparsons · July 28th, 2006, 02:53 PM

Do not take the following as a direct attack or that im degrading you as a programmer; that is a very very poor design as it, obviously, does not scale at all. As far as counting individual recordsets, it has been a while since i did any hard core ASP coding (in .NET it would be dataset.tables.count) so I am not exactly sure how to answer your question. I would hit up www.asp101.com or www.4guysfromrolla.com both have extensive information on Classic and .NET alike.

Consider revising your logic though so that no matter how many tables may be added to the database it doesnt break your code.

"The one language all programmers understand is profanity."

BCullenward · July 28th, 2006, 03:18 PM

Quote:

quote:Originally posted by dparsons
Do not take the following as a direct attack or that im degrading you as a programmer; that is a very very poor design as it, obviously, does not scale at all. As far as counting individual recordsets, it has been a while since i did any hard core ASP coding (in .NET it would be dataset.tables.count) so I am not exactly sure how to answer your question. I would hit up www.asp101.com or www.4guysfromrolla.com both have extensive information on Classic and .NET alike.

Consider revising your logic though so that no matter how many tables may be added to the database it doesnt break your code.

"The one language all programmers understand is profanity."

How is it a poor design when I'm looking at each field in each recordset to make sure it matches with what I am looking for? I'm looking for field "X", and was going to scan each recordset to see if it contained field "X", if it did, it would process it and skip looking.

If this is a poor design, what would be a better way to do it?

dparsons · July 28th, 2006, 03:35 PM

Quote:

quote:Originally posted by BCullenward
If someone added 3 tables tomorrow that would be returned I don't want my code to break.

Now, maybe I misread that statement but I infer that as "If someone adds tables to my database, it will break the code" By that inference, it is a bad design because if tables are added to the database the code breaks. The code should run independantly of what is happening in the database and it shouldnt care if there are 3 tables or 300.

I am pretty sure I don't understand what you are doing, you are scanning the recordsets and comparing the Column names to X to see if it matches...wouldnt it just be eaiser to call the table where column x exists.

"The one language all programmers understand is profanity."

BCullenward · July 28th, 2006, 03:56 PM

Quote:

quote:Originally posted by dparsons

Quote:

quote:Originally posted by BCullenward

Quote:

If someone added 3 tables tomorrow that would be returned I don't want my code to break.

Now, maybe I misread that statement but I infer that as "If someone adds tables to my database, it will break the code" By that inference, it is a bad design because if tables are added to the database the code breaks. The code should run independantly of what is happening in the database and it shouldnt care if there are 3 tables or 300.

I am pretty sure I don't understand what you are doing, you are scanning the recordsets and comparing the Column names to X to see if it matches...wouldnt it just be eaiser to call the table where column x exists.

"The one language all programmers understand is profanity."

It is a stored procedure that I am calling, the stored procedure (not written by me) returns several recordsets. I am trying to make my code that processed the recordset dynamic so if the stored procedure is modified and for some ungodly reason someone decides the middle of the stored procedure would be a good place to add another select statement causing the recordset order to change and more recordsets to be added to the results, the page will break.

Here's an example:

Returns now
RECORDSET 1
   fnm lnm
   John Smith
   Joe Doe
   Sue Frankson

RECORDSET 2
   desc price
   Apple .99
   Orange .78

RECORDSET 3
   desc bonus
   Mgr 18000
   Sec 13000

now someone edits the stored procedure so the results are now:
RECORDSET 1
   fnm lnm Desc
   John Smith mgr
   Joe Doe sec
   Sue Frankson clk

RECORDSET 2
   IDCD city ST
   124 NYC NY
   154 SCT SC

RECORDSET 3
   desc price
   Apple .99
   Orange .78

RECORDSET 4
   desc bonus
   Mgr 18000
   Sec 13000

If this happens the page will crash, I was going to scan all the recordsets and if it had colX in it it would be processed a certain way (where colx is unique to the recordset).

dparsons · July 28th, 2006, 04:16 PM

Ok the first thing you need to do is smack your DBA and secondly, the only available method that even remotely would work here is .NextRecordSet which will allow you to move through multiple record sets, problem is, it really isnt dynamic because you need to know how many recordsets are returned e.g., you would have to use multiple Do While loops as opposed to a single For Loop. I cannot find any documentation that points out a way to count how many recordsets are returned via a query, but you may have some luck with .NextRecordSet

"The one language all programmers understand is profanity."