May 01, 2012 07:35 AM|magicmike2011|LINK
Hi there, As mentioned above you can't nest a datareader within another datareader.
However, what you are wanting to do is very easily accomplished with a redesign to your sql statement.
Rather than selecting one set of data, and returning it to database to be used in another query, you could use a JOIN statement in your sql code.
Say I have 2 tables. Each table has related data and share a primary key. Lets use the following tables and columns:
Now to do an SQL select statement where I can gather all rows that intersect by using an INNER JOIN on the tables:
SELECT TB1.Id, TB1.FirstName, TB2.LastName
FROM dbo.Table1 AS TB1
INNER JOIN dbo.Table2 AS TB2
ON TB1.Id = TB2.Id
This will get all rows that share a primary key between the 2 tables. Basically, it creates a virtual table that simulates combining the 2 tables together on the primary key.
the TB1 and TB2 are just aliases we create for the tables so that we can tell the query engine what table each column we are requesting is located in. This is to resolve any ambiguous calls between data.
And when you are gathering the data from the datareader object, you just access the column names in the same way you normally would (do not use the appended table aliases from in the sql but rather, just the column name as normal)
Hope this helps :)