时间戳

首页 > 解决方案 > VBS 错误：BOF 或 EOF 为真或当前记录已被删除 错误 800AOBCD

vbscript - VBS 错误：BOF 或 EOF 为真或当前记录已被删除 错误 800AOBCD

问题描述

由于没有失败记录而失败的代码，具有讽刺意味

我有下面的代码显示 800AOBCD 错误。我做了一段时间的故障排除，发现我做错了什么，但 Notepad++ 中的这种语法现在太多了。

我尝试添加 WHILE NOT rs.EOF 但出错了。我想我已经看这个太久了

还尝试在 EOF 之前执行 LOOP，但也说明了这一点。

'******************************
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

标签： vbscripteofrs

解决方案

这不是一个糟糕的尝试，但这是一个返工*，它应该可以帮助您更好地理解流程。有几件事可以从中得到；

  • 如果将连接字符串传递到属性中，则无需显式处理，Set cn = CreateObject("ADODB.Connection")因为它ADODB.Command可以处理 a 的创建、打开和关闭。ADODB.ConnectionActiveConnection
  • 调用方法时无需显式Set rs = CreateObject("ADODB.RecordSet")返回ADODB.Commanda 。ADODB.RecordsetExecute()
  • 您可以使用If语句来检查是否存在当前记录，方法是同时检查BOF和EOF属性设置为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

*提供未经测试的代码。

有用的链接

推荐阅读