recordset is EOF (2000)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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. I was snagged several times by this, because it happened months apart, and I completely forgot to use the percent symbol! More then likely I’ll snag myself again a few months from now.

Glad to help out….

Reply | Quote

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. I was snagged several times by this, because it happened months apart, and I completely forgot to use the percent symbol! More then likely I’ll snag myself again a few months from now.

Glad to help out….

Reply | Quote

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

Reply | Quote

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!

Reply | Quote