Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

RDO and multiple resultsets open

Posted on 1998-10-01
9
Medium Priority
?
711 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
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.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

927 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