vba 编辑 ADODB 记录集中的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25566599/
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
Edit records in ADODB recordset
提问by Ernesto Monroy
What I am trying to do is to get some data from an online server through an SQL Query and then loop through the recordset modifying the records.
我想要做的是通过 SQL 查询从在线服务器获取一些数据，然后循环修改记录的记录集。
I get an error when trying to modify the recordset:
尝试修改记录集时出现错误：
"Multiple-Step operation generated errors. Check each status value."
“多步操作产生错误。检查每个状态值。”
My question is: Is there a way of modifying a record from a recordset that I got from a Query?
我的问题是：有没有办法修改我从查询获得的记录集中的记录？
In this case I am modifying field 2 if field 1 meets a certain criteria. (In this case Field 2 is a string)
在这种情况下，如果字段 1 满足特定条件，我将修改字段 2。（在这种情况下，字段 2 是一个字符串）
Here is the simplified code:
这是简化的代码：
Dim adoConn As ADODB.Connection
Dim locRS As New ADODB.Recordset, proRS As ADODB.Recordset
Dim strConnection As String
Set getSQL = New ADODB.Recordset
'Set Objects
Set adoConn = New ADODB.Connection
'Specify connection string
strConnection = "User ID=xxx; Password=xxx;Data Source=xxx;Provider=OraOLEDB.Oracle"
'Open the connection
adoConn.Open (strConnection)
'Set up recordset properties
getSQL.CursorType = adOpenStatic
getSQL.CursorLocation = adUseClient
getSQL.LockType = adLockBatchOptimistic
'Import the data
getSQL.Open "SELECT FIELD1, FIELD2 FROM TABLE", adoConn, adOpenStatic, adLockOptimistic
Set getSQL.ActiveConnection = Nothing
getSql.Update
'Loop through data
getSQL.MoveFirst
Do While Not stockRS.EOF
'If cetrain condition is met then modify the null column
if getSQL!FIELD1=CRITERIA then
'Error here
getSQL!FIELD2="SOME STRING"
End If
getSQL.MoveNext
Loop
'Close
adoConn.Close
Set adoConn = Nothing
采纳答案by nempnett
Your SQL is not doing what you think: SELECT ... NULL OUTCOME ... is going to return the value NULL in a field called OUTCOME but will not link to a field in the table called OUTCOME (which is what I think you are looking for) as your current syntax is setting up an ALIAS not selecting the field. I am assuming the field OUTCOME exists on the table. If not you need to create it up front or do an alter table to add the field before you can write anything to it. I recommend creating field up front (which I think you have already done). But make sure that the default value is NULL so you don't need to do your NULL trick in the select ALSO make sure that the field is allowed to take a NULL value or you will see errors. Select becomes:
您的 SQL 没有按照您的想法执行： SELECT ... NULL OUTCOME ... 将在名为 OUTCOME 的字段中返回 NULL 值，但不会链接到名为 OUTCOME 的表中的字段（这就是我认为您是寻找），因为您当前的语法正在设置一个 ALIAS，而不是选择该字段。我假设表中存在 OUTCOME 字段。如果不是，您需要预先创建它或执行更改表以添加字段，然后才能向其写入任何内容。我建议预先创建字段（我认为您已经完成了）。但请确保默认值为 NULL，因此您无需在选择中执行 NULL 技巧，同时确保允许该字段采用 NULL 值，否则您将看到错误。选择变成：
getSQL.Open "SELECT FIELD1, FIELD2, OUTCOME FROM TABLE", adoConn, adOpenStatic, adLockOptimistic
And then manage the NULL value in the function as follows:
然后在函数中管理NULL值如下：
if getSQL!FIELD1=CRITERIA then
'Error here
getSQL!OUTCOME="SOME STRING"
ELSE
getSQL!OUTCOME=NULL
End If
This ensure that you always write something to OUTCOME field so processing and OUTCOME don't get out of sync.
这确保您始终向 OUTCOME 字段写入内容，以便处理和 OUTCOME 不会不同步。
Also I still think that you have divorced the recordset data from the server when you:
此外，我仍然认为您在以下情况下已将记录集数据与服务器分离：
Set getSQL.ActiveConnection = Nothing
Do this to release resources after your are done. You may also need a
执行此操作以在完成后释放资源。您可能还需要一个
getSql.Update
After making changes to commit them back to database.
进行更改后将它们提交回数据库。
