sqlvbscriptrecordsetrowset

Rowset does not support scrolling backward

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?

Solution

  • 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.