Solved

RDO and multiple resultsets open

Posted on 1998-10-01
9
703 Views
Last Modified: 2013-12-25
I have a component, DBX, that handles my database connectivity.  It has a CreateRecordSet method that takes  SQL and returns a rdoResultSet.  The problem is that if in an application I open a resultset with the CreateRecordSet method and then need to do another CreateRecordSet (using a different resultset object), it uses a second database connection for the second resultset.
Here is an example of the code:
dim result1 as rdoResultset
dim result2 as rdoResultSet

set result1= CreateRecordset("select * from table1")
while not result1.eof

'Next line causes a new connection
set result2 = createrecordset("select * from table2 where x= " & result1("field"))
result2.close
result1.movenext
wend

I can't close the resultset in my DBX component because then it can't be returned to the calling procedure.  At the end of a routine like the one above, I wind up with tons of open connections.
0
Comment
Question by:MichaelJC
9 Comments
 

Expert Comment

by:trillian30
ID: 1496566
What error are you getting?

0
 

Author Comment

by:MichaelJC
ID: 1496567
Edited text of question
0
 

Author Comment

by:MichaelJC
ID: 1496568
Edited text of question
0
 

Expert Comment

by:trillian30
ID: 1496569
If you are using the results from the first query to build the second, why don't you store the results from query 1 in an array, then step through the contents of the array to build the second query. (that way you can close the first resultset)

Additionally, if you only need to return one field from the first query it would be better to do this:

set result1= CreateRecordset("select field from table1")

OR...better yet, do a join and you will only need one resultset:

set result1= CreateRecordset("select * from table2 right join table1 on table2.field = table1.field where table2.x = 'whatever' ")


Hope this helps.


0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Expert Comment

by:abhinavm
ID: 1496570
You haven't written code for DBX object i mean what does it do when you called createRecordset method. It seems that it is problem with that code, because you can open as many record set as you want in a single connection.
0
 

Author Comment

by:MichaelJC
ID: 1496571
In DBX, I basically do a openresultset method.  The only reason it is in a component is to handle error trapping.


0
 

Author Comment

by:MichaelJC
ID: 1496572
The exact error I get is:
ct_cmd_drop(): user api layer: external error: This routine can be called only if the command structure is idle.
0
 

Author Comment

by:MichaelJC
ID: 1496573
Now I am getting the same error even when only one resultset is open.  I do several stored procedure calls and they work and then I get this error.
0
 
LVL 3

Accepted Solution

by:
TheAnswerMan earned 10 total points
ID: 1496574
ok.. I see that you need to Return the Resultset, the Join will work.. but you cant do that since you dont want to return the Joined statement.. only the First one..
So what you have to do...
is Make sure you go to the last record of the Resultset.  this will free your Cursor.

do
lr_resultset.movelast
lr_resultset.movefirst
do while not lr_resultset.eof
   blah blah blah
loop

this sounds like you shouldnt have to.. but on larger resultsets  where you open multiple resultsets on the same RDO connection.. this helps you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now