|
 |
asp_databases thread: Problem with nultiple record sets and multiple connections
Message #1 by risho@c... on Tue, 28 May 2002 21:00:31
|
|
I have an Access database containing three tables: Projects, Exercises,
POCs. I used the Access wizard to create a join between Projects and POCs.
I am using the following to declare, open a connection / record set. Then
I use the sql string from the wizard that I assing to a variable and I
open the connection:
' Connection/Recordset module retrives records from Projects table
Set MyDB = Server.CreateObject("ADODB.Connection")
Set MyRS = Server.CreateObject("ADODB.Recordset")
Dim dbPath
dbPath = Server.MapPath("../Databases/Project_Exercise_042602.mdb")
MyDB.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
' Build our query based on the input.
MySQL = "SELECT Projects.Name, Projects.Description, Projects.Time_Line,
Projects.Time_Frame, " _
& "Projects.Participants, Projects.Spike, Projects.POR,
Projects.StatusReviewName, " _
& "Projects.StatusReviewFile, Projects.DesignReviewName,
Projects.DesignReviewFile, " _
& "Projects.QuadChartName, Projects.QuadChartFile, POSs.Titile,
POSs.Fname, POSs.Lname, " _
& "POSs.Dept, POSs.Organization, POSs.Phone, POSs.UC_Email,
POSs.C_Email, POSs.POCFlag, " _
& "Projects.ID " _
& "FROM Projects LEFT JOIN POSs ON Projects.POC_Id = POSs.POC_Id " _
& "WHERE (((Projects.StatusReviewName)<>'False')) OR
(((Projects.StatusReviewFile)<>'False')) " _
& "OR (((Projects.DesignReviewName)<>'False')) OR
(((Projects.DesignReviewFile)<>'False')) OR " _
& "(((Projects.QuadChartName)<>'False')) OR
(((Projects.QuadChartFile) Is Not Null));"
MyRS.CursorLocation = 3
MyRS.Open MySQL, MyDB
Next I repeat the previous code but for the Exercise table except that
the 'where' clause contains the condition, value of the record id from the
above record set:
' Connection/Recordset module retrives records from Exercises table
Set MyDB1 = Server.CreateObject("ADODB.Connection")
Set MyRS1 = Server.CreateObject("ADODB.Recordset")
Dim dbPath1
dbPath1 = Server.MapPath("../Databases/Project_Exercise_042602.mdb")
MyDB1.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath1
' Build our query based on the input.
MySQL1 = "SELECT Exercises.Event, Exercises.ID, Exercises.Project_Id " _
& "FROM Exercises " _
& "WHERE Exercises.Project_Id = " & MyRS("ID") & ";"
MyRS1.CursorLocation = 3
MyRS1.Open MySQL1, MyDB1
Loop cycles through the recordset, finds the first record displays the
record Name. Then it reads the next piece of vb code and cycles through
the first record of the second record set and if the Exercises
name "Event" is present it will display is as many times as there are
corresponding Events present, associated through a foreign key in the
table.
<%
For intRecord = 1 To MyRS.RecordCount
MyRexCount=MyRexCount+1
%>
<tr valign=top bgcolor=White>
<td class=SearchParam><font class=Data width="70"><%=MyRS
("Name")%></font></td>
<td><font class=Data width="100">
<%
If MyRS1("Event") <> "" OR NOT ISNULL(MyRS1("Event")) Then
For intRecord1 = 1 To MyRS1.RecordCount
Response.Write MyRS1("Event") & "<br><br>"
MyRS1.MoveNext
if MyRS1.EOF then
Exit For
End if
Next
Else
Response.Write " " 'Blank space
End If %>
</font></td>
<td><font class=Data width="100"><%= MyRS("Time_Line") %
></font></td>
<td><font class=Data width="100"><%= MyRS("Time_Frame") %
></font></td>
<td><font class=Data width="100"><%= MyRS("Description" ) %
></font></td>
<td><font class=Data width="60"><%= MyRS("Participants") %
></font></td>
<td><font class=Data width="70"><%= MyRS("Spike") %></font></td>
<td><font class=Data width="70"><%= MyRS("POR") %></font></td>
</tr><%
MyRS.MoveNext
if MyRS.EOF then
Exit For
End if
Next
It works the first time around but on the second cycle I get a error as
follows:
The line 163 is the line location of the second 'for' loop.
error '80020009'
Exception occurred.
/exercise_database/Projects/PrBriefs.asp, line 163
Is the error occuring due to the multiple recordsets or connections. The
logic seem fine, I mean, its quite rudimentary, yet it crashes.
Can you help in any way?
Thank you.
Eric
Message #2 by "Drew, Ron" <RDrew@B...> on Wed, 29 May 2002 07:53:51 -0400
|
|
Try changing your check for EOF to this so it does not do an End For
do while not rs.EOF
Response.Write rs.Fields(0) & "<br>" 'display any fields you
like
rs.movenext
loop
-----Original Message-----
From: risho@c... [mailto:risho@c...]
Sent: Tuesday, May 28, 2002 5:01 PM
To: ASP Databases
Subject: [asp_databases] Problem with nultiple record sets and multiple
connections
I have an Access database containing three tables: Projects, Exercises,
POCs. I used the Access wizard to create a join between Projects and
POCs.
I am using the following to declare, open a connection / record set.
Then
I use the sql string from the wizard that I assing to a variable and I
open the connection:
' Connection/Recordset module retrives records from Projects table Set
MyDB =3D Server.CreateObject("ADODB.Connection")
Set MyRS =3D Server.CreateObject("ADODB.Recordset")
Dim dbPath
dbPath =3D Server.MapPath("../Databases/Project_Exercise_042602.mdb")
MyDB.Open "PROVIDER=3DMICROSOFT.JET.OLEDB.4.0;DATA SOURCE=3D" & dbPath
' Build our query based on the input.
MySQL =3D "SELECT Projects.Name, Projects.Description,
Projects.Time_Line,
Projects.Time_Frame, " _
& "Projects.Participants, Projects.Spike, Projects.POR,
Projects.StatusReviewName, " _
& "Projects.StatusReviewFile, Projects.DesignReviewName,
Projects.DesignReviewFile, " _
& "Projects.QuadChartName, Projects.QuadChartFile, POSs.Titile,
POSs.Fname, POSs.Lname, " _
& "POSs.Dept, POSs.Organization, POSs.Phone, POSs.UC_Email,
POSs.C_Email, POSs.POCFlag, " _
& "Projects.ID " _
& "FROM Projects LEFT JOIN POSs ON Projects.POC_Id =3D
POSs.POC_Id
" _
& "WHERE (((Projects.StatusReviewName)<>'False')) OR
(((Projects.StatusReviewFile)<>'False')) " _
& "OR (((Projects.DesignReviewName)<>'False')) OR
(((Projects.DesignReviewFile)<>'False')) OR " _
& "(((Projects.QuadChartName)<>'False')) OR
(((Projects.QuadChartFile) Is Not Null));"
MyRS.CursorLocation =3D 3
MyRS.Open MySQL, MyDB
Next I repeat the previous code but for the Exercise table except that
the 'where' clause contains the condition, value of the record id from
the
above record set:
' Connection/Recordset module retrives records from Exercises table Set
MyDB1 =3D Server.CreateObject("ADODB.Connection")
Set MyRS1 =3D Server.CreateObject("ADODB.Recordset")
Dim dbPath1
dbPath1 =3D Server.MapPath("../Databases/Project_Exercise_042602.mdb")
MyDB1.Open "PROVIDER=3DMICROSOFT.JET.OLEDB.4.0;DATA SOURCE=3D" & dbPath1
' Build our query based on the input.
MySQL1 =3D "SELECT Exercises.Event, Exercises.ID, Exercises.Project_Id
"
_
& "FROM Exercises " _
& "WHERE Exercises.Project_Id =3D " & MyRS("ID") & ";"
MyRS1.CursorLocation =3D 3
MyRS1.Open MySQL1, MyDB1
Loop cycles through the recordset, finds the first record displays the
record Name. Then it reads the next piece of vb code and cycles through
the first record of the second record set and if the Exercises
name "Event" is present it will display is as many times as there are
corresponding Events present, associated through a foreign key in the
table.
<%
For intRecord =3D 1 To MyRS.RecordCount
MyRexCount=3DMyRexCount+1
%>
<tr valign=3Dtop bgcolor=3DWhite>
<td class=3DSearchParam><font class=3DData
width=3D"70"><%=3DMyRS
("Name")%></font></td>
<td><font class=3DData width=3D"100">
<%
If MyRS1("Event") <> "" OR NOT ISNULL(MyRS1("Event")) Then
For intRecord1 =3D 1 To MyRS1.RecordCount
Response.Write MyRS1("Event") & "<br><br>"
MyRS1.MoveNext
if MyRS1.EOF then
Exit For
End if
Next
Else
Response.Write " " 'Blank space
End If %>
</font></td>
<td><font class=3DData width=3D"100"><%=3D MyRS("Time_Line") %
></font></td>
<td><font class=3DData width=3D"100"><%=3D MyRS("Time_Frame")
%
></font></td>
<td><font class=3DData width=3D"100"><%=3D MyRS("Description"
) %
></font></td>
<td><font class=3DData width=3D"60"><%=3D MyRS("Participants")
%
></font></td>
<td><font class=3DData width=3D"70"><%=3D MyRS("Spike")
%></font></td>
<td><font class=3DData width=3D"70"><%=3D MyRS("POR")
%></font></td>
</tr><%
MyRS.MoveNext
if MyRS.EOF then
Exit For
End if
Next
It works the first time around but on the second cycle I get a error as
follows:
The line 163 is the line location of the second 'for' loop.
error '80020009'
Exception occurred.
/exercise_database/Projects/PrBriefs.asp, line 163
Is the error occuring due to the multiple recordsets or connections. The
logic seem fine, I mean, its quite rudimentary, yet it crashes.
Can you help in any way?
Thank you.
Eric
|
|
|
View Cart
