0

I am executing below function within my c# program

CREATE OR REPLACE FUNCTION F_INSERT_ORDER_DATA (  P_CUSTOMER_ID     IN NUMBER,
                                                  P_NOTES                  IN VARCHAR2,
                                                  P_CREATED_BY         IN VARCHAR2)
   RETURN NUMBER
IS
   vCREATED_ON   DATE := SYSDATE;
   vORDER_ID     NUMBER;
BEGIN
   INSERT INTO orders ( ORDER_ID,
                                        CUSTOMER_ID,
                                        NOTES,
                                        CREATED_BY,
                                        CREATED_ON)
                        VALUES ( NULL,                             --ORDER_ID Filled by trigger
                                        P_CUSTOMER_ID,         --CUSTOMER_ID
                                        P_NOTES,                    --NOTES
                                        P_CREATED_BY,             --CREATED_BY
                                        vCREATED_ON)              --CREATED_ON
RETURNING ORDER_ID INTO vORDER_ID;

   RETURN (vORDER_ID);

END;

but I am getting error that I cant insert null into CUSTOMER_ID column so the error is by passing parameters from c# to oracle function I need someone to help my by passing parameters ?

    private void B_Insert_Click(object sender, EventArgs e)
    {
        if (PaidCash < TotalInv) 
        {
            MessageBox.Show("paid amount less than cost", "be careful", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            TB_PAID_CASH.BackColor = Color.Tomato;
            TB_PAID_CASH.Focus();
            return;
        }
        else if (Reminder < 0)
        {
            MessageBox.Show("zero value", "be careful", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            TB_Discount.BackColor = Color.Tomato;
            TB_Discount.Focus();
            return; 
        }
        else
        {
            Form_control("DataInserted");

            string connstr = @"Data Source=orcl; User Id=admin; password=123123;";

            string insertcmdtxt = @"F_INSERT_ORDER_DATA";   //~ F_INSERT_ORDER_DATA ~//

            using (OracleConnection conn = new OracleConnection(connstr))
            using (OracleCommand cmd = new OracleCommand(insertcmdtxt, conn))
            {
                try
                {
                    conn.Open();

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.CommandText = insertcmdtxt;

                    cmd.Parameters.Add(new OracleParameter("P_CUSTOMER_ID",TB_CUSTOMER_ID.Text ));
                    cmd.Parameters.Add(new OracleParameter("P_NOTES", null));
                    cmd.Parameters.Add(new OracleParameter("P_CREATED_BY", "System"));
                    cmd.Parameters.Add(":vORDER_ID", OracleDbType.Int64, ParameterDirection.ReturnValue);

                    cmd.ExecuteNonQuery();

                    TB_INVOICE_ID.Text = (cmd.Parameters[":vORDER_ID"].Value).ToString();

                }
                catch (Exception EX)
                {
                    MessageBox.Show(EX.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
        }
    }

1 Answer 1

1

Use this syntax to pass parameters values

 int customerID = Convert.ToInt32(TB_CUSTOMER_ID.Text);
 cmd.Parameters.Add("P_CUSTOMER_ID",OracleDbType.Int32).Value = customerID;

Consider the fact that you should pass parameters specifying the type expected by the underlying column. If the CustomerID is an integer then do not pass a string but appropriately convert the value to an integer and use the correct OracleDbType enumeration. If you don't take this habit you will find yourself in troubles when for whatever reason the database engine use a different method to convert values (Particularly problematic are floating point values and dates)

Sign up to request clarification or add additional context in comments.

4 Comments

the datatype of customerID column in database is number but their is no number datatype with OracleDbType so does using int64 will couse problem ?
According to this page on MSDN you have a decimal value. Weird for an ID
While this is the Oracle docs on DataType mappings docs.oracle.com/cd/E56485_01/win.121/e55744/…
Thanks for the links they are really useful... and yes that wire I am getting error input string was not in a correct format tried to convert to decimal or int that wont work out decimal CUSTOMER_ID = Convert.ToDecimal(TB_CUSTOMER_ID.Text); cmd.Parameters.Add(new OracleParameter("P_CUSTOMER_ID", OracleDbType.Decimal).Value = CUSTOMER_ID);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.