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

User_VGARV

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?

svenkatn-Oracle

Hi,

I am suspecting the issue is due to the mismatch in dbtype.

Please map Oracle dbType to the proper Microsoft dataType and verify the code.

thanks and regards

Venkat

svenkatn-Oracle

Hi,

I am suspecting the issue is due to the mismatch in dbtype.

Please map Oracle dbType to the proper Microsoft dataType and verify the code.

thanks and regards

Venkat

User_VGARV

I changed the Stored Procedure to use a datatype of INT instead of NUMBER and it is now working, (along with a small change to my Command Text. BEGIN; and END;  is needed)