编辑ADODB记录集中的记录 [英] Edit records in ADODB recordset

问题描述

我想做的是通过SQL查询从联机服务器中获取一些数据，然后遍历记录集以修改记录。

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.

我得到一个尝试修改记录集时发生错误：

I get an error when trying to modify the recordset:

多步操作生成了错误。请检查每个状态值。

"Multiple-Step operation generated errors. Check each status value."

我的问题是：是否可以修改从查询中获取的记录集中的记录？

在这种情况下，我如果字段1符合特定条件，则修改字段2。 （在这种情况下，字段2是一个字符串）

In this case I am modifying field 2 if field 1 meets a certain criteria. (In this case Field 2 is a string)

这是简化代码：

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

推荐答案

您的SQL并没有按照您的想法进行操作：
SELECT ... NULL OUTCOME ...将在其中返回值NULL一个名为OUTCOME的字段，但不会链接到名为OUTCOME的表中的字段（我认为您正在寻找的字段），因为您当前的语法是设置ALIAS，而不选择该字段。我假设表上存在字段OUTCOME。如果不是这样，则需要先创建它或做一个alter table以添加该字段，然后才能向其中写入任何内容。
我建议您预先创建字段（我认为您已经完成）。但是请确保默认值是NULL，这样您就不必在select中执行NULL技巧，还请确保该字段允许采用NULL值，否则您将看到错误。选择变为：

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:

getSQL.Open "SELECT FIELD1, FIELD2, OUTCOME FROM TABLE", adoConn, adOpenStatic, adLockOptimistic

然后在函数中按以下方式管理NULL值：

And then manage the NULL value in the function as follows:

if getSQL!FIELD1=CRITERIA then
'Error here
    getSQL!OUTCOME="SOME STRING"
ELSE
    getSQL!OUTCOME=NULL
End If

这样可确保您始终向OUTCOME字段中写入内容，以便进行处理和OUTCOME

This ensure that you always write something to OUTCOME field so processing and OUTCOME don't get out of sync.

此外，当您执行以下操作时，我仍然认为您已将记录集数据与服务器离婚：

Also I still think that you have divorced the recordset data from the server when you:

Set getSQL.ActiveConnection = Nothing

Do完成后释放资源。
您可能还需要一个

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.

这篇关于编辑ADODB记录集中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！