No question at this time
DBA Top 10
1 B. Vroman 15700
2 M. Cadot 10200
3 T. Boles 8350
4 J. Schnackenberg 8300
5 A. Kavsek 7800
6 M. Hidayathullah ... 2500
7 P. Wisse 1200
8 G. Lambregts 1100
9 T. P 1000
10 B. Derous 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48277
Total active users1612
Act. users last 24h4
Act. users last hour0
Registered user hits last week185
Registered user hits last month836
Go up

INSERT INTO RETURNING with VBA using ADO/OLE DB
Next thread: File Empty
Prev thread: Archives have to be deleted by RMAN from Standby Site

Message Score Author Date
Does anyone knows and has an example of using IN...... Michel Cadot Oct 04, 2018, 07:46
Michel, Have you tried adParamReturnValue instead...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Rob Pattyn Oct 04, 2018, 12:00
Hi Rob, Many thanks for your answer. As you ...... Michel Cadot Oct 04, 2018, 17:55

Follow up by mail Click here


Subject: INSERT INTO RETURNING with VBA using ADO/OLE DB
Author: Michel Cadot, France
Date: Oct 04, 2018, 07:46, 17 days ago
Os info: Windows 7 and up
Oracle info: 11gR2 and up
Message:
Does anyone knows and has an example of using INSERT with RETURNING clause in VBA using ADO/OLE DB.
I failed to get any other value than 0 in the returning parameter.

Here's my current code (at least one of those I tried):
        SQL = _

"INSERT INTO gdpr_composants (id, nom, basicat, ocarto, traitement) " & _
"VALUES (gdpr_seq.nextval, TRIM(?), UPPER(?), LPAD(?,5,'0'), TRIM(?))" & _
"RETURNING id INTO ?"
Set OraCmd2 = New ADODB.Command
With OraCmd2
.CommandText = SQL
.CommandType = adCmdText
.ActiveConnection = OraCon
.Parameters.Append .CreateParameter("nom", adVarChar, adParamInput, 80, CompName)
.Parameters.Append .CreateParameter("basicat", adVarChar, adParamInput, 3, CodeBasicat)
.Parameters.Append .CreateParameter("ocarto", adVarChar, adParamInput, 5, CodeOC)
.Parameters.Append .CreateParameter("traitement", adVarChar, adParamInput, 500, TraitName)
.Parameters.Append .CreateParameter("outid", adInteger, adParamOutput, 10, CompId)
' .Execute Nb, Options:=adExecuteNoRecords
Set OraReturn = .Execute
End With
' CompId = OraReturn("outid")


The row is actually inserted but the returned value is always 0 when the id is different in the database.

Also posted at:
http://www.orafaq.com/forum/t/205119/

Regards
Michel
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: INSERT INTO RETURNING with VBA using ADO/OLE DB
Author: Rob Pattyn, Belgium
Date: Oct 04, 2018, 12:00, 17 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Michel,
Have you tried adParamReturnValue instead of adParamOutput?
https://community.oracle.com/thread/1037215

Perhaps it has to do with parameter order. Have you tried bindbyname?
https://www.codeproject.com/Articles/208176/Gotcha-sharp-Using-Named-Parameters-with-Oracl
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: INSERT INTO RETURNING with VBA using ADO/OLE DB
Author: Michel Cadot, France
Date: Oct 04, 2018, 17:55, 17 days ago
Message: Hi Rob,

Many thanks for your answer.

As you suggested I have tried using "adParamReturnValue" but failed in the same way.

Unfortunately named parameters are not supported by OraOleDB (at least in 11.2, version I'm stick on, specifically the driver is "Oracle Provider for OLE DB 02.01"), so I can only used positional parameters. I don't think it is a parameter order issue as row is inserted with the correct values, only the returning "id" is not returned.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here