sql-server - VBA SQL Server查询连接但不返回记录

Question

我有这个 VBA 代码，它在连接成功后发出 SQL Server 查询，但查询不返回任何记录（例如，-1）

Function invested_funds() As Integer
Dim c As ADODB.Connection
Dim rs As ADODB.Recordset
Dim connectionstring As String
Dim sql As String

connectionstring = "Provider=SQLOLEDB;Data Source=DESKTOP-2TTG3GQ\SQLEXPRESS;" & _
                   "Initial Catalog=DB;" & _
                   "Integrated Security=SSPI;"

Set c = New ADODB.Connection
Set rs = New ADODB.Recordset
c.Open connectionstring

sql = "select [DB].[dbo].[db].[CSRoot] " & _  
      "from [DB].[dbo].[db] " 

If c.State = adStateOpen Then
    Debug.Print ("Connected") 'This prints!
End If

Set rs = c.Execute(sql) 
Debug.Print (rs.RecordCount)
invested_funds = CInt(rs.RecordCount)

End Function

但是，我知道记录存在，并且 SSMS 中完全相同的查询确实返回记录

select  [DB].[dbo].[db].[CSRoot]
from [DB].[dbo].[db]

事实上，许多记录。

怎么会这样？

标签： sql-serverexcelvba

Answer 1

您可以使用 rs.getRows 函数获取记录数。

Function invested_funds() As Integer
    Dim c As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connectionstring As String
    Dim sql As String
    
    connectionstring = "Provider=SQLOLEDB;Data Source=DESKTOP-2TTG3GQ\SQLEXPRESS;" & _
                       "Initial Catalog=DB;" & _
                       "Integrated Security=SSPI;"
    
    Set c = New ADODB.Connection
    Set rs = New ADODB.Recordset
    c.Open connectionstring
    
    sql = "select [DB].[dbo].[db].[CSRoot] " & _
          "from [DB].[dbo].[db] "
    
    If c.State = adStateOpen Then
        Debug.Print ("Connected") 'This prints!
    End If
    
    Set rs = c.Execute(sql)
    
    Dim arr As Variant, n As Integer
    
    arr = rs.GetRows
    n = UBound(arr, 2) + 1
    'Debug.Print (rs.RecordCount)
    Debug.Print n
    invested_funds = n

End Function