2 Replies Latest reply on Feb 2, 2017 9:28 PM by 3390695

    ODAC Error 01008: not all variables bound when using a Procedure to update table

    3390695

      Simple procedure to update Employee Note:

      [code]

      create or replace procedure testq (p_empiid number,p_empnote varchar2)

      is

      begin

               update l_employees set empnote=p_empnote where empiid= p_empiid;

      end;

      [/code]

       

      This works fine when running from SQL Developer or SqlPlus. However, in VB.NET I get the "01008: not all variables bound" error

       

      [code]

       

      Try

                  Dim conn As New OracleConnection

                  Dim cmd As New OracleCommand

                  conn.ConnectionString = myconn

                  cmd.Connection = conn

                  cmd.CommandType = CommandType.StoredProcedure

                  cmd.CommandText = "TESTq (:pempiid,:pempnote)"

       

                  cmd.Parameters.Add("pempiid", 1)

                  cmd.Parameters.Add("pempnote", "a1a")

       

                  conn.Open()

                  cmd.ExecuteNonQuery()

                  conn.Dispose()

       

              Catch ex As Exception

                  MsgBox(ex.ToString)

              End Try

      [/code]

       

      Anyone have any ideas for me? I have tried writing the Parameters 10 different ways along with the Command Text.

      I tried changing the SP to just run an update with the values stored in the SP - and it worked fine - The table updated. Basically CommandText = "TESTQ" with no variables.

      A Select using Variables seems to work fine - only when its a Stored Procedure in Oracle.

      As soon as I introduce variables, I get the error.

       

      Any ideaS?