|
 |
asp_databases thread: Parent/Child Recordset Loops
Message #1 by "Ray Murphy" <raymurphy@g...> on Thu, 22 Nov 2001 19:19:44
|
|
Ladies and Gentlemen ....
Having a problem with some code, and would appreciate some assistance.
I'm trying to loop through all the records in a parent table, and for each
record in the parent then loop through all the children for that parent.
The parent and child tables can be joined on the 'memb_id' column.
Here is the relevant portion of the code :
<%
' Create the DB Connection
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRSParent = Server.CreateObject("ADODB.Recordset")
Set objRSChild = Server.CreateObject("ADODB.Recordset")
objConn.Open strConnect ' strConnect from INCLUDED file
parentSQL= "select memb_id, memb_name FROM members"
childSQL = "SELECT memb_id, memb_address, memb_dob" _
& " FROM memdetails WHERE memb_id = " & objRSParent("memb_id")
' Open and populate the RecordSet
objRS.Open parentSQL, objConn
' Specific processing for the parent will go here
' Now attempt to loop through all Parents, and then
' for each Parent loop through all Children .........
Do While Not objRSParent.EOF
ParentCount = ParentCount + 1
Set objRSChild = objConn.Execute(childSQL)
Do While Not objRSChild.EOF
ChildCount = ChildCount + 1
' Specific processing for the child will go here
objRSChild.MoveNext
Loop ' finished looping through the children
objRSParent.MoveNext
Loop ' finished looping through the parents
%>
When the code runs, I get the following ADODB error :
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding
to the requested name or ordinal
and the line flagged as being in error is the line with :
childSQL = "SELECT memb_id, memb_address, memb_dob" ....
Can anyone please see what may be causing the error in my code, or is there
perhaps a more elegant way of achieving what I'm attempting to do ?
Thanks.
Ray
Message #2 by "Kim Iwan Hansen" <kimiwan@k...> on Thu, 22 Nov 2001 21:19:21 +0100
|
|
Hi Ray,
There's an article here which shows you an efficient way to deal with
exactly that main category/sub category tables thing.
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=154
-Kim
-----Original Message-----
From: Ray Murphy [mailto:raymurphy@g...]
Sent: 22. november 2001 19:20
To: ASP Databases
Subject: [asp_databases] Parent/Child Recordset Loops
Ladies and Gentlemen ....
Having a problem with some code, and would appreciate some assistance.
I'm trying to loop through all the records in a parent table, and for each
record in the parent then loop through all the children for that parent.
The parent and child tables can be joined on the 'memb_id' column.
Here is the relevant portion of the code :
<%
' Create the DB Connection
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRSParent = Server.CreateObject("ADODB.Recordset")
Set objRSChild = Server.CreateObject("ADODB.Recordset")
objConn.Open strConnect ' strConnect from INCLUDED file
parentSQL= "select memb_id, memb_name FROM members"
childSQL = "SELECT memb_id, memb_address, memb_dob" _
& " FROM memdetails WHERE memb_id = " & objRSParent("memb_id")
' Open and populate the RecordSet
objRS.Open parentSQL, objConn
' Specific processing for the parent will go here
' Now attempt to loop through all Parents, and then
' for each Parent loop through all Children .........
Do While Not objRSParent.EOF
ParentCount = ParentCount + 1
Set objRSChild = objConn.Execute(childSQL)
Do While Not objRSChild.EOF
ChildCount = ChildCount + 1
' Specific processing for the child will go here
objRSChild.MoveNext
Loop ' finished looping through the children
objRSParent.MoveNext
Loop ' finished looping through the parents
%>
When the code runs, I get the following ADODB error :
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding
to the requested name or ordinal
and the line flagged as being in error is the line with :
childSQL = "SELECT memb_id, memb_address, memb_dob" ....
Can anyone please see what may be causing the error in my code, or is there
perhaps a more elegant way of achieving what I'm attempting to do ?
Thanks.
Ray
$subst('Email.Unsub')
Message #3 by David Cameron <dcameron@i...> on Fri, 23 Nov 2001 13:05:22 +1100
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C173C3.543BFBDE
Content-Type: text/plain
Debug your SQL String. To do this use response.write to dump it to the
screen. Copy it into Access QBE/ SQL Server QA and test it.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Ray Murphy [mailto:raymurphy@g...]
Sent: Friday, 23 November 2001 5:20 AM
To: ASP Databases
Subject: [asp_databases] Parent/Child Recordset Loops
Ladies and Gentlemen ....
Having a problem with some code, and would appreciate some assistance.
I'm trying to loop through all the records in a parent table, and for each
record in the parent then loop through all the children for that parent.
The parent and child tables can be joined on the 'memb_id' column.
Here is the relevant portion of the code :
<%
' Create the DB Connection
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRSParent = Server.CreateObject("ADODB.Recordset")
Set objRSChild = Server.CreateObject("ADODB.Recordset")
objConn.Open strConnect ' strConnect from INCLUDED file
parentSQL= "select memb_id, memb_name FROM members"
childSQL = "SELECT memb_id, memb_address, memb_dob" _
& " FROM memdetails WHERE memb_id = " & objRSParent("memb_id")
' Open and populate the RecordSet
objRS.Open parentSQL, objConn
' Specific processing for the parent will go here
' Now attempt to loop through all Parents, and then
' for each Parent loop through all Children .........
Do While Not objRSParent.EOF
ParentCount = ParentCount + 1
Set objRSChild = objConn.Execute(childSQL)
Do While Not objRSChild.EOF
ChildCount = ChildCount + 1
' Specific processing for the child will go here
objRSChild.MoveNext
Loop ' finished looping through the children
objRSParent.MoveNext
Loop ' finished looping through the parents
%>
When the code runs, I get the following ADODB error :
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding
to the requested name or ordinal
and the line flagged as being in error is the line with :
childSQL = "SELECT memb_id, memb_address, memb_dob" ....
Can anyone please see what may be causing the error in my code, or is there
perhaps a more elegant way of achieving what I'm attempting to do ?
Thanks.
Ray
$subst('Email.Unsub')
Message #4 by "Ray Murphy" <raymurphy@g...> on Fri, 23 Nov 2001 19:18:04
|
|
> Hi Ray,
>
> There's an article here which shows you an efficient way to deal with
> exactly that main category/sub category tables thing.
>
> http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=154
>
> -Kim
>
>
>
> -----Original Message-----
> From: Ray Murphy [mailto:raymurphy@g...]
> Sent: 22. november 2001 19:20
> To: ASP Databases
> Subject: [asp_databases] Parent/Child Recordset Loops
>
>
> Ladies and Gentlemen ....
>
> Having a problem with some code, and would appreciate some assistance.
> I'm trying to loop through all the records in a parent table, and for
each
> record in the parent then loop through all the children for that parent.
> The parent and child tables can be joined on the 'memb_id' column.
>
> Here is the relevant portion of the code :
> <%
> ' Create the DB Connection
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objRSParent = Server.CreateObject("ADODB.Recordset")
> Set objRSChild = Server.CreateObject("ADODB.Recordset")
>
> objConn.Open strConnect ' strConnect from INCLUDED file
> parentSQL= "select memb_id, memb_name FROM members"
>
> childSQL = "SELECT memb_id, memb_address, memb_dob" _
> & " FROM memdetails WHERE memb_id = " & objRSParent("memb_id")
>
> ' Open and populate the RecordSet
> objRS.Open parentSQL, objConn
>
> ' Specific processing for the parent will go here
>
> ' Now attempt to loop through all Parents, and then
> ' for each Parent loop through all Children .........
> Do While Not objRSParent.EOF
> ParentCount = ParentCount + 1
>
> Set objRSChild = objConn.Execute(childSQL)
>
> Do While Not objRSChild.EOF
> ChildCount = ChildCount + 1
> ' Specific processing for the child will go here
> objRSChild.MoveNext
> Loop ' finished looping through the children
>
> objRSParent.MoveNext
> Loop ' finished looping through the parents
> %>
>
> When the code runs, I get the following ADODB error :
> ADODB.Recordset (0x800A0CC1)
> Item cannot be found in the collection corresponding
> to the requested name or ordinal
Ooops,
Silly mistake - problem caused by me trying access the parent recordset
before I had populated it (ie setting childSQl needed to be done after
populating the parent recordset). Should now probably stand in a bucket
of cold custard, but then that's another story ;)
Thanks for that link, Kim, as it contained a better way of achieving what
I wanted anyway....
Ray
>
> and the line flagged as being in error is the line with :
> childSQL = "SELECT memb_id, memb_address, memb_dob" ....
>
> Can anyone please see what may be causing the error in my code, or is
there
> perhaps a more elegant way of achieving what I'm attempting to do ?
>
> Thanks.
>
> Ray
>
>
> $subst('Email.Unsub')
>
>
Message #5 by "Tomm Matthis" <matthis@b...> on Sat, 24 Nov 2001 09:43:35 -0500
|
|
You need to set the childSQL line (with its var from the parent recordset)
*after* you open the parent recordset. Not before.
And yes, there is a more elegant way to do this. Check the MSDN site for
information on "Data Shaping".
-- Tomm
> -----Original Message-----
> From: Ray Murphy [mailto:raymurphy@g...]
> Sent: Thursday, November 22, 2001 7:20 PM
> To: ASP Databases
> Subject: [asp_databases] Parent/Child Recordset Loops
>
>
> Ladies and Gentlemen ....
>
> Having a problem with some code, and would appreciate some assistance.
> I'm trying to loop through all the records in a parent table, and for each
> record in the parent then loop through all the children for that parent.
> The parent and child tables can be joined on the 'memb_id' column.
>
> Here is the relevant portion of the code :
> <%
> ' Create the DB Connection
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objRSParent = Server.CreateObject("ADODB.Recordset")
> Set objRSChild = Server.CreateObject("ADODB.Recordset")
>
> objConn.Open strConnect ' strConnect from INCLUDED file
> parentSQL= "select memb_id, memb_name FROM members"
>
> childSQL = "SELECT memb_id, memb_address, memb_dob" _
> & " FROM memdetails WHERE memb_id = " & objRSParent("memb_id")
>
> ' Open and populate the RecordSet
> objRS.Open parentSQL, objConn
>
> ' Specific processing for the parent will go here
>
> ' Now attempt to loop through all Parents, and then
> ' for each Parent loop through all Children .........
> Do While Not objRSParent.EOF
> ParentCount = ParentCount + 1
>
> Set objRSChild = objConn.Execute(childSQL)
>
> Do While Not objRSChild.EOF
> ChildCount = ChildCount + 1
> ' Specific processing for the child will go here
> objRSChild.MoveNext
> Loop ' finished looping through the children
>
> objRSParent.MoveNext
> Loop ' finished looping through the parents
> %>
>
> When the code runs, I get the following ADODB error :
> ADODB.Recordset (0x800A0CC1)
> Item cannot be found in the collection corresponding
> to the requested name or ordinal
>
> and the line flagged as being in error is the line with :
> childSQL = "SELECT memb_id, memb_address, memb_dob" ....
>
> Can anyone please see what may be causing the error in my code, or is there
> perhaps a more elegant way of achieving what I'm attempting to do ?
>
> Thanks.
>
> Ray
>
>
> $subst('Email.Unsub')
>
Message #6 by "Ken Schaefer" <ken@a...> on Mon, 26 Nov 2001 15:50:59 +1100
|
|
I agree with Tomm - the type of cursor-based logic in the original post is a
performance killer.
If you don't want to use data shaping, then consider using a JOIN instead.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Tomm Matthis" <matthis@b...>
Subject: [asp_databases] RE: Parent/Child Recordset Loops
: You need to set the childSQL line (with its var from the parent recordset)
: *after* you open the parent recordset. Not before.
:
: And yes, there is a more elegant way to do this. Check the MSDN site for
: information on "Data Shaping".
:
: -- Tomm
:
: > -----Original Message-----
: > From: Ray Murphy [mailto:raymurphy@g...]
: > Sent: Thursday, November 22, 2001 7:20 PM
: > To: ASP Databases
: > Subject: [asp_databases] Parent/Child Recordset Loops
: >
: >
: > Ladies and Gentlemen ....
: >
: > Having a problem with some code, and would appreciate some assistance.
: > I'm trying to loop through all the records in a parent table, and for
each
: > record in the parent then loop through all the children for that parent.
: > The parent and child tables can be joined on the 'memb_id' column.
: >
: > Here is the relevant portion of the code :
: > <%
: > ' Create the DB Connection
: > Set objConn = Server.CreateObject("ADODB.Connection")
: > Set objRSParent = Server.CreateObject("ADODB.Recordset")
: > Set objRSChild = Server.CreateObject("ADODB.Recordset")
: >
: > objConn.Open strConnect ' strConnect from INCLUDED file
: > parentSQL= "select memb_id, memb_name FROM members"
: >
: > childSQL = "SELECT memb_id, memb_address, memb_dob" _
: > & " FROM memdetails WHERE memb_id = " & objRSParent("memb_id")
: >
: > ' Open and populate the RecordSet
: > objRS.Open parentSQL, objConn
: >
: > ' Specific processing for the parent will go here
: >
: > ' Now attempt to loop through all Parents, and then
: > ' for each Parent loop through all Children .........
: > Do While Not objRSParent.EOF
: > ParentCount = ParentCount + 1
: >
: > Set objRSChild = objConn.Execute(childSQL)
: >
: > Do While Not objRSChild.EOF
: > ChildCount = ChildCount + 1
: > ' Specific processing for the child will go here
: > objRSChild.MoveNext
: > Loop ' finished looping through the children
: >
: > objRSParent.MoveNext
: > Loop ' finished looping through the parents
: > %>
: >
: > When the code runs, I get the following ADODB error :
: > ADODB.Recordset (0x800A0CC1)
: > Item cannot be found in the collection corresponding
: > to the requested name or ordinal
: >
: > and the line flagged as being in error is the line with :
: > childSQL = "SELECT memb_id, memb_address, memb_dob" ....
: >
: > Can anyone please see what may be causing the error in my code, or is
there
: > perhaps a more elegant way of achieving what I'm attempting to do ?
Message #7 by "Ray Murphy" <raymurphy@g...> on Tue, 27 Nov 2001 08:55:16
|
|
Ken/Tomm,
Having just finished some tests with 'reasonable' volumes of data (ie
thousands of records rather than a couple of hundred), I can now see that
my original code isn't terribly good for performance ;)
Thanks for the advice - I'll follow up your recommendations.
Thanks.
Ray
> I agree with Tomm - the type of cursor-based logic in the original post
is a
> performance killer.
>
> If you don't want to use data shaping, then consider using a JOIN
instead.
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Tomm Matthis" <matthis@b...>
> Subject: [asp_databases] RE: Parent/Child Recordset Loops
>
>
> : You need to set the childSQL line (with its var from the parent
recordset)
> : *after* you open the parent recordset. Not before.
> :
> : And yes, there is a more elegant way to do this. Check the MSDN site
for
> : information on "Data Shaping".
> :
> : -- Tomm
> :
> : > -----Original Message-----
> : > From: Ray Murphy [mailto:raymurphy@g...]
> : > Sent: Thursday, November 22, 2001 7:20 PM
> : > To: ASP Databases
> : > Subject: [asp_databases] Parent/Child Recordset Loops
> : >
> : >
> : > Ladies and Gentlemen ....
> : >
> : > Having a problem with some code, and would appreciate some
assistance.
> : > I'm trying to loop through all the records in a parent table, and for
> each
> : > record in the parent then loop through all the children for that
parent.
> : > The parent and child tables can be joined on the 'memb_id' column.
> : >
> : > Here is the relevant portion of the code :
> : > <%
> : > ' Create the DB Connection
> : > Set objConn = Server.CreateObject("ADODB.Connection")
> : > Set objRSParent = Server.CreateObject("ADODB.Recordset")
> : > Set objRSChild = Server.CreateObject("ADODB.Recordset")
> : >
> : > objConn.Open strConnect ' strConnect from INCLUDED file
> : > parentSQL= "select memb_id, memb_name FROM members"
> : >
> : > childSQL = "SELECT memb_id, memb_address, memb_dob" _
> : > & " FROM memdetails WHERE memb_id = " & objRSParent
("memb_id")
> : >
> : > ' Open and populate the RecordSet
> : > objRS.Open parentSQL, objConn
> : >
> : > ' Specific processing for the parent will go here
> : >
> : > ' Now attempt to loop through all Parents, and then
> : > ' for each Parent loop through all Children .........
> : > Do While Not objRSParent.EOF
> : > ParentCount = ParentCount + 1
> : >
> : > Set objRSChild = objConn.Execute(childSQL)
> : >
> : > Do While Not objRSChild.EOF
> : > ChildCount = ChildCount + 1
> : > ' Specific processing for the child will go here
> : > objRSChild.MoveNext
> : > Loop ' finished looping through the children
> : >
> : > objRSParent.MoveNext
> : > Loop ' finished looping through the parents
> : > %>
> : >
> : > When the code runs, I get the following ADODB error :
> : > ADODB.Recordset (0x800A0CC1)
> : > Item cannot be found in the collection corresponding
> : > to the requested name or ordinal
> : >
> : > and the line flagged as being in error is the line with :
> : > childSQL = "SELECT memb_id, memb_address, memb_dob" ....
> : >
> : > Can anyone please see what may be causing the error in my code, or is
> there
> : > perhaps a more elegant way of achieving what I'm attempting to do ?
>
>
|
|
|
View Cart
