我正在 Excel VBA 中构建数据访问层，但在返回记录集时遇到问题。我的类中的 Execute() 函数肯定是从数据库中检索一行，但似乎没有返回任何内容。
以下函数包含在名为 DataAccessLayer 的类中。该类包含函数 Connect 和 Disconnect，它们处理打开和关闭连接。
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
这是我在电子表格的单元格 A1 中使用的公共函数以进行测试。
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
我究竟做错了什么？
问题在于设置 ActiveConnection = Nothing。以下代码有效：
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
