Alek
Alek

Reputation: 329

insert in to table gives back ORA-01843: not a valid month

I need to insert a row in my table in oracle from C# (Windows Forms)

conn.Open();
OracleCommand cmd = new OracleCommand("INSERT INTO RECIPES(id,name,time_cooking,time_prep,price,directions,user_name,submit_timestamp) VALUES (:id, :name, :time_cook, :time_prep, :price, :directions, :user_name, :sub_time)",conn);
cmd.Parameters.AddWithValue(":id",x);
cmd.Parameters.AddWithValue(":name",textBox10.Text);
cmd.Parameters.AddWithValue(":time_cook", textBox9.Text);
cmd.Parameters.AddWithValue(":time_prep",textBox8.Text);
cmd.Parameters.AddWithValue(":price", textBox6.Text);
cmd.Parameters.AddWithValue(":directions",richTextBox2.Text);
cmd.Parameters.AddWithValue(":user_name",this.username);
cmd.Parameters.AddWithValue(":sub_time",DateTime.Now.ToString("MM/dd/yyyy"));

try
{
    cmd.ExecuteNonQuery();
}
catch (OracleException ex)
{
    MessageBox.Show(ex.ToString());
}

conn.Close();

I get the error below.

ORA-01843: not a valid month

I checked in oracle :

select * 
from nls_session_parameters;

and returned NLS_DATE_FORMAT mm/dd/yyyy

Upvotes: 0

Views: 2864

Answers (1)

Thomas Levesque
Thomas Levesque

Reputation: 292765

You shouldn't pass the date as a string; you should pass it as a date instead... just remove the ToString call:

cmd.Parameters.AddWithValue(":sub_time", DateTime.Now);

If you pass a string, it will be parsed using the format specified by the nls_date_format parameter in the database. If the format you pass is not the one the database expects, the parsing will fail. By passing the date itself, you don't need to worry about the format.

Upvotes: 0

Related Questions