No question at this time
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered users | 48684 | | Total active users | 1328 | | Act. users last 24h | 1 | | Act. users last hour | 0 | | Registered user hits last week | 67 | | Registered user hits last month | 224 |
|
Go up
INSERT INTO RETURNING with VBA using ADO/OLE DB
| Subject: |
INSERT INTO RETURNING with VBA using ADO/OLE DB |
| Author: |
Michel Cadot, France |
| Date: |
Oct 04, 2018, 07:46, 1411 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, 1410 days ago |
| Score: |
  |
| 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, 1410 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
|