Using ASP.Net Class to to Insert Data into Oracle and Return Confirmation Code or Error Code RRS feed

  • Question

  • User564995064 posted

    In my application, I have a class which passes parameters to a stored procedure.  The stored procedure inserts data into the Oracle database and returns either a confirmation number if the insert is successful or an error code if the insert failed.

    I am not getting the confirmation code back to the class.  However, when I run the stored procedure, it is returning it.  Here is my  code passing data to the class:

            protected void btnSave_Click(object sender, EventArgs e)
                string ssno = Convert.ToString(Session["ssno"]);
                string yr = Convert.ToString("2016");
                string confirm = null;
                clClass clInsert = new clClass();
                clInsert.fInsertApp(ssno, yr, out confirm);
                string result = confirm;

    Here is my class code.  I am not sure if I should use a datatable, datareader or what.  I realize the code for the class is not right.  Any suggestions?  Thanks!

            public DataTable fInsertApp(string ssno, string yr)
                //DataTable Dt = new DataTable();
                OracleConnection Connection = null;
                OracleCommand cmd = null;
                OracleParameter ssnoParm = null;
                OracleParameter yrParm = null;
                OracleParameter CursorConfirmParm = null;
                //OracleParameter CursorErrorParm = null;
                Connection = new OracleConnection();
                Connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                cmd = new OracleCommand();
                cmd.Connection = Connection;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "StoredProcedure.insert_app_test";
                ssnoParm = cmd.CreateParameter();
                ssnoParm.ParameterName = "ssno";
                ssnoParm.OracleDbType = OracleDbType.Varchar2;
                ssnoParm.Direction = System.Data.ParameterDirection.Input;
                ssnoParm.Value = ssno;
                ssnoParm.Size = 15;
                yrParm = cmd.CreateParameter();
                yrParm.ParameterName = "yr";
                yrParm.OracleDbType = OracleDbType.Varchar2;
                yrParm.Direction = System.Data.ParameterDirection.Input;
                yrParm.Value = yr;
                yrParm.Size = 6;
                CursorConfirmParm = cmd.CreateParameter();
                CursorConfirmParm.ParameterName = "out_confirmation";
                CursorConfirmParm.OracleDbType = OracleDbType.RefCursor;
                CursorConfirmParm.Direction = System.Data.ParameterDirection.Output;
                //CursorErrorParm = cmd.CreateParameter();
                //CursorErrorParm.ParameterName = "out_error";
                //CursorErrorParm.OracleDbType = OracleDbType.RefCursor;
                //CursorErrorParm.Direction = System.Data.ParameterDirection.Output;
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                    //Dt.TableName = "insert_app_test";
                catch (Exception ex)
                    System.Console.WriteLine("Exception: {0}", ex.ToString());
                return ??????;
                //return Dt;
    Thursday, March 31, 2016 3:19 PM


  • User614698185 posted

    Hi rhondadunn,

    Firstly, you should make sure your stored procedure is right.

    Secondly, I create the sample for you, like below:

    using Oracle.DataAccess;
    using Oracle.DataAccess.Client;
    public DataTable GetHeader_BySproc(string unit, string office, string receiptno)
        using (OracleConnection cn = new OracleConnection("ConnectionString")
            OracleDataAdapter da = new OracleDataAdapter();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = cn;
            cmd.CommandText = "stored procedure name";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("PUNIT", OracleDbType.Char).Value = unit;
            cmd.Parameters.Add("POFFICE", OracleDbType.Char).Value = office;
            cmd.Parameters.Add("PRECEIPT_NBR", OracleDbType.Int32).Value = receiptno;
            cmd.Parameters.Add("T_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            return dt;

    For more information, please see: http://forums.asp.net/t/2090289.aspx

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 1, 2016 5:58 AM