Rowset does not support scrolling backward

Question

I am trying to query a MySQL database with the below code:

'declare the variables 
Dim Connection
Dim Recordset
Dim SQL

'declare the SQL statement that will query the database
SQL = "SELECT * FROM CUSIP"

'create an instance of the ADO connection and recordset objects
Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")

'open the connection to the database
Connection.Open "DSN=CCS_DSN;UID=root;PWD=password;Database=CCS"

Recordset.CursorType=adOpenDynamic

'Open the recordset object executing the SQL statement and return records 

Recordset.Open SQL,Connection
Recordset.MoveFirst

If Recordset.Find ("CUSIP_NAME='somevalue'") Then
    MsgBox "Found"
Else
    MsgBox "Not Found"
End If


'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing

Whenever I execute the above I get an error 'rowset does not support scrolling backward', any suggestions?

according to http://msdn.microsoft.com/en-us/library/ee275542%28v=bts.10%29.aspx you don't use .Find correctly. — Ekkehard.Horner, Jan 02 '13 at 13:13

GSerg · Accepted Answer · 2013-01-02T22:56:20.480

7

adOpenDynamic is not declared in VBScript and therefore equals Empty, which gets converted to 0 when you assign the CursorType property.
0 is adOpenForwardOnly, and forward only does not support moving backwards, an ability the Find method wants.

You should replace adOpenDynamic with its literal value:

Recordset.CursorType = 2 'adOpenDynamic

To avoid this class of errors altogether, place Option Explicit as the first line of your script.

edited Jan 02 '13 at 22:56

answered Jan 02 '13 at 12:53

GSerg

76,472
17
159
346

That did the trick ! Thanks !! btw would you know why 'recordset.recordcount' returns -1 when there are 5 rows in the database and cursortype changed to dynamic ? – Telson Alva Jan 02 '13 at 13:26
@TelsonAlva Because it is [documented](http://msdn.microsoft.com/ru-ru/library/windows/desktop/ms676701(v=vs.85).aspx) to do so: `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.` – GSerg Jan 02 '13 at 13:38
Oh, i understand now ! So i need to change the cursor type to static in order to get the count. Otherwise since it is dynamic there are possibilities for the record to change dynamically. Thanks ! – Telson Alva Jan 02 '13 at 13:43
One last question, my Find statement is not returning a match. Any reasons ? i have set the exact string which is under the coloum CUSIP_NAME but still i get 'Not Found' – Telson Alva Jan 02 '13 at 13:44
@TelsonAlva Because, as Ekkehard.Horner [noted](http://stackoverflow.com/questions/14122308/rowset-does-not-support-scrolling-backward#comment19548549_14122308), you're not using `Find` properly. It's a sub and it does not return a value. It moves to a record. If it doesn't find one, `.EOF` becomes `true`. – GSerg Jan 02 '13 at 13:53

score 0 · Answer 2 · answered Jan 02 '13 at 12:50

0

That is because the rowset does not permit backward moves; as the error message suggests. Your code is not using them; so you should replace the line

Recordset.CursorType=adOpenDynamic with Recordset.CursorType=adOpenForwardOnly (or the equivalent value 0)

Better leave the line altogether; the default is forward cursor.

answered Jan 02 '13 at 12:50

Amit

1,836
15
24

You got it the other way round. The `Find` method wants ability to move backwards. – GSerg Jan 02 '13 at 12:57

Rowset does not support scrolling backward

2 Answers2

Linked