T
Terp
Hello,
Undoubtedly this answer is in front of my nose, but I can't figure it out or
find it anywhere.
Using VB.NET, Access database.
I have successfully built a series of nested queries that return results
from three interrelated tables, reading the results into datareaders. I can
iterate through the three datareaders and build the results that I want, but
I suspect there are more efficient ways to do what I need. The only way I
have been able to get this to work is by establishing one connection for the
first query, and then in a nested loop keep making connections to satisfy
the second query, and within that loop there is another loop that keeps
making connections to satisfy the third query. This works fine on my very
small test database, but I know users will have much larger ones. I can't
use UNION, because the tables have differing numbers of fields.
Let's say I have three tables: Neighborhoods, Houses, and Residents (not my
real database!). Neighborhood.nID = Houses.nID and Houses.hID =
Residents.hID. The trick is that I might not have houses for every
neighborhood, and I might not have residents for every house. But I need to
get all the neighborhoods, even the ones without houses, and I need to get
all of the houses, even the ones without residents. Conversely, every
resident has to have a house, every house has to have a neighborhood.
So my simplified code is:
Establish first connection
Get all the neighborhoods
Read the first neighborhood
Build a node for a TreeView
Establish/reestablish second connection
Get all the houses in that neighborhood
Read the first house
Build a node for a TreeView
Establish/reestablish third connection
Get all the residents in that house
Read the first resident
Build a node for a TreeView
Loop
Close third connection when we've found all the residents for
that house
Loop
Close second connection when we've found all the houses for that
neighborhood
Loop
Close first connection when we've found all the neighborhoods
Any and all advice gratefully received.
Thanks,
Chris
Undoubtedly this answer is in front of my nose, but I can't figure it out or
find it anywhere.
Using VB.NET, Access database.
I have successfully built a series of nested queries that return results
from three interrelated tables, reading the results into datareaders. I can
iterate through the three datareaders and build the results that I want, but
I suspect there are more efficient ways to do what I need. The only way I
have been able to get this to work is by establishing one connection for the
first query, and then in a nested loop keep making connections to satisfy
the second query, and within that loop there is another loop that keeps
making connections to satisfy the third query. This works fine on my very
small test database, but I know users will have much larger ones. I can't
use UNION, because the tables have differing numbers of fields.
Let's say I have three tables: Neighborhoods, Houses, and Residents (not my
real database!). Neighborhood.nID = Houses.nID and Houses.hID =
Residents.hID. The trick is that I might not have houses for every
neighborhood, and I might not have residents for every house. But I need to
get all the neighborhoods, even the ones without houses, and I need to get
all of the houses, even the ones without residents. Conversely, every
resident has to have a house, every house has to have a neighborhood.
So my simplified code is:
Establish first connection
Get all the neighborhoods
Read the first neighborhood
Build a node for a TreeView
Establish/reestablish second connection
Get all the houses in that neighborhood
Read the first house
Build a node for a TreeView
Establish/reestablish third connection
Get all the residents in that house
Read the first resident
Build a node for a TreeView
Loop
Close third connection when we've found all the residents for
that house
Loop
Close second connection when we've found all the houses for that
neighborhood
Loop
Close first connection when we've found all the neighborhoods
Any and all advice gratefully received.
Thanks,
Chris