[b]UpdatingOracle table from VB[/b] 1

[tatochka]

I need to add and delete data in oracle table from VB app. Here is my code:

*************************************
Dim cnn As ADODB.Connection
Dim rsIhdaDoc1 As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn = "Provider=MSDAORA;Password=password;User ID=userid;Data Source=test;Persist Security Info=True"

cnn.Open
Set rsIhdaDoc1 = New ADODB.Recordset

rsIhdaDoc1.LockType = adLockOptimistic
rsIhdaDoc1.CursorType = adOpenDynamic
rsIhdaDoc1.Open "IHDA_DOC", cnn, , , adCmdTable

rsIhdaDoc1.AddNew
With rsIhdaDoc1
.........
rsIhdaDoc1.Update
End With
**********************************************

It stops at AddNew and gives me a message "Runtime error 3251. Current recordset doesn't support updating. This may be the limitation of the provider or selected locktype"

Can somebody tell me what should I do to make it updateable? Thanks.

 

[Sheco]

Are you sure that the user has INSERT permisson on the IHDA_DOC table?

 

[tatochka]

The user is the owner of the table.

 

[Sheco]

Well it would probably work then if you just construct an INSERT statment and use the connection's .Execute() method.

Another thing to try might be calling it like this:
rsIhdaDoc1.LockType = adLockOptimistic
rsIhdaDoc1.CursorType = adOpenDynamic
rsIhdaDoc1.CursorLocation = adUseClient
Set rsIhdaDoc1.ActiveConnection = cnn
rsIhdaDoc1.Open "SELECT * FROM IHDA_DOC WHERE 1=2"


Also be sure to check to make sure that cnn.State = 1 and cnn.Errors.Count = 0 and also that rsIhdaDoc1.State = 1

 

[tatochka]

Thank you very much Sheco for all your help. I changed the code to:

' Open connection.
Set cnn = New ADODB.Connection
cnn = "......"
cnn.Open

' Open recordset
Set rsIhdaDoc1 = New ADODB.Recordset
rsIhdaDoc1.LockType = adLockOptimistic
rsIhdaDoc1.CursorType = adOpenDynamic
rsIhdaDoc1.CursorLocation = adUseClient
Set rsIhdaDoc1.ActiveConnection = cnn
rsIhdaDoc1.Open "Select * from IHDA_DOC"


and it works now. Although I don't understand what's the difference