Looping through a record set

[kanejone]

Hi Guys
I'm getting this error when I loop through a record set. I am connecting to a Stored Procedure that performs the search on my database. I can retrieve the first five set of records and then when there is no more data left in the table I get the following error.

The Rowset was built over a live data feed and cannot be restarted

Here is my loop

Do While objRS.EOF = False
Call MsgBox((objRS.Fields(0) & vbNewLine & objRS.Fields(1) & vbNewLine & _
objRS.Fields(2) & vbNewLine & objRS.Fields(3) & vbNewLine & objRS.Fields(4)), vbInformation)
objRS.MoveNext
Loop
objRS.MoveFirst

Can someone please help me
JK

[simonm]

I supsect you're having the same problem as me and I just started a thread on it before I read your thread:

ADO Commands

The problem is that sometimes stored procedures return 'forward only' recordsets. This must be dictated somehow by the structure (or nature) of the SP but I don't know what.

[paulw]

the cursor type does not work as documented. I have the details somewhere and I will post them later.

Cheers,

P.

[paulw]

Client side cursors are Static only and are adOpenForwardOnly. Changing to a different cursor type (adOpenKeyset, Dynamic or Static) will force the use of a Server Side cursor.

Although Client-side cursors are static they can be updateable. They are useful for managing multiple record sets and using batch operations. The downside is that they are 'disconnected'.

Server side are more flexible in type and quicker, and you can see updates in real time, handled by the Server and not you.

Make sure you have the type you really need.

Treat all of this information with caution as it refers to ADO in its earlier incarnations. Yours might be different

Cheers,

P.

[simonm]

When opening a recordset like this:

SET rsSource = cmSource.Execute

Where rsSource is a recordset and cmSource is a command (that is a stored procedure).

The problem is that you don't get to specify the cursor type, lock type or cursor location.

But, Since my last post, I have worked out how to do it.

According to a document I found on ADO, this method of opening a recordset based on a SP, will always yield a read-only / forward only recordset.

If you open it like this:

SET rsSource = New ADODB.Recordset
rsSource.Open cmSource, , adOpenStatic, adLockReadOnly

you can open the recordset in any manor you wish (the example opening a read-only static recordset.