#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Posts
    101
    Rep Power
    9

    Either BOF or EOF is True, or the current record has been deleted.


    I'm attempting to read thru a list of SKUs and pull that data from the database for each, it works (list all the items) but i get the error

    ADODB.Field error '80020009'
    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

    at the end




    I can't figure out where the infraction occurs (or did i screw soemthing up?)


    Code:
    set conn=server.createobject("adodb.connection")
    conn.open conn_string
    
    '-------------------------------------
    ' Open text File
    '-------------------------------------
    '
    Set fs=Server.CreateObject("Scripting.FileSystemObject")
    Set f=fs.OpenTextFile(Server.MapPath("new.txt"), 1)
    
    
    
    
    do while f.AtEndOfStream = false 'loop text
    
    'get data for db
    titleNum=trim(f.ReadLine)
    
    'connect and pull the information from the db
    
    strSql = "SELECT * FROM new7 WHERE SKU='" & titleNum & "' ORDER BY SKU DESC"
    
    'read records
    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open strSql, conn
    %>
     
    <%=rs("sku")%>, <%=rs("itemname")%>
     
    <% 
    Response.Write("<br />")
    rs.close
    set rs=Nothing
    
    loop 'loop text
    %>
    
    
    <% 'close text
    f.Close
    Set f=Nothing
    Set fs=Nothing
    %>
    
    <% 'close db
    
    conn.close
    %>
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Posts
    101
    Rep Power
    9

    Got it


    I got it...
    Had to do soem fierce searching so for anything else who reads this, one of the records in the text file had no correponding record in teh database

    I added

    If not RS.BOF and not RS.EOF then

    END IF

    around teh output and it works fine.



    I think it means If not Beginning of File and Not Beginning of File... not sure how it determines a blank value not to display tho
  4. #3
  5. Bring the noise....
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2005
    Location
    Brighton
    Posts
    125
    Rep Power
    72
    It's ALWAYS good practise to sanity check your recordsets with:

    Code:
    IF RS.BOF AND RS.EOF THEN
        response.write "Debug: No records returned.
    ELSE
        blah blah blah blah
    END IF
    The RS.BOF AND RS.EOF basically means, the recordset pointer is at the beginning of the recordset, and at the end of the recordset.

    I.e. It's empty.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    217
    Rep Power
    24
    Originally Posted by palagrim
    Code:
    IF RS.BOF AND RS.EOF THEN
    ...
    The above code will work, however, it is not safe. That should have been "IF RS.BOF OR RS.EOF THEN", which I believe is safer.

    For instance, the following code will fail spectacularly at the end of the RecordSet:
    Code:
    ....
    rs.MoveFirst
    While Not (rs.EOF And rs.BOF)
        MsgBox rs.Fields(1)
        rs.MoveNext
    Wend
    ...
    "Imagination is more important than knowledge" - Albert Einstein
    Physics Forums | Physics & Astronomy Stories
  8. #5
  9. Bring the noise....
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2005
    Location
    Brighton
    Posts
    125
    Rep Power
    72
    Originally Posted by ingenuity
    The above code will work, however, it is not safe. That should have been "IF RS.BOF OR RS.EOF THEN", which I believe is safer.

    For instance, the following code will fail spectacularly at the end of the RecordSet:
    Code:
    ....
    rs.MoveFirst
    While Not (rs.EOF And rs.BOF)
        MsgBox rs.Fields(1)
        rs.MoveNext
    Wend
    ...
    I see what you're saying - but I use the IF RS.BOF AND RS.EOF to check if the recordset has anything in it before I do the while loop.

    Code:
    IF RS.BOF AND RS.EOF THEN
       response.write "No Data Returned..."
    ELSE
       WHILE NOT RS.EOF
          response.write RS("Field Name")
          RS.movenext
       WEND
    END IF
    I've always done it this way so maybe I'm missing something?
  10. #6
  11. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    if rs.bof AND rs.eof is the only correct way to check for an empty ADO recordset. There are other conditions you can create by code where rs.bof OR rs.eof becomes true but the recordset isn't empty.

    Keep using what you're using palagrim
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    217
    Rep Power
    24
    ... to check if the recordset has anything in it before I do the while loop.
    If you are using the conditional statement for that purpose, then it is absolutely fine. No, you are not missing anything at all.

    I just wanted to point out under which circumstance that particular statement might prove to be fragile and hence, might result in a run-time error (as depicted by my post above).
    "Imagination is more important than knowledge" - Albert Einstein
    Physics Forums | Physics & Astronomy Stories
  14. #8
  15. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    Originally Posted by ingenuity
    If you are using the conditional statement for that purpose, then it is absolutely fine. No, you are not missing anything at all.

    I just wanted to point out under which circumstance that particular statement might prove to be fragile and hence, might result in a run-time error (as depicted by my post above).
    But it's not fragile. rs.bof and rs.eof is the proper way to test for an empty recordset, and you must test before you use any operations on a recordset to prevent errors. So your example is not valid, since you'd never use an empty recordset test in such a while loop. You may however use an OR test, but that's not to test for an empty recordset, that would just test to see if your cursor movement has reached either end of the rs. Which is completely unrelated to an empty rs test.

    Comments on this post

    • ingenuity agrees
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    217
    Rep Power
    24
    So your example is not valid, since you'd never use an empty recordset test in such a while loop.
    Yes indeed, my example is not valid in this case. You are right.

    As soon as I read the code, I thought that this condition (rs.BOF and rs.EOF) would also be used to test for beginning or the end of the recordset in order to carry out any transaction on the recordset (hence, I thought it would be used in a loop).
    "Imagination is more important than knowledge" - Albert Einstein
    Physics Forums | Physics & Astronomy Stories
  18. #10
  19. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    I just wanted this to be clear for anyone who may google to this topic Thanks for your help here!
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo