Solved

RDO and multiple resultsets open

Posted on 1998-10-01
9
705 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

777 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