Hi,

 I have trying to pass parameters to Oracle Stored Procedure using ODP.NET through an ASP.NET application.

I dont get any Compile time error. But when I anm executing nonquery command its returning -1 and nothing is updated to DB.

But when I am using inline query everything works fine.

Please find my code snippet below:

int intOutcome = 0;
        using (OracleConnection orclConnection = new OracleConnection(m_strConnectionString))
        using (OracleCommand orclCommand = new OracleCommand())
        {            
            orclCommand.CommandText = "storepdproc";
            orclCommand.CommandType = CommandType.StoredProcedure;
            orclCommand.Connection = orclConnection;

            //Setting up Userid Parameter
            OracleParameter userid = new OracleParameter("userid",intUserID);
            orclCommand.Parameters.Add(userid);

            //Setting up Userid Parameter
            OracleParameter qid = new OracleParameter("qid", intQID);
            orclCommand.Parameters.Add(qid);

            //Setting up Userid Parameter
            OracleParameter read = new OracleParameter("read",intRead);
            orclCommand.Parameters.Add(read);

            //Setting up Userid Parameter
            OracleParameter write = new OracleParameter("write",intWrite);
            orclCommand.Parameters.Add(write);

            //Setting up Userid Parameter
            OracleParameter delete = new OracleParameter("delete",intDelete);
            orclCommand.Parameters.Add(delete);

            //Setting up Userid Parameter
            OracleParameter admin = new OracleParameter("admin",intAdmin);
            orclCommand.Parameters.Add(admin);

            orclConnection.Open();
            intOutcome = orclCommand.ExecuteNonQuery();

            if (intOutcome == 1)
            {
                //No error
                return true;
            }
            else
            {
                //Errored out
                return false;

            }
        }

 Hi,

I even tried the following way to do it, but still its not updating the database.

orclCommand.Parameters.Add(new OracleParameter("ReadPriv", OracleDbType.Int32, ParameterDirection.Input));
            orclCommand.Parameters.Add(new OracleParameter("WritePriv", OracleDbType.Int32, ParameterDirection.Input));
            orclCommand.Parameters.Add(new OracleParameter("DeletePriv", OracleDbType.Int32, ParameterDirection.Input));
            orclCommand.Parameters.Add(new OracleParameter("AdminPriv", OracleDbType.Int32, ParameterDirection.Input));
            orclCommand.Parameters.Add(new OracleParameter("QID", OracleDbType.Int32, ParameterDirection.Input));
            orclCommand.Parameters.Add(new OracleParameter("userid",OracleDbType.Int32,ParameterDirection.Input));
            

            orclCommand.Parameters["ReadPriv"].Value = intRead;
            orclCommand.Parameters["WritePriv"].Value = intWrite;
            orclCommand.Parameters["DeletePriv"].Value = intDelete;
            orclCommand.Parameters["AdminPriv"].Value = intAdmin;
            orclCommand.Parameters["QID"].Value = intQID;
            orclCommand.Parameters["userid"].Value = intUserID;

            orclConnection.Open();
            intOutcome = orclCommand.ExecuteNonQuery();

            if (intOutcome == 1)
            {
                //No error
                return true;
            }
            else
            {
                //Errored out
                return false;

            }

 I am also attaching the stored procedure I am trying to execute from ASP.net using ODP.NET

 
create or replace
PROCEDURE PL_RTNS_UPDATE_CFQUSERPRIV
( ReadPriv IN NUMBER
, WritePriv IN NUMBER
, DeletePriv IN NUMBER
, AdminPriv IN NUMBER
, QID IN NUMBER
, UserID IN NUMBER
) AS
BEGIN
  Update rtns_cfq_user_priv set read_priv= readpriv, write_priv= writepriv, delete_priv= deletepriv, admin_priv= adminpriv
  where q_id= qid and user_id= userid;
END PL_RTNS_UPDATE_CFQUSERPRIV;

  Thanks In Advance
Aditya 

adityapawar:

 I have trying to pass parameters to Oracle Stored Procedure using ODP.NET through an ASP.NET application.

Check this link - 

http://www.oracle.com/technology/oramag/oracle/05-sep/o55odpnet.html

adityapawar:

I anm executing nonquery command its returning -1

Show your SP.

 I have attached my SP in previous post....

here it goes : 

create or replace
PROCEDURE PL_RTNS_UPDATE_CFQUSERPRIV
( ReadPriv IN NUMBER
, WritePriv IN NUMBER
, DeletePriv IN NUMBER
, AdminPriv IN NUMBER
, QID IN NUMBER
, UserID IN NUMBER
) AS
BEGIN
  Update rtns_cfq_user_priv set read_priv= readpriv, write_priv= writepriv, delete_priv= deletepriv, admin_priv= adminpriv
  where q_id= qid and user_id= userid;
END PL_RTNS_UPDATE_CFQUSERPRIV;

 I tried it the way its showing in the link but still its not updating the database, this is my code

int intOutcome = 0;
        using (OracleConnection orclConnection = new OracleConnection(m_strConnectionString))
        using (OracleCommand orclCommand = new OracleCommand())
        {
            orclCommand.CommandText = "PL_RTNS_UPDATE_CFQUSERPRIV";            
            orclCommand.CommandType = CommandType.StoredProcedure;
            orclCommand.Connection = orclConnection;

            //Setting up Userid Parameter
            OracleParameter userid = new OracleParameter();
            userid.OracleDbType = OracleDbType.Int32;
            userid.Value = intUserID;
            orclCommand.Parameters.Add(userid);

            //Setting up qid Parameter
            OracleParameter qid = new OracleParameter();
            qid.OracleDbType = OracleDbType.Int32;
            qid.Value = intQID;
            orclCommand.Parameters.Add(qid);

            //Setting up read Parameter
            OracleParameter read = new OracleParameter();
            read.OracleDbType = OracleDbType.Int32;
            read.Value = intRead;
            orclCommand.Parameters.Add(read);

            //Setting up write Parameter
            OracleParameter write = new OracleParameter();
            write.OracleDbType = OracleDbType.Int32;
            write.Value = intWrite;
            orclCommand.Parameters.Add(write);

            //Setting up delete Parameter
            OracleParameter delete = new OracleParameter();
            delete.OracleDbType = OracleDbType.Int32;
            delete.Value = intDelete;
            orclCommand.Parameters.Add(delete);

            //Setting up admin Parameter
            OracleParameter admin = new OracleParameter();
            admin.OracleDbType = OracleDbType.Int32;
            admin.Value = intAdmin;
            orclCommand.Parameters.Add(admin);

            orclConnection.Open();
            intOutcome = orclCommand.ExecuteNonQuery();

            if (intOutcome == 1)
            {
                //No error
                return true;
            }
            else
            {
                //Errored out
                return false;

            }
        }

Hi all, eventually I found the problem with my code from OTN Discussion Form and thanks to Mark A. Williams.

Please follow the link below to read it:

http://forums.oracle.com/forums/thread.jspa?messageID=2766679&#2766679