The rowset does not support fetching backwards

M

MightyCat

Registered User.

Local time

Today, 03:18

Joined

Sep 11, 2012

Messages

17

• Mar 10, 2014

• #1

I've found the same answer all over the place but I can't implement the suggestions and have my code work. Can anyone see what I'm doing wrong? I really just want to get the recordcount of the recordset to display on my form.

Code:

Public Sub CallSP()
    Dim cnn As ADODB.Connection
    Dim rsSQL As ADODB.Recordset
    Dim CmdSQL As ADODB.Command
    
    Dim rsACCESS As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    
    Dim tName As String
    
    tName = "tTABLE"
    
    
    cnn.ConnectionString = gSQLConnection
      
    cnn.Open
    Set CmdSQL = New ADODB.Command
    CmdSQL.ActiveConnection = cnn
    
    With CmdSQL
        
    .CommandText = "[SP_STOREDPROC]"
    .CommandType = adCmdStoredProc
    .Parameters("@inCompany").Value = "PARAM1"
    .Parameters("@CNStatus").Value = "PARAM2"
    
    
   End With
    
    
    Set rsSQL = New ADODB.Recordset
    
    rsSQL.Open CmdSQL, , adOpenKeyset, adLockOptimistic
    rsSQL.MoveLast
 
    rsSQL.Close
    Set rsSQL = Nothing
    cnn.Close
    Set cnn = Nothing

What am I missing????

Thanks All!

 

jdraw

Super Moderator

Staff member

Local time

Today, 06:18

Joined

Jan 23, 2006

Messages

15,525

• Mar 10, 2014

• #2

You aren't asking for a record count.
Maybe debug.print rsSQL.RecordCount

from M$oft
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

 

M

MightyCat

Registered User.

Local time

Today, 03:18

Joined

Sep 11, 2012

Messages

17

• Mar 10, 2014

• #3

That doesn't work. I get a -1 which got me searching for an answer.

I found that the cursor is defaulted to forward only therefore, I needed to change my cursor type. But, it still doesn't work.

 

jdraw

Super Moderator

Staff member

Local time

Today, 06:18

Joined

Jan 23, 2006

Messages

15,525

• Mar 10, 2014

• #4

Not sure why your recordset would be defaulted -- you specified.
rsSQL.Open CmdSQL, , adOpenKeyset, adLockOptimistic

Did you try the debug.print?
Did you try adOpenDynamic as cursor type?

 

M

MightyCat

Registered User.

Local time

Today, 03:18

Joined

Sep 11, 2012

Messages

17

• Mar 10, 2014

• #5

I actually did. I tried a whole bunch of variations.

It's really weird...I can't figure it out

 

jdraw

Super Moderator

Staff member

Local time

Today, 06:18

Joined

Jan 23, 2006

Messages

15,525

• Mar 10, 2014

• #6

Is there something special with your stored procedure that would prevent any movement??

What exactly is the recordset vs the tName/ttable?

Have you tried to iterate the recordset and do a physical count?

Do while not rsSQL.eof
i = i +1
rsSQL.movenext
Loop

 

jdraw

Super Moderator

Staff member

Local time

Today, 06:18

Joined

Jan 23, 2006

Messages

15,525

• Mar 10, 2014

• #7

Try this
Set rsSQL = New ADODB.Recordset

rsSQL.Open CmdSQL, , adOpenStatic, adLockOptimistic
MsgBox rsSQL.RecordCount

Taken From
http://www.ozgrid.com/forum/showthread.php?t=82309

 

M

MightyCat

Registered User.

Local time

Today, 03:18

Joined

Sep 11, 2012

Messages

17

• Mar 11, 2014

• #8

Thank you for the suggestions.

I already set the recordset to New.ADODB.

I can iterate through the recordset b/c I write it out to a local table. But, I added the loop code provided which it did fine, but when I then tried to rst.MoveFirst, I got 'rowset position cannot be restarted'.

I'm so stumped. I used the below:

Set rsSQL = New ADODB.Recordset

rsSQL.Open CmdSQL, , adOpenKeyset, adLockOptimistic

Do While Not rst.EOF
TotalRec = TotalRec + 1
rst.MoveNext
Loop

rst.MoveFirst