Either BOF or EOF is True...

[doofusboy]

"Either BOF or EOF is True, or the current record has been deleted. Requested operation reuires a current record." Error 3021

This error is received when macro is run. However, when I run this exact same SQL string in SQL*Plus against the Oracle database I receive records with no problem. If I remove Date >= and <=, it runs with no problem.

Anyone see a problem with my code?

Public Sub GetExpIDs()
Dim intCTR As Integer
Dim oldStatusBar As Variant
    On Error GoTo GetExpInfo_Err
    Application.EnableCancelKey = xlErrorHandler
 
    ' update status bar
    oldStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.StatusBar = "Connecting to eE database..."
    ' use ADO to access eE database
    Set CN = New ADODB.Connection
    CN.Open ConnectionString:= _
            "Provider=OraOLEDB.Oracle.1; " & _
            "User ID=" & wsIndivExperiment.Decrypt(ThisWorkbook.gKey1) & "; " & _
            "Password=" & wsIndivExperiment.Decrypt(ThisWorkbook.gKey2) & "; " & _
            "Persist Security Info=True; " & _
            "Data Source=" & ThisWorkbook.gKey3
 
    Application.StatusBar = "Searching records in eE database..."
 
    Set RS = New ADODB.Recordset
    RS.Open GetExperimentsSQL(ThisWorkbook.Names("StartDate").RefersToRange.Value, ThisWorkbook.Names("EndDate").RefersToRange.Value), CN, adOpenDynamic, adLockReadOnly
    If RS.BOF And RS.EOF Then     [COLOR=red][B]<-----error occurs here[/B][/COLOR]
        MsgBox "Unable to return record(s).", vbOKOnly, "No records found : " & ActiveWorkbook.Name
    Else
        ' put recordset data into worksheet
        RS.MoveFirst
        While Not RS.EOF
            intCTR = intCTR + 1
            DoEvents
            If IsNull(RS.fields(ExperimentID).Value) Then
                Range("C" & intCTR + cnHEADER_ROWS).Value = ""
            Else
                wsExperiments.Unprotect
                Range("C" & intCTR + cnHEADER_ROWS).Locked = False
                Range("C" & intCTR + cnHEADER_ROWS).Value = RS.fields(ExperimentID).Value
                Range("C" & intCTR + cnHEADER_ROWS).Locked = True
                wsExperiments.Protect
            End If
            If IsNull(RS.fields(ExperimentName).Value) Then
                Range("D" & intCTR + cnHEADER_ROWS).Value = ""
            Else
                wsExperiments.Unprotect
                Range("D" & intCTR + cnHEADER_ROWS).Locked = False
                Range("D" & intCTR + cnHEADER_ROWS).Value = RS.fields(ExperimentName).Value
                Range("D" & intCTR + cnHEADER_ROWS).Locked = True
                wsExperiments.Protect
            End If
            If IsNull(RS.fields(ExperimentDate).Value) Then
                Range("E" & intCTR + cnHEADER_ROWS).Value = ""
            Else
                wsExperiments.Unprotect
                Range("E" & intCTR + cnHEADER_ROWS).Locked = False
                Range("E" & intCTR + cnHEADER_ROWS).Value = RS.fields(ExperimentDate).Value
                Range("E" & intCTR + cnHEADER_ROWS).Locked = True
                wsExperiments.Protect
            End If
            If IsNull(RS.fields(ExperimentLab).Value) Then
                Range("F" & intCTR + cnHEADER_ROWS).Value = ""
            Else
                wsExperiments.Unprotect
                Range("F" & intCTR + cnHEADER_ROWS).Locked = False
                Range("F" & intCTR + cnHEADER_ROWS).Value = RS.fields(ExperimentLab).Value
                Range("F" & intCTR + cnHEADER_ROWS).Locked = True
                wsExperiments.Protect
            End If
            RS.MoveNext
        Wend
        ' close and destroy recordset object
        RS.Close
        Set RS = Nothing
    End If
 
    ' close and destroy connection object
    CN.Close
    Set CN = Nothing
 
    ' unhide rows containing data
    For intCTR = cnHEADER_ROWS + 1 To cnMAX_ROWS
        If Len(Range("C" & intCTR)) <> 0 Then
            wsExperiments.Unprotect
            Range("C" & intCTR).EntireRow.Hidden = False
            wsExperiments.Protect
        End If
    Next intCTR
 
    ' reset status bar
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
GetExpInfo_Err:
    If Err.Number <> 0 Then
        MsgBox "An error occurred retrieving data." & vbCrLf & vbCrLf _
        & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, vbCritical, _
        ActiveWorkbook.ActiveSheet.Name & Space(3) & "GetExpIDs"
        modGeneral.WorkbookReset
    End If
 
End Sub
Public Function GetExperimentsSQL(sDate1 As String, sDate2 As String) As String
Dim sSQL As String
    On Error GoTo ErrHandling
    Application.EnableCancelKey = xlErrorHandler
    sSQL = "SELECT A.EXPERIMENTID, A.CREATEDDATE, B.NAME, C.NAME"
    sSQL = sSQL & " FROM EE.TEMPLATEBYEXPERIMENT A, EE.EXPERIMENT B, EE.LAB C"
    sSQL = sSQL & " WHERE A.TEMPLATEID = 24609"
[COLOR=red][B]    sSQL = sSQL & " AND A.CREATEDDATE >= '" & sDate1 & "'"[/B][/COLOR]
[COLOR=red][B]    sSQL = sSQL & " AND A.CREATEDDATE <= '" & sDate2 & "'"[/B][/COLOR]
    sSQL = sSQL & " AND A.EXPERIMENTID = B.EXPERIMENTID"
    sSQL = sSQL & " AND B.LABID = C.LABID"
    sSQL = sSQL & " ORDER BY A.EXPERIMENTID"
 
    Debug.Print sSQL
 
    GetExperimentsSQL = sSQL
 
    Application.EnableCancelKey = xlInterrupt
ErrHandling:
    If Err.Number <> 0 Then
        MsgBox "Error occurred during GetExperimentsSQL function." & vbCrLf & vbCrLf & _
               "Error #" & Err.Number & vbTab & Err.Description, vbInformation, _
               ActiveWorkbook.ActiveSheet.Name & Space(3) & "GetExperimentsSQL Function"
        modGeneral.WorkbookReset
    End If
End Function

 

[DCrake]

It looks like you are treating dates as strings you need to use #'s around dates not single quotes.

 

[doofusboy]

Thanks for your response, but I tried that too. When querying an Oracle database it calls '#' and 'invalid character' and an error results.