Solved

RDO and multiple resultsets open

Posted on 1998-10-01
9
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month7 days, 23 hours left to enroll

617 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