Error inserting data into recordset with oraOLEDB

Question

Good afternoon, everyone.

I have a problem in various applications here in the company that are in VB6. When I try to include a record in a recordset an error occurs (it is described in the code), but it only happens when I use the preview oraOLEDB. To the MSDAORA works normally. I set an example to do the tests, basically with the instructions that are in the application.
If anyone can help I’d appreciate it!

Private Sub Command1_Click()

Dim cnn  As New ADODB.Connection 
Dim sSql As String 
Dim Rst  As ADODB.Recordset 
Dim Cmd  As New ADODB.Command 

connection using oraOLEDB

sSql = "Provider=oraOLEDB.oracle.1; Password=**; User ID=USER_DB; " & _
"Data Source=ORA12C; Persist Security Info=false;"

connection using the MSDAORA

sSql = "Provider=MSDAORA; Password=**; User ID=USER_DB; " & _
"Data Source=ORA12C; Persist Security Info=false"

cnn.Open sSql 
cnn.CursorLocation = adUseClient 

sSql = "SELECT CODIGO, DESCRICAO FROM TABELA" 

Set Rst = New ADODB.Recordset 
Rst.Open sSql, cnn, adOpenStatic, adLockBatchOptimistic, adCmdText 

Corto a conexão para trabalhar como um recordset virtual. Depois monto a instrução DML.
Set Rst.ActiveConnection = Nothing

This is an example, but it’s the same as in the application.

Me.Rst.AddNew 
Me.Rst("CODIGO").Value = 999 

After assigning the value in the recordset CODE field the error occurs:

-2147217887 - Multi-step operation generated errors. Check each status value.

I know this happens when you assign a different value to the type that is in recordset, but this is not the case, both are numerical.

Me.Rst.Close 
cnn.Close 
End Sub

Answer 1

To add new records, the recordset cannot be opened with adOpenStatic

Try replacing with adOpenKeyset

Rst.Open sSql, cnn, adOpenKeyset, adLockBatchOptimistic, adCmdText