ORA-01843: not a valid month -- working on DB but not when doing the same on asp.net web pages

Question

I'm trying to insert a record into the DB via asp.net C# web page using 'add button'. The date format on my DB is 'dd/MON/yyyy'. the isert statement works fine on my DB but it doesnt in asp.net.

On my DB:: WORKS FINE!

INSERT INTO EVENT (RES_ID,EMP_ID,PHONE_NUMBER,EVENT_DT,TIME_SLOT)  
values (null,100,'123-123-1233','01/Oct/2012','08:00 PM - 12:00 AM');

Om myaspx.cs page:: THROWS THE ERROR ""Error inserting record! ORA-01843: not a valid month"" on the webpage.

    string insertSQL;
    insertSQL = "insert into event (res_id,emp_id,phone_number,event_dt,time_slot) ";
    insertSQL += " values (:res_id,:emp_id,:phone_number,:event_dt,:time_slot)";

    OracleConnection con = new OracleConnection(connectionString);
    OracleCommand cmd = new OracleCommand(insertSQL, con);

    cmd.Parameters.Add(":emp_id", cboResOrEmpName.SelectedValue);
    cmd.Parameters.Add(":res_id", null);
    cmd.Parameters.Add(":phone_number", txtContactNo.Text);
    cmd.Parameters.Add(":time_slot", rblTimeSlot.Text);
    cmd.Parameters.Add(":event_dt", txtEvtDt.Text);

    // Try to open the database and execute the update.
    int added = 0;
    try
    {
        con.Open();
        added = cmd.ExecuteNonQuery();
        lblResults.Text = added.ToString() + " record added!";
    }
    catch (Exception err)
    {
        lblResults.Text += "Error inserting record! ";
        lblResults.Text += err.Message;
    }
    finally
    {
        con.Close();
    }

I used a textbox for the event date with AJAX caledar extansion. Following is its definition.

<asp:TextBox ID="txtEvtDt" runat="server"></asp:TextBox>
<asp:CalendarExtender ID="txtEvtDt_CalendarExtender" runat="server" 
TargetControlID="txtEvtDt" Format="dd/MMM/yyyy">
</asp:CalendarExtender>

Please help me understand the problem. Any help is greatly appreciated. Thank you!

Solution 1

Hope this will work

cmd.Parameters.Add(":event_dt", DateTime.ParseExact(txtEvtDt.Text, "dd/MMM/yyyy", CultureInfo.InvariantCulture));

Solution 2

Your SQL statement works fine on your database presumably because DD/Mon/YYYY is the default date format for whatever program you use to connect to Oracle (SQL*Plus, SQL Developer, TOAD, PL/SQL Developer, etc.) Presumably when ASP.NET connects to the database it doesn't use the same default date format.

I'd recommend not relying on automatic conversion between dates and strings. If you're converting between dates and strings, always be explicit about it. Use TO_DATE(some_string, 'DD/Mon/YYYY') to convert a string to a date, and TO_CHAR(some_date, 'DD/Mon/YYYY') to convert a date to a string.

So, in your insertSQL string, I'd recommend replacing :event_dt with TO_DATE(:event_dt, 'DD/Mon/YYYY').