如何从函数返回记录集 [英] How to return a recordset from a function

问题描述

我正在Excel VBA中构建数据访问层，并且无法返回记录集。我的类中的Execute（）函数绝对是从数据库中检索一行，但似乎没有返回任何东西。

以下函数包含在类中称为DataAccessLayer。该类包含用于打开和关闭连接的函数Connect and Disconnect。

  
公共函数执行（ByVal sqlQuery As String）As ADODB.recordset 
 Dim rs As ADODB.recordset 
 Set rs = New ADODB.recordset 
 Dim recordsAffected As Long 
 
'确保我们已连接到数据库。 
如果连接然后
设置命令=新ADODB.command 
 
使用命令
 .ActiveConnection =连接
 .CommandText = sqlQuery 
 .CommandType = adCmdText 
结束
 
'设置rs = command.Execute（recordsAffected）
'设置Execute = command.Execute（recordsAffected）
 rs.Open command.Execute （recordsAffected）
 rs.ActiveConnection = Nothing 
设置Execute = rs 
设置command = Nothing 
 Call Disconnect 
 End If 
 End Function 
  

这是一个我在电子表格的单元格A1中用于测试的公共函数。

  
公共函数Scott_Test（）
 Dim数据库作为新的DataAccessLayer 
'Dim rs As ADODB.recordset 
 'set rs = CreateObject（ADODB.Recordset）
设置rs =新建ADODB.recordset 
 
设置rs = Database.Exec ute（SELECT item_desc_1 FROM imitmidx_sql WHERE item_no ='11001'）
'rs.Open Database.Execute（SELECT item_desc_1 FROM imitmidx_sql WHERE item_no ='11001'）
'rs.Open 
 
'从不显示。 
 MsgBox rs.EOF 
 
如果不是rs.EOF然后
'这显示#VALUE！在单元格A1中。 
 Scott_Test = rs！item_desc_1 
 rs.Close 
 End If 
 
 rs.ActiveConnection = Nothing 
设置rs = Nothing 
结束函数
  

我做错了什么？

解决方案

问题在于设置ActiveConnection = Nothing。以下代码工作：

 公共函数执行（ByVal sqlQuery As String）作为ADODB.recordset 
 Dim rs As ADODB .recordset 
设置rs =新建ADODB.recordset 
 Dim recordsAffected As Long 
 
'确保我们连接到数据库。 
如果连接然后
设置命令=新ADODB.command 
 
使用命令
 .ActiveConnection =连接
 .CommandText = sqlQuery 
 .CommandType = adCmdText 
结束
 
 rs.Open command.Execute（recordsAffected）
 
设置Execute = rs 
设置command = Nothing 
调用断开
结束If 
结束功能 

I'm building a data access layer in Excel VBA and having trouble returning a recordset. The Execute() function in my class is definitely retrieving a row from the database, but doesn't seem to be returning anything.

The following function is contained in a class called DataAccessLayer. The class contains functions Connect and Disconnect which handle opening and closing the connection.

Public Function Execute(ByVal sqlQuery As String) As ADODB.recordset
    Dim rs As ADODB.recordset
    Set rs = New ADODB.recordset
    Dim recordsAffected As Long

    ' Make sure we're connected to the database.
    If Connect Then
        Set command = New ADODB.command

        With command
            .ActiveConnection = connection
            .CommandText = sqlQuery
            .CommandType = adCmdText
        End With

        'Set rs = command.Execute(recordsAffected)
        'Set Execute = command.Execute(recordsAffected)
        rs.Open command.Execute(recordsAffected)
        rs.ActiveConnection = Nothing
        Set Execute = rs
        Set command = Nothing
        Call Disconnect
    End If
End Function

Here's a public function that I'm using in cell A1 of my spreadsheet for testing.

Public Function Scott_Test()
    Dim Database As New DataAccessLayer
    'Dim rs As ADODB.recordset
    'Set rs = CreateObject("ADODB.Recordset")
    Set rs = New ADODB.recordset

    Set rs = Database.Execute("SELECT item_desc_1 FROM imitmidx_sql WHERE item_no = '11001'")
    'rs.Open Database.Execute("SELECT item_desc_1 FROM imitmidx_sql WHERE item_no = '11001'")
    'rs.Open

    ' This never displays.
    MsgBox rs.EOF

    If Not rs.EOF Then
        ' This is displaying #VALUE! in cell A1.
        Scott_Test = rs!item_desc_1
        rs.Close
    End If

    rs.ActiveConnection = Nothing
    Set rs = Nothing
End Function

What am I doing wrong?

解决方案

The problem was with setting the ActiveConnection = Nothing. The following code works:

Public Function Execute(ByVal sqlQuery As String) As ADODB.recordset
    Dim rs As ADODB.recordset
    Set rs = New ADODB.recordset
    Dim recordsAffected As Long

    ' Make sure we are connected to the database.
    If Connect Then
        Set command = New ADODB.command

        With command
            .ActiveConnection = connection
            .CommandText = sqlQuery
            .CommandType = adCmdText
        End With

        rs.Open command.Execute(recordsAffected)

        Set Execute = rs
        Set command = Nothing
        Call Disconnect
    End If
End Function

这篇关于如何从函数返回记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！