Handling multiple SQL queries

[David Wessell]

Hi..

I'm trying to work out the syntax for having multiple SQL queries..
I'm connecting to a Firebird DB using ADO.

What I'm attempting to do is something like this:

Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
rs.Open "QUERY Goes here", dbMain, adOpenKeyset, adLockPessimistic

While Not rs.EOF
//Do stuff with result
rs1.Open"Query using rs(0) as a variable, dbMain, adOpenKeyset,
adLockPessimistic

rs.MoveNext
Wend

I'm getting run time errors, that would lead me to believe I'm doing
this the wrong way. Do I need to create a second recordSet? Can I use
rs(0) as a variable in that new query?

Thanks
David

 

[RB Smissaert]

I think you will have to move the rs to an array with rs.GetRows and then
loop through the array.
The other option might be to get all the data from the first query.

RBS

 

[Guest]

David,

what kind of error messages do you get? The only possible problem I see is
that you need to make sure that you close rs1 when you're done with it.
Otherwise, the Open will fail on the next iteration.. So,

While Not rs.EOF
//Do stuff with result
rs1.Open"Query using rs(0) as a variable, dbMain, adOpenKeyset,
adLockPessimistic
'do stuff with rs1
'then close it
rs1.Close

rs.MoveNext
Wend

 

[RB Smissaert]

Forgot to say: and close rs after doing rs.GetRows.

RBS

 

[David Wessell]

Hi Vergel,

It's an automation error, and it's on the first iteration that it
occurs.. I did track it down, and it was a stoopid typo

Thanks to everyone though.. Now adding in a second recordset (rs1) and
closing it is working just fine..

Does opening up multiple recordsets take up much overhead?

Thanks
David