Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recordset is EOF (2000)

    I am opening an ADO recordset in code and although I am using an SQL SELECT string that should bring back records, the recordset is opening with both EOF and BOF set to true. I know that the SQL brings back records because I've printed it in the immediate window then pasted the text into the SQL view of a query, where it works fine and returns records.

    The SQL string I am using (copied from the immediate window) is:

    SELECT tbl_WeeklyTotals.BranchCode, dbo_branch.branch_sht_name, tbl_WeeklyTotals.AgrWeek, tbl_WeeklyTotals.Amount, tbl_WeeklyTotals.NumTxs, tbl_WeeklyTotals.Units, tbl_WeeklyTotals.Footfall, [NumTxs]/[Footfall] AS FootfallConversion, [Amount]/[NumTxs] AS AveTxVal, [Amount]/[Units] AS AveUnitVal, [Units]/[NumTxs] AS AveUnitsPerTx FROM dbo_branch RIGHT JOIN tbl_WeeklyTotals ON dbo_branch.branch_code = tbl_WeeklyTotals.BranchCode WHERE tbl_WeeklyTotals.BranchCode = '00001' AND tbl_WeeklyTotals.AgrWeek Like '2004*' ORDER BY tbl_WeeklyTotals.AgrWeek;

    The code I am using to open the recordset is:

    rst.Open strSQL, CurrentProject.Connection, , , adCmdText

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordset is EOF (2000)

    Do you test for EOF immediately after opening the recordset, or in the GetYearFiguresRst function? If the latter, try testing in the procedure/function in which you open the recordset. Do you get the same result?

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset is EOF (2000)

    The EOF problem first came up in the calling function. This instances the recordset variable then sends it to GetYearFiguresRst to be populated. When it came back from this function with EOF = True I put a breakpoint in GetYearFiguresRst and tested it there. It was still EOF.

    Since then I have tried moving all the code that was in GetYearFiguresRst into the calling function, so that the whole lot runs in one function and rst variables don't get passed anywhere. Unfortunately I am still getting EOF.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: recordset is EOF (2000)

    With an ADO recordset, check for EOF and BOF. Just checking for EOF is not sufficient. The other test you can make in ADO is RecordCount, which will return a True if there are records. In that case, you can move to the first record before doing anything else.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset is EOF (2000)

    That was it, and that's a good one to remember, use % not *. Thanks.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset is EOF (2000)

    No problem. I think the underscore is used instead of the question mark too. Quite frankly, they would make life a lot easier if they put a note about ADO wild cards in the MSDN under the definitions of ADO's .EOF and .BOF properties. Because every time a developer who is used to * in Access, goes to ADO and uses * in his SQL, he's going to get EOF and BOF, not realizing it's the * acting as a true Asterick. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I was snagged several times by this, because it happened months apart, and I completely forgot to use the percent symbol! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> More then likely I'll snag myself again a few months from now. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    Glad to help out.... <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>

  7. #7
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invalid Wild Card in ADO Sets EOF = True (Access)

    Every so often I get snagged by this one myself. ADO doesn't use the asterisk (*) for a wildcard. ADO uses the percent symbol %. So replace the * with a percent symbol, and you should be good to go.

    Odd, isn't it? You can paste the SQL directly into a query, and it works, but ADO does not see the * as a wildcard.

    Hope this helps! <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •