locked
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;
    
                Response.Redirect("Home.aspx");
            }

    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;
                Connection.Open();
    
                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;
                cmd.Parameters.Add(ssnoParm);
    
                yrParm = cmd.CreateParameter();
                yrParm.ParameterName = "yr";
                yrParm.OracleDbType = OracleDbType.Varchar2;
                yrParm.Direction = System.Data.ParameterDirection.Input;
                yrParm.Value = yr;
                yrParm.Size = 6;
                cmd.Parameters.Add(yrParm);
    
                CursorConfirmParm = cmd.CreateParameter();
                CursorConfirmParm.ParameterName = "out_confirmation";
                CursorConfirmParm.OracleDbType = OracleDbType.RefCursor;
                CursorConfirmParm.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(CursorConfirmParm);
    
                //CursorErrorParm = cmd.CreateParameter();
                //CursorErrorParm.ParameterName = "out_error";
                //CursorErrorParm.OracleDbType = OracleDbType.RefCursor;
                //CursorErrorParm.Direction = System.Data.ParameterDirection.Output;
                //cmd.Parameters.Add(CursorErrorParm);
    
                OracleDataAdapter da = new OracleDataAdapter(cmd);
    
                try
                {
                    
                    //da.Fill(Dt);
                    //Dt.TableName = "insert_app_test";
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine("Exception: {0}", ex.ToString());
                }
    
                return ??????;
    
                Connection.Close();
    
                //return Dt;
    
            }
    Thursday, March 31, 2016 3:19 PM

Answers

  • 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();
            da.Fill(dt);
            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