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."
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)
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
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
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
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 this to release resources after your are done. You may also need a
getSql.Update
After making changes to commit them back to database.