wbwillson
asked on
ADODB.Field Error '8002009'
I'm getting this error on my page:
ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.
I know that this error usually means that the record I'm trying to pull from a table doesn't exist or the table is empty. But this isn't actually true in my case.
I'm trying to Pull two records from the same table.
Example:
I'd like my page to Display:
Doe, Jon A Mr. - Originator - Created Task - - 10/7/2004 <-- Line 1
Doe, Jon A Mr. - Originator - Tasked - Sample, Jane B Mrs. - Action Code - 10/7/2004 <-- Line 2
Sample, Jane B. Mrs. - Action Code - Delegated - Example, Don C. - Action Code - 10/7/2004 <-- Line 3
Etc...etc..
Everything works fine except when I try to get the second person's info from the table. So this works:
Doe, Jon A Mr. - Originator - Created Task - - 10/7/2004 <-- Line 1
Doe, Jon A Mr. - Originator - Tasked - 1612 - Action Code - 10/7/2004 <-- Line 2
But If I try to get the name for ID 1612 is Get the ADODB.Field Error '80020009' - But the record does exist! I doesn't work at all if I try to put a If not (not myRs.EOF AND myRs.BOF) Then into the code - then it nothing shows up.
My Code:
<!--#INCLUDE FILE="inc_Config.inc"-->
<!--#INCLUDE FILE="inc_New_Task_Header. asp"-->
<%
'Count the Number of records in tblActionCode
Set objConn = Server.CreateObject("ADODB .Connectio n")
objConn.Open ConnString
Set Rec = objConn.execute("SELECT COUNT(*) FROM tblTaskHistory WHERE TaskerID = '" & Session("TaskerID") & "'")
MyCount = Rec(0)
%>
<TABLE Border="1" Cellspacing="0" cellpadding="0" bordercolor="#C0C0C0" bgcolor="#C0C0C0" width="65%">
<TR>
<TD bgcolor="#7F7F7F" colspan="2" align="center"><Font Face="Arial" Size="2" Color="White">Delegation Entries: </Font>
<Font Face="Arial" Size="3" Color="White"><B><%=Rec(0) %></B></Fo nt>
</TD>
</TR>
<TR>
<%
'Database Connection to tblTaskHistory
Dim myConn
Set myConn = Server.CreateObject("ADODB .Connectio n")
myConn.Open ConnString 'from your config.inc file
mySQL = "SELECT * FROM tblTaskHistory WHERE TaskerID = '" & Session("TaskerID") & "'"
Set myRs = myConn.Execute(mySQL)
%>
<%While Not myRs.EOF%>
<TD align="center">
<%
'Get the User's Name
'Database Connection to tblPersonnelRoster
Dim Pers1Conn
Set Pers1Conn = Server.CreateObject("ADODB .Connectio n")
Pers1Conn.Open ACConnString 'from your config.inc file
Pers1SQL = "SELECT * FROM tblPersonnelRoster WHERE ID ='" & myRs("Person1ID") & "'"
Set Pers1Rs = Pers1Conn.Execute(Pers1SQL )
%>
<%
'Get the Person2's Name
'Database Connection to tblPersonnelRoster
Dim Pers2Conn
Set Pers2Conn = Server.CreateObject("ADODB .Connectio n")
Pers2Conn.Open ACConnString2 'from your config.inc file
Pers2SQL = "SELECT [Last],[First],[Grade] FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
Set Pers2Rs = Pers2Conn.Execute(Pers2SQL )
%>
<%
'Get the Action Type
'Database Connection to tblTaskAction
Dim ATConn
Set ATConn = Server.CreateObject("ADODB .Connectio n")
ATConn.Open ConnString 'from your config.inc file
ATSQL = "SELECT * FROM tblTaskAction WHERE TaskActionID ='" & Trim(myRs("ActionID")) & "'"
Set ATRs = ATConn.Execute(ATSQL)
%>
<%=Pers1Rs("Last")%>,  ;<%=Pers1R s("First") %>
<%=Pers1Rs("MiddleInitial" )%>, <%=Pers1Rs ("Grade")% > -
<%=myRs("Person1Title")%> - <%=ATRs("TaskAction")%> -
<%=MyRs("Person2ID")%>
<%=myRs("Person2Title")%> - <%=myRs("ActionDate")%>
<%'=Pers2Rs("Last")%> 'This is causing the ERROR - If I take it out it works fine but only displays the persons
'ID instead of the Last Name
<%myRs.movenext%><%WEND%>
<%
'Close Connection to Pers1
Pers1Rs.Close
Pers1Conn.Close
Set Pers1Conn = Nothing
Set Pers1Rs = Nothing
Set Pers1SQL = Nothing
%>
<%
'Close Connection to Pers2
Pers2Rs.Close
Pers2Conn.Close
Set Pers2Conn = Nothing
Set Pers2Rs = Nothing
Set Pers2SQL = Nothing
'Close Connection to ATConn
ATRs.Close
ATConn.Close
Set ATConn = Nothing
Set ATRs = Nothing
Set ATSQL = Nothing
%>
<%
'Close Connection to tblTaskHistory
myRs.Close
myConn.Close
Set myConn = Nothing
Set myRs = Nothing
%>
</TD>
</TR>
</TABLE>
<%
'Close Count Connection
objConn.Close
Set objConn = Nothing
Set Rec = Nothing
%>
ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.
I know that this error usually means that the record I'm trying to pull from a table doesn't exist or the table is empty. But this isn't actually true in my case.
I'm trying to Pull two records from the same table.
Example:
I'd like my page to Display:
Doe, Jon A Mr. - Originator - Created Task - - 10/7/2004 <-- Line 1
Doe, Jon A Mr. - Originator - Tasked - Sample, Jane B Mrs. - Action Code - 10/7/2004 <-- Line 2
Sample, Jane B. Mrs. - Action Code - Delegated - Example, Don C. - Action Code - 10/7/2004 <-- Line 3
Etc...etc..
Everything works fine except when I try to get the second person's info from the table. So this works:
Doe, Jon A Mr. - Originator - Created Task - - 10/7/2004 <-- Line 1
Doe, Jon A Mr. - Originator - Tasked - 1612 - Action Code - 10/7/2004 <-- Line 2
But If I try to get the name for ID 1612 is Get the ADODB.Field Error '80020009' - But the record does exist! I doesn't work at all if I try to put a If not (not myRs.EOF AND myRs.BOF) Then into the code - then it nothing shows up.
My Code:
<!--#INCLUDE FILE="inc_Config.inc"-->
<!--#INCLUDE FILE="inc_New_Task_Header.
<%
'Count the Number of records in tblActionCode
Set objConn = Server.CreateObject("ADODB
objConn.Open ConnString
Set Rec = objConn.execute("SELECT COUNT(*) FROM tblTaskHistory WHERE TaskerID = '" & Session("TaskerID") & "'")
MyCount = Rec(0)
%>
<TABLE Border="1" Cellspacing="0" cellpadding="0" bordercolor="#C0C0C0" bgcolor="#C0C0C0" width="65%">
<TR>
<TD bgcolor="#7F7F7F" colspan="2" align="center"><Font Face="Arial" Size="2" Color="White">Delegation Entries: </Font>
<Font Face="Arial" Size="3" Color="White"><B><%=Rec(0)
</TD>
</TR>
<TR>
<%
'Database Connection to tblTaskHistory
Dim myConn
Set myConn = Server.CreateObject("ADODB
myConn.Open ConnString 'from your config.inc file
mySQL = "SELECT * FROM tblTaskHistory WHERE TaskerID = '" & Session("TaskerID") & "'"
Set myRs = myConn.Execute(mySQL)
%>
<%While Not myRs.EOF%>
<TD align="center">
<%
'Get the User's Name
'Database Connection to tblPersonnelRoster
Dim Pers1Conn
Set Pers1Conn = Server.CreateObject("ADODB
Pers1Conn.Open ACConnString 'from your config.inc file
Pers1SQL = "SELECT * FROM tblPersonnelRoster WHERE ID ='" & myRs("Person1ID") & "'"
Set Pers1Rs = Pers1Conn.Execute(Pers1SQL
%>
<%
'Get the Person2's Name
'Database Connection to tblPersonnelRoster
Dim Pers2Conn
Set Pers2Conn = Server.CreateObject("ADODB
Pers2Conn.Open ACConnString2 'from your config.inc file
Pers2SQL = "SELECT [Last],[First],[Grade] FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
Set Pers2Rs = Pers2Conn.Execute(Pers2SQL
%>
<%
'Get the Action Type
'Database Connection to tblTaskAction
Dim ATConn
Set ATConn = Server.CreateObject("ADODB
ATConn.Open ConnString 'from your config.inc file
ATSQL = "SELECT * FROM tblTaskAction WHERE TaskActionID ='" & Trim(myRs("ActionID")) & "'"
Set ATRs = ATConn.Execute(ATSQL)
%>
<%=Pers1Rs("Last")%>, 
<%=Pers1Rs("MiddleInitial"
<%=myRs("Person1Title")%> - <%=ATRs("TaskAction")%> -
<%=MyRs("Person2ID")%>
<%=myRs("Person2Title")%> - <%=myRs("ActionDate")%>
<%'=Pers2Rs("Last")%> 'This is causing the ERROR - If I take it out it works fine but only displays the persons
'ID instead of the Last Name
<%myRs.movenext%><%WEND%>
<%
'Close Connection to Pers1
Pers1Rs.Close
Pers1Conn.Close
Set Pers1Conn = Nothing
Set Pers1Rs = Nothing
Set Pers1SQL = Nothing
%>
<%
'Close Connection to Pers2
Pers2Rs.Close
Pers2Conn.Close
Set Pers2Conn = Nothing
Set Pers2Rs = Nothing
Set Pers2SQL = Nothing
'Close Connection to ATConn
ATRs.Close
ATConn.Close
Set ATConn = Nothing
Set ATRs = Nothing
Set ATSQL = Nothing
%>
<%
'Close Connection to tblTaskHistory
myRs.Close
myConn.Close
Set myConn = Nothing
Set myRs = Nothing
%>
</TD>
</TR>
</TABLE>
<%
'Close Count Connection
objConn.Close
Set objConn = Nothing
Set Rec = Nothing
%>
What if you substitute this line of code:
Pers2SQL = "SELECT [Last],[First],[Grade] FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
With such:
Pers2SQL = "SELECT * FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
?? Does it help?
Huji
Pers2SQL = "SELECT [Last],[First],[Grade] FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
With such:
Pers2SQL = "SELECT * FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
?? Does it help?
Huji
I mean why do you use those brackets (i.e. [ and ] ) in your SQL command? You don't need them! The correct format of that line is:
Pers2SQL = "SELECT Last,First,Grade FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
Wish I can help
huji
Pers2SQL = "SELECT Last,First,Grade FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
Wish I can help
huji
ASKER
Huji,
Thanks for the response...I originally had my SQL statement like that and it doesn't seem to matter. I think this the error is because I'm trying to pull two different records from the same table within the same cycle. Any other ideas?
Bill
Thanks for the response...I originally had my SQL statement like that and it doesn't seem to matter. I think this the error is because I'm trying to pull two different records from the same table within the same cycle. Any other ideas?
Bill
ASKER
Huji,
Actually I do need use brackets around Last and First because those are Reserved Words within SQL Server so you must enclose them in [ ] brackets, I could leave the brackets off of the remaining fields but I find that it makes it a bit easier for me to read anyway. The brackets don't cause any harm to the statement.
Bill
Actually I do need use brackets around Last and First because those are Reserved Words within SQL Server so you must enclose them in [ ] brackets, I could leave the brackets off of the remaining fields but I find that it makes it a bit easier for me to read anyway. The brackets don't cause any harm to the statement.
Bill
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Huji,
If I put the statement:
IF Pers2Rs.EOF=True THEN
response.write "Huji was true!"
End If
within the Loop it is TRUE. If I put it outside the Loop its FALSE.
The problem is that Pers2 is populated, it's not empty - how I can I get the data for Pers2 to show up inside the loop?
Bill
If I put the statement:
IF Pers2Rs.EOF=True THEN
response.write "Huji was true!"
End If
within the Loop it is TRUE. If I put it outside the Loop its FALSE.
The problem is that Pers2 is populated, it's not empty - how I can I get the data for Pers2 to show up inside the loop?
Bill
Send the whole code to me please. Or follow this post..
We first have to focus on this line of code:
Pers2SQL = "SELECT [Last],[First],[Grade] FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
Are you sure that myRs("person2ID") contains a value that also appears on ID column of tblPersonnelRoster?
Check it out again.
Huji
Pers2SQL = "SELECT [Last],[First],[Grade] FROM tblPersonnelRoster WHERE ID ='" & Trim(myRs("Person2ID")) & "'"
Are you sure that myRs("person2ID") contains a value that also appears on ID column of tblPersonnelRoster?
Check it out again.
Huji
i notice tha in ur line1, there is no id and ur code within the loop does not check for EOF or BOF .. so what heppens when within the loop, the first record tries to fetch the "Last" name for an empty id??
ASKER
Rejojohny,
Thanks for the reply. I actually check for the existence of the ID in another page which then calls this one. So this page would be called upon unless an ID exists.
Bill
Thanks for the reply. I actually check for the existence of the ID in another page which then calls this one. So this page would be called upon unless an ID exists.
Bill
i thought u were listing all the records here .. u might have to explain exactly what u r trying to do and also provide more code ..
Thanks for the A
Huji
Huji
ASKER