Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RDO and multiple resultsets open

Posted on 1998-10-01
9
Medium Priority
?
710 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

670 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