How to do a record lookup with mutiple datareaders ?

[Guest]

Hi;

Why don't I get the data rows that I want when I nest two data readers ?

I have two connections and I execute two reads one nested inside the other.

I am trying to use the key fields of the outer read/TABLE to look up
specific records in a second table inner read.

Then inner reader just goes through the entire table.

Example:

open conn1
open conn2

selrecs1 = " Select 1, 2, 3 from a"
hold1 = 1
hold2 = 2

selrecs2 = " Select b.3, b.4, b.5 from a, b where b.3 = '" & hold1 & "' and
b.4 = '" & hold2& "'



while rdr1.read
var1 = rdr1.getValue(0)
var2 = rdr1.getValue(1)

while rdr2.read
var3 = rdr2.getvalue(0)
var4 - rdr2.getvalue(1)
' I NEED ONLY MATCHED RECORDS RETURNED WITH THIS READER
wend
wend

close conn1
close conn2

Thanks for your insights

 

[Cowboy \(Gregory A. Beamer\)]

There are a variety of issues.

1. space before ampersand hold2& = hold2 &

selrecs2 = " Select b.3, b.4, b.5 from a, b where b.3 = '" & hold1 & "' and
b.4 = '" & hold2& "'

2. You are not really nesting, as you have nothing to link reader 1 and 2
together. I.E., there is nothing in reader 1 that is set up in the query of
reader 2. As they stand now, they are independent readers

Let's try a different way:

What are you putting into the system?
What are the expected results?
What are you querying (data wise)?

With the answer to those three questions, your issue might be solved. And,
it might be in a completely different manner than nested readers.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************

 

[Guest]

Ok

What I am trying to accomplish here is to read records from a table and
write them
to a text file in a summary record/ detail record order.

So I read a record with a summary value from table A , my first data reader,
and then add this line in my text file. Then I read all the matching detail
values which
sum up to the table A's value and write them directly after the first line
with the second reader.

The text file will be used by a Cobol program.

I am sure that there is probably a way to do this all in SQL, but I am not a
Joe Celko so I am doing this via ADODB datareaders.

Hope this gives a better understanding of why I am placing one reader
inside another.

 

[Guest]

Hi;

I solved my own problem by changing the SELECT

from:

" Select b.3, b.4, b.5 from a, b where b.3 = '" & hold1 & "' and

b.4 = '" & hold2 & "'

to:

" Select b.3, b.4,b.5 from b where 3 = '" & hold1 & "' and

b.4 = '" & hold2 & "'

So my problem was a conflict between the implied INNER JOIN and the WHERE.