Rowset does not support fetching backwards 1

[pungy]

VB 6 Enterprise Edition

I am getting the error message "rowset does not support fetching backward" when I try to do "recordset.movelast". I have read in other post that I am apparently using a "read forward only" recordset but I don't know how to fix it. I was under the understanding that "CursorType" is the culprit. Doesn't "adOpenKeyset" allow direction in all ways?
Please help if you can. Below in my code. These routines ar in a BAS module:

Public Sub OpenConnections()
Set pconSports = New ADODB.Connection
        pconSports.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Program Files\FootballPool\Sports.mdb;Persist Security Info=False"
        'ReportPath = App.Path
    pconSports.Open
End Sub

Public Sub OpenWeekWinners()
On Error GoTo ErrHandler
    Set pcmdWeekWinners.ActiveConnection = pconSports
    pcmdWeekWinners.CommandType = adCmdTable
    pcmdWeekWinners.CommandText = "WeekWinners"
    Set rstWeekWinners = New ADODB.Recordset
    rstWeekWinners.LockType = adLockOptimistic
    rstWeekWinners.CursorLocation = adUseClient
    rstWeekWinners.CursorType = adOpenKeyset
    rstWeekWinners.Open pcmdWeekWinners
    Exit Sub
ErrHandler:
    Call MsgBox(Err.Description, vbCritical, "OpenAWeekWinners")
End Sub

On the Form in the Form_Load event, I have the following code:

call OpenConnections
Call OpenWeekWinners

Here is the code that is causing the problem:

SQL = "Select * from WeekWinners where PoolPlayer = '" & lblPlayerName(Counter).Caption & "' "
    SQL = SQL & "order by WeekNumber ;"
    Set rstWeekWinners = pconSports.Execute(SQL)
    If rstWeekWinners.EOF And rstWeekWinners.BOF Then
        lblAccumulative(HoldPlayerCount) = 0
    Else
        [B][I][U]rstWeekWinners.MoveLast[/U][/I][/B]
        lblAccumulative(HoldPlayerCount).Caption = rstWeekWinners.Fields.Item("Accumulative").Value
    End If

 

[dilettante]

There is no such thing as a client keyset cursor, so you're defaulting to a forward only cursor. Try requesting a static cursor instead.

Client cursors work with a copy of the data, and don't sync with the underlying provider automagically.

 

[pungy]

I changed

rstWeekWinners.CursorType = adOpenKeyset

to

rstWeekWinners.CursorType = adOpenStatic

I still have the same problem.

 

[dilettante]

I took a harder look at this and it seems you have a lot of red herrings in there.

Your WeekWinners() subroutine pretty much doesn't do anything. It's a scrambled set of operations in the Recordset that appears lucky not to throw an exception itself... seemingly trying to set up a Command object to do something. Maybe open a table named WeekWinners, or invoke a stored procedure of the same name? You do exit with the Recordset still open, probably with a server-side keyset cursor.

The Connection was previously opened server-side, so you can't change it to a client cursor here anyway.


Most of that is irrelevant, because in the last blob of code you listed you throw the Recordset away anyhow... by calling Execute on the Connection to obtain a new Recordset. This Recordset will be forward-only and read-only, because that's all you can get from Execute on a Connection.

The returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type.

 

[pungy]

dilettante: I appreciate your feedback. Can you be more specific. I understand you are saying that code in "OpenWeekWinners" doesn't do much but I don't understand WHY or HOW to fix it.

I would like to learn the right way of doing things. I am relying on experts like yourself to explain to me the correct way.

Just a few minutes ago, I was able to fix the problem of this thread. Here is the fix:

Public Sub OpenConnections()
Set pconSports = New ADODB.Connection
    pconSports.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
       "C:\Program Files\FootballPool\Sports.mdb;Persist Security Info=False"
    'ReportPath = App.Path
    [b][u][i]pconSports.CursorLocation = adUseClient[/i][/u]
[/b]pconSports.Open
End Sub

the "pconSports.CursorLocation = adUseClient" statement was moved from the "OpenWeekWinners" routine to the "OpenConnectios" routine.
Thanks,
Sam

 

[dilettante]

Your change works because you changed the cursor location to a client-side cursor. Since a client cursor can only have a cursor type of static, you now have a static (and not a forward-only) cursor.

This may not be what you want.

The reason that OpenWeekWinners() appears to me to be useless is that after calling it you turn around and throw away rstWeekWinners and get a new one by calling Execute on the Connection:

[tt]Set rstWeekWinners = pconSports.Execute(SQL)[/tt]


The error handling in OpenWeekWinners() sets off alarm bells too. It looks entirely random and doesn't do anything to actually handle any exceptions that might be raised. Ok for debugging I suppose, but even then it does nothing to tell you what failed. You might as well let the unhandled exception warnings fly, at least you'd know what line errored out.


Normally you'd want to use a server-side cursor with a Jet database unless you have something specific in mind like batch updating. The quote I posted above makes it clear where your error was: don't use a Connection.Execute if you need a cursor with more functionality than it provides.

Changing the cursor location to a client cursor is not a proper fix. Use Recordset.Open and ask for the type of cursor you need there.


The MSDN documentation you should be using with VB6 covers all of this under "ADO API Reference." Studying that material will be far faster for you than asking in a forum if you don't already know how to use ADO. The section you'll want next is called "Recordset Object Properties, Methods, and Events." Look at the Open method.

Alternatively you might try any of several tutorial web sites. Be aware that the Web is full of erroneous information though. For example Timesheets MTS Software - Visual Basic 6 ADO Tutorial comes up early on a Google search, but it is chock-full of misinformation. This is why you must go to the authoritative reference materials or to books by respected authors.

You might also try one of the other more focused forums here like forum709

 

[pungy]

dilettante: Thanks again for your advice. I will take a look at the various locations you mention.

I do have another question and will make it a new Post. The SUbject Title will be system Variables. Would you mind looking at it?

Sam