Inserting in Oracle DB from vb.net application retuning value

Question

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

i think that with Oracle the order of parameters is important. The OracleParameters must be in the same order they appear in the SQL statement - Try moving the monid parameter to end of the list to match the SQL — JayV
– JayV, Commented Jun 18, 2021 at 12:30

T.S. · Accepted Answer · 2021-06-18 23:16:16Z

1

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

answered Jun 18, 2021 at 23:16

T.S.

19.7k11 gold badges70 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YannickIngenierie Over a year ago

I add too I change name of 2 parameter : Ref and desc cause can be already know in Oracle. But I follow your suggestion and it's ok , Then I think u correct futurs errors :-)

Collectives™ on Stack Overflow

Inserting in Oracle DB from vb.net application retuning value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related