I'm continue to learn using Oracle... and it's not so easy... I want make a insert with parameter and return a value
Public Function InsertMapping(GeneralDatas As List(Of String)) As Decimal
Dim idobj As Decimal = 4563
Dim Ref As String = "A85693_B"
Dim nbversion = 1
Dim idroutting As Decimal
Using con As OracleConnection = GetConnexion()
cmd As New OracleCommand With {.Connection = con}
cmd.CommandText = "insert into ROUTING
(DESCRIPTION, DRAFT_NUMBER, DRAFT_VERSION,
ESTIMATE,ID_COMPANY,ID_CUSTOMER,ID_ROUTING_TYPE,
ID_STATUS,REFERENCE,RELEASE,REVISION,SCRAP_PCENT,
SCRAP_QUANTITY,VERSION,ID_OBJECT)
values(:desc,:noplan,:indplan,0,
1,:idclt,4,381,
:ref,0,0,0,0,
:voir,:idobj) returning ID_ROUTING into :monid"
cmd.CommandType = CommandType.Text
Dim prm = New OracleParameter("monid", OracleDbType.Decimal)
prm.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(prm)
Dim prm1 = New OracleParameter("desc", OracleDbType.Varchar2)
prm1.Direction = ParameterDirection.Input
prm1.Value = ""
cmd.Parameters.Add(prm1)
Dim prm2 = New OracleParameter("noplan", OracleDbType.Varchar2)
prm2.Direction = ParameterDirection.Input
prm2.Value = "Cloison"
cmd.Parameters.Add(prm2)
Dim prm3 = New OracleParameter("indplan", OracleDbType.Varchar2)
prm3.Direction = ParameterDirection.Input
prm3.Value = ""
cmd.Parameters.Add(prm3)
Dim prm4 = New OracleParameter("idclt", OracleDbType.Decimal)'Number(20,0)
prm4.Direction = ParameterDirection.Input
prm4.Value = 5
cmd.Parameters.Add(prm4)
Dim prm5 = New OracleParameter("ref", OracleDbType.Varchar2)
prm5.Direction = ParameterDirection.Input
prm5.Value = Ref
cmd.Parameters.Add(prm5)
Dim prm6 = New OracleParameter("voir", OracleDbType.Varchar2)
prm6.Direction = ParameterDirection.Input
prm6.Value = "1"
cmd.Parameters.Add(prm6)
Dim prm7 = New OracleParameter("idobj", OracleDbType.Decimal)
prm7.Direction = ParameterDirection.Input
prm7.Value = idobj
cmd.Parameters.Add(prm7)
con.Open()
cmd.ExecuteNonQuery()'<------Error here
HelperJournal.WriteEntry("after excecute")
idroutting = cmd.Parameters("monid").Value.ToString
End Using
HelperJournal.WriteEntry("idroutting", idroutting)
Return idroutting
End Function
And here my error message
Message:ORA-01745: nom de variable hôte/attachée (bind) non valide Exception:Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01745: nom de variable hôte/attachée (bind) non valide à OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) à OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) à Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
I read so many times without see the parameter what bad linked... Thanks for your help
This
Dim prm = New OracleParameter("monid", OracleDbType.Decimal)
prm.Direction = ParameterDirection.ReturnValue
should be last and direction should be output
' all parameters first ^^^
Dim prm = New OracleParameter("monid", OracleDbType.Decimal)
prm.Direction = ParameterDirection.Output ' <-- here
con.Open()
cmd.ExecuteNonQuery()
Additionally, ODP OracleCommand had BindByName property. If you set cmd.BindByName = True, you can have any order of parameters