Tuesday, October 14, 2014

ORA-01722: invalid number - Parameters should always be added in order

I got this error while run the update statement.

The issue is due to the order while adding parameters not consistent with the order of the sql.
        const string UpdateXXXXXXXQuery = @"UPDATE XXXXXXX
                                                    SET STATUS           = :v_STATUS,
                                                    STATUSDATE       = SYSDATE,
                                                    STATUSUPDATEUSER = :v_USER                            
                                                    WHERE COLYEAR        = :v_YEAR
                                                    AND EMPLOYERNUMBER   = :v_EMPLOYER";
Parameters should always be added in the order of the sql
            OracleCommand cmd = new OracleCommand();
            OracleDataReader odr = null;
            cmd.Connection = dataDBConnection;
            cmd.CommandText = UpdateXXXXXXXQuery;
            cmd.CommandType = CommandType.Text;
            cmd.Prepare();
            cmd.Parameters.Clear();
            cmd.Parameters.Add("v_STATUS", "SUBMITTED");//1st
            cmd.Parameters.Add("v_USER", userLogin);//2nd
            cmd.Parameters.Add("v_YEAR", OracleDbType.Int32, DateTime.Now.Year, ParameterDirection.Input);//3rd
            cmd.Parameters.Add("v_EMPLOYER",OracleDbType.Int32,  employerID,ParameterDirection.Input);//4th            
            cmd.ExecuteNonQuery();
            cmd.Dispose();

No comments:

Post a Comment