How to do a record lookup with mutiple datareaders ?

microsoft.public.dotnet.framework.adonet

11 Nov 2006 5:14 AM

Gordon

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

--
Gordon

13 Nov 2006 2:57 PM

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!
*************************************************

Show quote

"Gordon" <Gor***@discussions.microsoft.com> wrote in message
news:69C8F27A-6619-4BA2-8FE2-7A876715BCE8@microsoft.com...
> 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
> --
> Gordon

13 Nov 2006 3:41 PM

Gordon

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.

--
Gordon

Show quote

"Cowboy (Gregory A. Beamer)" wrote:

> 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!
> *************************************************
> "Gordon" <Gor***@discussions.microsoft.com> wrote in message
> news:69C8F27A-6619-4BA2-8FE2-7A876715BCE8@microsoft.com...
> > 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
> > --
> > Gordon
>
>
>

13 Nov 2006 7:07 PM

Gordon

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.

--
Gordon

Show quote

"Gordon" wrote:

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