VB.NET循环处理49条记录后报ODBC错误，求排查方案

我需要遍历Excel表格获取发票号，再通过该发票号在DataTable中查找对应最大行号，但获取行号环节出现异常：

• 代码仅能正常处理49条记录，超过则触发ODBC错误
• 第49条发票号为70330，检查无异常；移除该记录后，仍会在下一条发票号处报错
• 将代码中的SQL语句复制到MS Access中执行可正常运行

相关代码 #

'Find Line Number
Dim Query2 As String = "SELECT PUB_invoice2.[invoice-number], Max(PUB_invoice2.[line-number]) AS [MaxOfline-number] FROM PUB_invoice2 GROUP BY PUB_invoice2.[invoice-number] HAVING (((PUB_invoice2.[invoice-number])=" & invoiceNumber & "));"
Dim cmd2 As New OleDbCommand(Query2, cnn)
Dim TheDataReader2 As OleDbDataReader = cmd2.ExecuteReader()

While TheDataReader2.Read()
    invoiceLine = TheDataReader2("MaxOfline-number")
End While

TheDataReader2.Close()

排查思路 #

• 替换字符串拼接为参数化查询：当前直接拼接invoiceNumber可能存在类型不匹配（如发票号是字符串却未加引号）、特殊字符干扰，或多次拼接触发ODBC查询长度限制。改用参数化写法：

  Dim Query2 As String = "SELECT PUB_invoice2.[invoice-number], Max(PUB_invoice2.[line-number]) AS [MaxOfline-number] FROM PUB_invoice2 GROUP BY PUB_invoice2.[invoice-number] HAVING PUB_invoice2.[invoice-number] = ?;"
  Dim cmd2 As New OleDbCommand(Query2, cnn)
  cmd2.Parameters.AddWithValue("@invoiceNumber", invoiceNumber)
  

• 强制资源自动释放：循环处理中可能存在Command、DataReader资源泄漏，用Using语句自动管理资源生命周期：

  Using cmd2 As New OleDbCommand(Query2, cnn)
      cmd2.Parameters.AddWithValue("@invoiceNumber", invoiceNumber)
      Using TheDataReader2 As OleDbDataReader = cmd2.ExecuteReader()
          While TheDataReader2.Read()
              invoiceLine = TheDataReader2("MaxOfline-number")
          End While
      End Using
  End Using
  

• 优化数据库交互次数：避免每条发票号单独查一次，先批量获取所有发票的最大行号存入字典，再遍历Excel匹配，减少ODBC请求次数：

  '预查询所有发票的最大行号
  Dim invoiceMaxLines As New Dictionary(Of Object, Object)()
  Dim batchQuery As String = "SELECT [invoice-number], Max([line-number]) AS MaxLine FROM PUB_invoice2 GROUP BY [invoice-number];"
  Using cmd As New OleDbCommand(batchQuery, cnn)
      Using reader As OleDbDataReader = cmd.ExecuteReader()
          While reader.Read()
              invoiceMaxLines.Add(reader("invoice-number"), reader("MaxLine"))
          End While
      End Using
  End Using
  '遍历Excel时直接从字典取值
  invoiceLine = invoiceMaxLines(invoiceNumber)
  

• 检查ODBC驱动与连接池：确认使用的ODBC驱动为最新版本，连接字符串中是否存在批量操作限制参数；同时排查循环中是否重复创建连接导致连接池耗尽，确保连接在循环外创建并复用。

内容的提问来源于stack exchange，提问作者Robert Bee