Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 28th, 2006, 01:18 PM
Authorized User
 
Join Date: Mar 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default multiple recordsets returned

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

 
Old July 28th, 2006, 02:14 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
 
Old July 28th, 2006, 02:19 PM
Authorized User
 
Join Date: Mar 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old July 28th, 2006, 02:53 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
 
Old July 28th, 2006, 03:18 PM
Authorized User
 
Join Date: Mar 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old July 28th, 2006, 03:35 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
 
Old July 28th, 2006, 03:56 PM
Authorized User
 
Join Date: Mar 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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).


 
Old July 28th, 2006, 04:16 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."





Similar Threads
Thread Thread Starter Forum Replies Last Post
closing and cleaning up multiple recordsets Loralee Access 1 September 26th, 2005 05:46 PM
Returned Error chuck BOOK: Beginning VB.NET Databases 6 February 13th, 2005 04:07 PM
Multiple Recordsets from Multiple Tables TSEROOGY Classic ASP Databases 2 December 28th, 2004 12:45 PM
manipulate multiple returned values from oracle st herlenrosa Pro VB Databases 1 November 12th, 2003 03:24 AM
manipulate multiple values returned by stored proc herlenrosa Beginning VB 6 3 November 11th, 2003 06:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.