Recordset strangeness

[NFI]

What-ho,

here's a section of some code I've written inside a timer event;

rs.Open sql, DB, adOpenDynamic, adLockReadOnly

If Not (rs.EOF And rs.BOF) Then
rs.MoveLast

Nothing too complicated going on; I open a recordset based on some arbitrary SQL, check that the recordset actually contains data and, if it does, move to the last record returned.

However, when I run this, I get the following error;

Run-time error '-2147217884 (80040e24)':
Rowset does not support fetching backward.

The problem stems from the fact that, although EOF and BOF are both true for this recordset, VB blunders on through the IF statement and starts executing the code within it anyway.

Has anyone got any idea at all why this is happening to me? This is currently top of my all time most stupid things VB has ever done to me.


Any help at all will be much appreciated,

thanks,

Paul

 

[Scoty]

Paul,
I used to get that alot too.

Try this:

rs.Open sql, DB, adOpenDynamic, adLockOptimistic

That should do it
hth
Scoty :

"Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[balves]

Perhaps something is going on somewhere else in the code?

The code works okay for me, with an empty recordset or a recordset with rows.

 

[MikeCox]

The way your IF statement reads, EOF and BOF must both be true for the whole statement to be true. The only time this condition will be met is if there are no records in the recordset when you open it, so you will hit an error trying to MoveLast. I would consider changing it to read:
---------------------------------
If RS.RecordCount > 0 Then 'or "If RS.RecordCount Then"
RS.MoveLast
End If
---------------------------------

Another way is this:

---------------------------------
If Not RS.EOF And Not RS.BOF Then
RS.MoveLast
End If
---------------------------------

Hope that helps!

~Mike

Any man willing to sacrifice liberty for security deserves neither liberty nor security.

-Ben Franklin

 

[NFI]

Hiya,

thanks for the advice chaps. I've looked into this a bit more and I may have mislead you a touch. In fact, what's happening here, it appears, is that my recordset is, in fact, populated, but when it throws the Rowset does not support fetching backwards, it sneakily sets BOF and EOF to true, which is what fooled me. In fact, the error seems to be due to the type of cursor I'm using; adOpenForwardOnly. However, I decided to change the cursor type to adOpenStatic, but this didn't help; I still get the same error.

The only reason I'm doing this is to attempt to retrieve the RowCount from the recordset, which means I have to move to the end of the recordset first. Perhaps there's some other way of doing this? I'm considering just loading the contents of the recordset into a collection and doing all my operations on it there, but that would annoy me for at least the next half and hour, because there must be some way of doing this - stupid recordsets!

Thanks again for your advice, any more will be just as gratefully received,


Paul

 

[balves]

What driver and what database are you connected to?

 

[NFI]

Hiya,

I'm connecting to an SQL Server 7 database, using ADO. The recordset I'm working on here is actually being returned by a stored procedure within the DB itself - I wondered if this might affect the type of cursor I can use against the returned results?

Thanks again,

Paul