Handling multiple SQL queries
 

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

Handling multiple SQL queries
 

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

"David Wessell" wrote in message
ps.com...
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

Handling multiple SQL queries
 

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

--
Hope that helps.

Vergel Adriano


"David Wessell" wrote:

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

Handling multiple SQL queries
 

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

RBS

"RB Smissaert" wrote in message
...
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

"David Wessell" wrote in message
ps.com...
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

Handling multiple SQL queries
 

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