oracle ORA-01843: 不是一个有效的月份——在 DB 上工作，但在 asp.net 网页上做同样的事情时不是
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12765181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01843: not a valid month -- working on DB but not when doing the same on asp.net web pages
提问by user1700818
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.
我正在尝试使用“添加按钮”通过 asp.net C# 网页将记录插入到数据库中。我的数据库上的日期格式是“dd/MON/yyyy”。isert 语句在我的数据库上运行良好，但在 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.
Om myaspx.cs 页面:: 抛出错误“”插入记录时出错！ORA-01843: 不是网页上的有效月份""。
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.
我使用文本框作为带有 AJAX caledar 扩展的事件日期。下面是它的定义。
<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!
请帮助我理解问题。任何帮助是极大的赞赏。谢谢！
回答by Jith
Hope this will work
希望这会奏效
cmd.Parameters.Add(":event_dt", DateTime.ParseExact(txtEvtDt.Text, "dd/MMM/yyyy", CultureInfo.InvariantCulture));
回答by Luke Woodward
Your SQL statement works fine on your database presumably because DD/Mon/YYYYis 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.
您的 SQL 语句在您的数据库上运行良好，大概是因为它DD/Mon/YYYY是您用于连接到 Oracle（SQL*Plus、SQL Developer、TOAD、PL/SQL Developer 等）的任何程序的默认日期格式。大概是在 ASP.NET 连接到数据库它不使用相同的默认日期格式。
I'd recommend not relying on automatic conversion between dates and strings. If you're converting between dates and strings, alwaysbe 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.
我建议不要依赖日期和字符串之间的自动转换。如果要在日期和字符串之间进行转换，请始终明确说明。使用TO_DATE(some_string, 'DD/Mon/YYYY')一个字符串转换为日期，TO_CHAR(some_date, 'DD/Mon/YYYY')将日期转换为字符串。
So, in your insertSQLstring, I'd recommend replacing :event_dtwith TO_DATE(:event_dt, 'DD/Mon/YYYY').
因此，在您的insertSQL字符串中，我建议替换:event_dt为TO_DATE(:event_dt, 'DD/Mon/YYYY').
