Avoiding EOF Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Avoiding EOF Error

Author

Topic

Bobba Buoy
Starting Member

36 Posts

Posted - 2003-06-20 : 07:14:03

I am querying a sql server db from asp/ado. I am trying to get data from a list of people but all of them may not have the data I am looking for. As a result, when the query gets to that person/record, I get the message:

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

How can I avoid this message? Here is my code:

sql="SELECT TrngDate, Effort, UnitMeas FROM TrainingData WHERE PartID = " & CLng(iPartID)

Set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 2

Do While Not rs.eof
.
.
.
rs.MoveNext
Loop

I also used the conn.Execute approach.

This a recurring issue for me. Any ideas?

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-20 : 08:07:18

Bobba,

Sounds like you've diagnosed the problem down to searching for a user that has no record in the resultset?

If that's the case, (maybe I don't understand this?), either stop expecting to find all users in the result set, - 0R - modify the query to ensure all users are returned in the resultset, users with no result return zero or NULL for those columns.

I can't propose a solution for the SQL query because there is no "User" in column in the SELECT list. It's not clear to me how TrngDate, Effort, UnitMeas can identify a User at all.

How about posting some more information - sample data, what is TrainingData's strucure? How do you identify a user?

Sam

uberman
Posting Yak Master

159 Posts

Posted - 2003-06-20 : 11:25:38

try wrapping your loop in a bof check


if not rs.bof then
  'rs loop here
else
  response.write "no records returned"
end if

the recordset also has a status property that you could check if this does not work for you

rhinof
Starting Member

1 Post

Posted - 2003-06-22 : 21:51:33

Bobba,
why are you using a do..while..loop Statement ?
in a statment like this the condicional expression is checked at the end of each run, so the first condicional evaluation happens only after the first run...so if you try to access the recordset fields (when your query didnt return any resualts) you will get a eof | bof error.
try using other control structure like while(!oRs.oef){...}

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-23 : 04:30:05

You would find...

noresults = false
rs.open sql, conn, 1, 2
if not rs.eof then
yourresults = rs.getrows()
rs.close
else
noresults = true
end if
set rs = nothing
if noresults = false then
for resultsloop = 0 to ubound (yourresults, 1)
response.write "result " & resultsloop & " " & yourresults (0,resultsloop)
next
else
response.write "Empty results set"
end if

... more efficient.

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources