vba 从 sql server 记录集中获取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26407632/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba get value from sql server recordset
提问by user3344443
My code to retrieve value is as below:
我检索值的代码如下：
Sub UploadData()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim strConn As String
Dim sql As String
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=.\sql2000;INITIAL CATALOG=EquityDB;INTEGRATED SECURITY=sspi;"
cn.Open strConn
sql = "select * from EquityDB.dbo.table1 where field1 = '" & Replace(Range("d1").Value, "'", "''") & "'"
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
GetData = rs.Fields(0).Value
If Not GetData = "" Then
cn.Execute sql001
Else
cn.Execute sql002
End If
sql001 is an insert, and sql002 is an update
sql001 是插入，而 sql002 是更新
When I run the macro, I got error saying operation is not allowed when the record is open for the line
当我运行宏时，我收到错误提示，当该行的记录打开时不允许操作
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
If I change
如果我改变
If Not GetData = "" Then
to
到
If Not GetData Is Null Then
I get error saying "object required" with the line
我收到一条错误消息，说“需要对象”
If Not GetData Is Null Then
Any advice on how to fix the bug would be great!
关于如何修复错误的任何建议都会很棒！
回答by JNevill
To test if anything was returned into your recordset, instead of:
要测试是否有任何内容返回到您的记录集中，而不是：
GetData = rs.Fields(0).Value
If Not GetData = "" Then
Use:
用：
If not(rs.eof and rs.bof) then
This will return true if the recordset is not empty.
如果记录集不为空，这将返回 true。
