VBS Error: Either BOF or EOF is True or Current Record has been deleted Error 800AOBCD

Question

Code that is failing due to no failed records, ironic

I have the below code that is displaying the 800AOBCD error. I did some troubleshooting for a while to find what I did wrong but this syntax in Notepad++ is too much right now.

I tried adding a WHILE NOT rs.EOF but errored. I think I've been looking at this too long

Also tried to do a LOOP until EOF but that's stated as well.

'******************************
Function CheckFailures()
'******************************

        cmdTxt = "SELECT CAST([DateImported] AS DATE) AS [DateImported],[IsCopied],[IsDeleted],[IsFailed],[FailedReason],[Years],[Scenario],[Version],[Entity],[Account],[AverageBalance],[P01],[P02],[P03],[P04],[P05],[P06],[P07],[P08],[P09],[P10],[P11],[P12] "
        cmdTxt = cmdTxt & "FROM essstage.HFM_FACT_PLAN_BSO WHERE ( ISFAILED<>0) AND FAILEDREASON NOT IN('Zero Balance','AverageBalance Not Needed') "

        Dim strConnSQL
        strConnSQL = "Provider=SQLOLEDB; Server=CHI-SQL16A.dev.LOCAL; Database=GLDMUS_MDEV; Trusted_Connection=Yes"

    'Open the connection to the database
        Dim cn
        Set cn = CreateObject("ADODB.Connection")
        cn.Open strConnSQL

    'Set the command
        Dim cmd
        Set cmd = CreateObject("ADODB.Command")
        Set cmd.ActiveConnection = cn

    'Set the record Set
        Dim rs
        Set rs = CreateObject("ADODB.recordSet")

    'Prepare the query
        cmd.CommandText = cmdTxt
        cmd.CommandType = 1  'adCmdText

    'Execute the query
        Set rs = cmd.Execute
        Set cmd = Nothing

     ' First spot I'm trying to check - If the rs returns no records, 
     ' then just say no records returned and don't go interpreting 
     ' the results into a format for email 


    'Header Row
            CheckFailures = "<br><br><br><b> Batch Failures:</b><br><br>"
            CheckFailures = CheckFailures & "DateImported"  & FillSpaces (12, "DateImported") 
            CheckFailures = CheckFailures & "IsCopied"      & FillSpaces (14, "IsCopied")
            CheckFailures = CheckFailures & "IsDeleted"     & FillSpaces (12, "IsDeleted")
            CheckFailures = CheckFailures & "IsFailed"  & FillSpaces (12, "IsFailed")
            CheckFailures = CheckFailures & "FailedReason"  & FillSpaces (50, "FailedReason")
            CheckFailures = CheckFailures & "Years"     & FillSpaces (12, "Years")
            CheckFailures = CheckFailures & "Scenario"  & FillSpaces (12, "Scenario")
            CheckFailures = CheckFailures & "Version"   & FillSpaces (12, "Version")
            CheckFailures = CheckFailures & "Entity"    & FillSpaces (12, "Entity")
            CheckFailures = CheckFailures & "Account"   & FillSpaces (12, "Account")
            CheckFailures = CheckFailures & "AverageBalance"    & FillSpaces (16, "AverageBalance")
            CheckFailures = CheckFailures & "P01"   & FillSpaces (10, "P01") 
            CheckFailures = CheckFailures & "P02"   & FillSpaces (10, "P02") 
            CheckFailures = CheckFailures & "P03"   & FillSpaces (10, "P03") 
            CheckFailures = CheckFailures & "P04"   & FillSpaces (10, "P04") 
            CheckFailures = CheckFailures & "P05"   & FillSpaces (10, "P05") 
            CheckFailures = CheckFailures & "P06"   & FillSpaces (10, "P06") 
            CheckFailures = CheckFailures & "P07"   & FillSpaces (10, "P07") 
            CheckFailures = CheckFailures & "P08"   & FillSpaces (10, "P08") 
            CheckFailures = CheckFailures & "P09"   & FillSpaces (10, "P09") 
            CheckFailures = CheckFailures & "P10"   & FillSpaces (10, "P10") 
            CheckFailures = CheckFailures & "P11"   & FillSpaces (10, "P11") 
            CheckFailures = CheckFailures & "P12"   & "<br>"


    'Read Query Results
       'is this where I put the WHILE NOT rs.EOF to prevent rs.MoveNext? 
        rs.MoveFirst
        Do Until rs.EOF
            CheckFailures = CheckFailures & rs.Fields("DateImported").Value     & FillSpaces (12, rs.Fields("DateImported").Value) 
            CheckFailures = CheckFailures & rs.Fields("IsCopied").Value     & FillSpaces (14, rs.Fields("IsCopied").Value) 
            CheckFailures = CheckFailures & rs.Fields("IsDeleted").Value    & FillSpaces (12, rs.Fields("IsDeleted").Value) 
            CheckFailures = CheckFailures & rs.Fields("IsFailed").Value     & FillSpaces (20, rs.Fields("IsFailed").Value) 
            CheckFailures = CheckFailures & rs.Fields("FailedReason").Value     & FillSpaces (50, rs.Fields("FailedReason").Value) 
            CheckFailures = CheckFailures & rs.Fields("Years").Value    & FillSpaces (12, rs.Fields("Years").Value) 
            CheckFailures = CheckFailures & rs.Fields("Scenario").Value     & FillSpaces (12, rs.Fields("Scenario").Value) 
            CheckFailures = CheckFailures & rs.Fields("Version").Value  & FillSpaces (12, rs.Fields("Version").Value) 
            CheckFailures = CheckFailures & rs.Fields("Entity").Value   & FillSpaces (12, rs.Fields("Entity").Value) 
            CheckFailures = CheckFailures & rs.Fields("Account").Value  & FillSpaces (12, rs.Fields("Account").Value) 
            CheckFailures = CheckFailures & rs.Fields("AverageBalance").Value   & FillSpaces (16, rs.Fields("AverageBalance").Value) 
            CheckFailures = CheckFailures & rs.Fields("P01").Value  & FillSpaces (10, rs.Fields("P01").Value) 
            CheckFailures = CheckFailures & rs.Fields("P02").Value  & FillSpaces (10, rs.Fields("P02").Value) 
            CheckFailures = CheckFailures & rs.Fields("P03").Value  & FillSpaces (10, rs.Fields("P03").Value) 
            CheckFailures = CheckFailures & rs.Fields("P04").Value  & FillSpaces (10, rs.Fields("P04").Value) 
            CheckFailures = CheckFailures & rs.Fields("P05").Value  & FillSpaces (10, rs.Fields("P05").Value) 
            CheckFailures = CheckFailures & rs.Fields("P06").Value  & FillSpaces (10, rs.Fields("P06").Value) 
            CheckFailures = CheckFailures & rs.Fields("P07").Value  & FillSpaces (10, rs.Fields("P07").Value) 
            CheckFailures = CheckFailures & rs.Fields("P08").Value  & FillSpaces (10, rs.Fields("P08").Value) 
            CheckFailures = CheckFailures & rs.Fields("P09").Value  & FillSpaces (10, rs.Fields("P09").Value) 
            CheckFailures = CheckFailures & rs.Fields("P10").Value  & FillSpaces (10, rs.Fields("P10").Value) 
            CheckFailures = CheckFailures & rs.Fields("P11").Value  & FillSpaces (10, rs.Fields("P11").Value) 
            CheckFailures = CheckFailures & rs.Fields("P12").Value  & "<br>"
            'is this where I put the WHILE NOT rs.EOF to prevent rs.MoveNext? 
            rs.MoveNext

        Loop

        If Len(CheckFailures) = 0 Then 
            CheckFailures= "no records returned"
        End If

    'Close connections
        cn.Close
        Set cn = Nothing        

End Function

Answer 1

This isn't a bad attempt but here's a re-work^* which should help you understand the flow better. Couple of things to take from this;

• No need to explicitly Set cn = CreateObject("ADODB.Connection") as the ADODB.Command can handle the creation, opening and closing of a ADODB.Connection if a connection string is passed into the ActiveConnection property.
• No need to explicitly Set rs = CreateObject("ADODB.RecordSet") as the ADODB.Command returns a ADODB.Recordset when calling the Execute() method.
• You can use an If statement to check that there is a current record by checking both and the BOF and EOF properties are set to False.

Const adCmdText = 1

'******************************
Function CheckFailures()
'******************************
  Dim result, conn, cmd, sql, rs

  conn = "Provider=SQLOLEDB; Server=CHI-SQL16A.dev.LOCAL; Database=GLDMUS_MDEV; Trusted_Connection=Yes"

  sql = ""
  sql = sql & "SELECT CAST([DateImported] AS DATE) AS [DateImported],[IsCopied],[IsDeleted],[IsFailed],[FailedReason]"
  sql = sql & ",[Years],[Scenario],[Version],[Entity],[Account],[AverageBalance]"
  sql = sql & ",[P01],[P02],[P03],[P04],[P05],[P06],[P07],[P08],[P09],[P10],[P11],[P12] "
  sql = sql & "FROM essstage.HFM_FACT_PLAN_BSO "
  sql = sql & "WHERE (ISFAILED<>0) AND FAILEDREASON NOT IN('Zero Balance','AverageBalance Not Needed')"

  Set cmd = CreateObject("ADODB.Command")
  With cmd
    .ActiveConnection = conn
    .CommandText = cmdTxt
    .CommandType = adCmdText
    Set rs = .Execute()
  End With
  Set cmd = Nothing

  ' First spot I'm trying to check - If the rs returns no records, 
  ' then just say no records returned and don't go interpreting 
  ' the results into a format for email 


  ' Header Row
  result = "<br><br><br><b> Batch Failures:</b><br><br>"
  result = result & "DateImported"  & FillSpaces (12, "DateImported") 
  result = result & "IsCopied"      & FillSpaces (14, "IsCopied")
  result = result & "IsDeleted"     & FillSpaces (12, "IsDeleted")
  result = result & "IsFailed"  & FillSpaces (12, "IsFailed")
  result = result & "FailedReason"  & FillSpaces (50, "FailedReason")
  result = result & "Years"     & FillSpaces (12, "Years")
  result = result & "Scenario"  & FillSpaces (12, "Scenario")
  result = result & "Version"   & FillSpaces (12, "Version")
  result = result & "Entity"    & FillSpaces (12, "Entity")
  result = result & "Account"   & FillSpaces (12, "Account")
  result = result & "AverageBalance"    & FillSpaces (16, "AverageBalance")
  result = result & "P01"   & FillSpaces (10, "P01") 
  result = result & "P02"   & FillSpaces (10, "P02") 
  result = result & "P03"   & FillSpaces (10, "P03") 
  result = result & "P04"   & FillSpaces (10, "P04") 
  result = result & "P05"   & FillSpaces (10, "P05") 
  result = result & "P06"   & FillSpaces (10, "P06") 
  result = result & "P07"   & FillSpaces (10, "P07") 
  result = result & "P08"   & FillSpaces (10, "P08") 
  result = result & "P09"   & FillSpaces (10, "P09") 
  result = result & "P10"   & FillSpaces (10, "P10") 
  result = result & "P11"   & FillSpaces (10, "P11") 
  result = result & "P12"   & "<br>"


  'Read Query Results
  If Not (rs.BOF And rs.EOF) Then
    While Not rs.EOF
      result = result & rs.Fields("DateImported").Value     & FillSpaces (12, rs.Fields("DateImported").Value) 
      result = result & rs.Fields("IsCopied").Value     & FillSpaces (14, rs.Fields("IsCopied").Value) 
      result = result & rs.Fields("IsDeleted").Value    & FillSpaces (12, rs.Fields("IsDeleted").Value) 
      result = result & rs.Fields("IsFailed").Value     & FillSpaces (20, rs.Fields("IsFailed").Value) 
      result = result & rs.Fields("FailedReason").Value     & FillSpaces (50, rs.Fields("FailedReason").Value) 
      result = result & rs.Fields("Years").Value    & FillSpaces (12, rs.Fields("Years").Value) 
      result = result & rs.Fields("Scenario").Value     & FillSpaces (12, rs.Fields("Scenario").Value) 
      result = result & rs.Fields("Version").Value  & FillSpaces (12, rs.Fields("Version").Value) 
      result = result & rs.Fields("Entity").Value   & FillSpaces (12, rs.Fields("Entity").Value) 
      result = result & rs.Fields("Account").Value  & FillSpaces (12, rs.Fields("Account").Value) 
      result = result & rs.Fields("AverageBalance").Value   & FillSpaces (16, rs.Fields("AverageBalance").Value) 
      result = result & rs.Fields("P01").Value  & FillSpaces (10, rs.Fields("P01").Value) 
      result = result & rs.Fields("P02").Value  & FillSpaces (10, rs.Fields("P02").Value) 
      result = result & rs.Fields("P03").Value  & FillSpaces (10, rs.Fields("P03").Value) 
      result = result & rs.Fields("P04").Value  & FillSpaces (10, rs.Fields("P04").Value) 
      result = result & rs.Fields("P05").Value  & FillSpaces (10, rs.Fields("P05").Value) 
      result = result & rs.Fields("P06").Value  & FillSpaces (10, rs.Fields("P06").Value) 
      result = result & rs.Fields("P07").Value  & FillSpaces (10, rs.Fields("P07").Value) 
      result = result & rs.Fields("P08").Value  & FillSpaces (10, rs.Fields("P08").Value) 
      result = result & rs.Fields("P09").Value  & FillSpaces (10, rs.Fields("P09").Value) 
      result = result & rs.Fields("P10").Value  & FillSpaces (10, rs.Fields("P10").Value) 
      result = result & rs.Fields("P11").Value  & FillSpaces (10, rs.Fields("P11").Value) 
      result = result & rs.Fields("P12").Value  & "<br>"
      rs.MoveNext
    Loop
  Else
    result = "no records returned"
  End If
  Call rs.Close()
  CheckFailures = result
End Function

^{*Code provided untested.}

---

Useful Links

• VBScript - Return a Recordset in an Array (Shows how to take this further and use GetRows() method to output the recordset into an array, which negates the overhead of working with the ADODB.Recordset object)