No question at this time
DBA Top 10
1 B. Vroman 13400
2 M. Cadot 10400
3 J. Schnackenberg 8300
4 T. Boles 7850
5 A. Kavsek 5700
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
8 P. Wisse 1000
10 . Lauri 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48302
Total active users1583
Act. users last 24h3
Act. users last hour1
Registered user hits last week197
Registered user hits last month812
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, 73 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, 72 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, 72 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