插入错误：varchar数据类型到datetime数据类型的转换导致超出范围的值 [英] Insert error: the conversion of a varchar datatype to a datetime data type resulted in an out-of-range value
问题描述
我用下面的code插入起始日期（S）和结束日期（S）到我的迭代表。我在显示文本框我这样的输出：DD / MM / YYYY，但我写我的分贝为MM / DD / YYYY，因为datetime数据类型的 - 因此我使用：
System.Globalization.CultureInfo CI =新System.Globalization.CultureInfo（EN-GB）;
sc.Add（PROJ_ID +，+ Convert.ToDateTime（box1.Text，CI）+，+ Convert.ToDateTime（box2.Text，CI））;
我是pretty肯定这code工作在本地主机上就好了，但是当我上传到服务器，我得到的错误：插入错误：varchar数据类型的转换为datetime数据类型导致超出范围的值。请帮忙！谢谢！
下面是完整的code：
私人无效InsertRecords（StringCollection SC）
{ 康涅狄格州的SqlConnection =新的SqlConnection（GetConnectionString（））;
StringBuilder的SB =新的StringBuilder（的String.Empty）; 字符串[] splitItems = NULL; 的foreach（在SC字符串项）
{
常量字符串的SQLStatement =INSERT INTO迭代（专案编号，起始日期，结束日期）VALUES 如果（item.Contains（，））
{ splitItems = item.Split（，ToCharArray（））;
sb.AppendFormat（{0}（{1}，{2}，{3}）;，的SQLStatement，splitItems [0]，splitItems [1]，splitItems [2]）; }
} 字符串SQL =INSERT INTO ProjectIterationMember（ProjectIterationID，MEMBERID）选择ProjectIterationID AS pro_it_id，@member_id FROM次迭代，其中专案编号='+ PROJ_ID +'; 尝试
{ conn.Open（）;
CMD的SqlCommand =新的SqlCommand（sb.ToString（），康涅狄格州）;
的SqlCommand CMD2 =新的SqlCommand（SQL，conn）;在 cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery（）; 的SqlParameter memberParameter =新的SqlParameter（@ member_id，SqlDbType.Int）;
cmd2.Parameters.Add（memberParameter）; cmd2.CommandType = CommandType.Text; CMD2 prepare（）。 memberParameter.Value = project_manager.SelectedValue;
cmd2.ExecuteNonQuery（）; 的for（int i = 0; I＆LT; project_members.Items.Count ++ I）
{ 如果（project_members.Items [I] .Selected）
{ memberParameter.Value = project_members.Items [I] .value的;
cmd2.ExecuteNonQuery（）;
}
} //显示这表明该记录已成功插入弹出
Page.ClientScript.RegisterClientScriptBlock（typeof运算（页），脚本，警报（新迭代成功添加！'）;，真正的）;
} 赶上（System.Data.SqlClient.SqlException前）
{ 弦乐味精=插入错误：;
味精+ = ex.Message;
抛出新的异常（MSG）;
} 最后
{
conn.Close（）;
}}保护无效btnSaveIterations_Click（对象发件人，EventArgs的发送）
{ INT的rowIndex = 0; StringCollection SC =新StringCollection（）; 如果（的ViewState [CurrentTable]！= NULL）
{ 数据表dtCurrentTable =（数据表）的ViewState [CurrentTable];
如果（dtCurrentTable.Rows.Count大于0）
{ 的for（int i = 1; I＆LT; = dtCurrentTable.Rows.Count;我++）
{ //提取文本框值
文本框BOX1 =（文本框）Gridview1.Rows [rowIndex位置] .Cells [1] .FindControl（start_iteration）;
文本框BOX2 =（文本框）Gridview1.Rows [rowIndex位置] .Cells [2] .FindControl（end_iteration）; System.Globalization.CultureInfo CI =新System.Globalization.CultureInfo（EN-GB）; //从文本框获取值
//然后将其添加到用逗号集合，作为分隔值 sc.Add（PROJ_ID +，+ Convert.ToDateTime（box1.Text，CI）+，+ Convert.ToDateTime（box2.Text，CI））; rowIndex位置++; } //调用方法执行插入 InsertRecords（SC）;
的Response.Redirect（Request.Url.ToString（））; //r.Close（）;
//conn.Close（）; }
}
}
首先：串联起来你的INSERT语句是真的不好的做法，并打开大门，SQL注入攻击。不这样做 - 使用参数化查询，而不是！
常量字符串的SQLStatement =
INSERT INTO迭代（专案编号，起始日期，结束日期）+
VALUES（@ProjectID，@StartDate，@EndDate）;
在这里：
SQL字符串=
INSERT INTO ProjectIterationMember（ProjectIterationID，MEMBERID）+
选择ProjectIterationID AS pro_it_id，@member_id+
FROM WHERE迭代=专案编号@ProjectID;
您需要设置的参数为你的的SqlCommand 和执行查询之前的值传递。
的SqlCommand _cmd =新的SqlCommand（的SQLStatement，_connection）;_cmd.Parameters.Add（@专案编号，SqlDbType.Int）;
_cmd.Parameters [@专案编号]值= 42。_cmd.Parameters.Add（@起始日期，SqlDbType.DateTime）;
_cmd.Parameters [@起始日期]值= Convert.ToDateTime（你的文本字符串）。_cmd.Parameters.Add（@结束日期，SqlDbType.DateTime）;
_cmd.Parameters [@结束日期]值= Convert.ToDateTime（你的文本字符串）。
二：SQL Server有从一系列 1753年1月1日来9999年底 - 如果您的任何字符串重新present之前的日期1753，你得到这个问题。验证您的输入！当您使用参数化查询，你可以在你在哪里设置 SqlCommand.Parameters 的值点做到这一点 - 任何SQL Server支持的范围之外（如日期像1/1/0001等）被传递到SQL Server之前，必须先消毒。
I am using the following code to insert StartDate(s) and EndDate(s) into my Iterations table. I am displaying the output in my textbox like this: dd/MM/yyyy, but am writing to my db as MM/dd/yyyy, because of the datetime data type - hence I'm using:
System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-GB");
sc.Add(proj_id + "," + Convert.ToDateTime(box1.Text, ci) + "," + Convert.ToDateTime(box2.Text, ci));
I'm pretty sure this code worked just fine on localhost, but when I uploaded it to a server, I am getting the error: Insert error: the conversion of a varchar datatype to a datetime data type resulted in an out-of-range value. Please help! thanks!
Here's the full code:
private void InsertRecords(StringCollection sc)
{
SqlConnection conn = new SqlConnection(GetConnectionString());
StringBuilder sb = new StringBuilder(string.Empty);
string[] splitItems = null;
foreach (string item in sc)
{
const string sqlStatement = "INSERT INTO Iterations (ProjectID, StartDate, EndDate) VALUES";
if (item.Contains(","))
{
splitItems = item.Split(",".ToCharArray());
sb.AppendFormat("{0}('{1}','{2}','{3}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2]);
}
}
string sql = "INSERT INTO ProjectIterationMember (ProjectIterationID, MemberID) SELECT ProjectIterationID AS pro_it_id, @member_id FROM Iterations WHERE ProjectID = '" + proj_id + "'";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
SqlCommand cmd2 = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
SqlParameter memberParameter = new SqlParameter("@member_id", SqlDbType.Int);
cmd2.Parameters.Add(memberParameter);
cmd2.CommandType = CommandType.Text;
cmd2.Prepare();
memberParameter.Value = project_manager.SelectedValue;
cmd2.ExecuteNonQuery();
for (int i = 0; i < project_members.Items.Count; ++i)
{
if (project_members.Items[i].Selected)
{
memberParameter.Value = project_members.Items[i].Value;
cmd2.ExecuteNonQuery();
}
}
//Display a popup which indicates that the record was successfully inserted
Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('New iterations were successfully added!');", true);
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
}
protected void btnSaveIterations_Click(object sender, EventArgs e)
{
int rowIndex = 0;
StringCollection sc = new StringCollection();
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
//extract the TextBox values
TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("start_iteration");
TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("end_iteration");
System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-GB");
//get the values from the TextBoxes
//then add it to the collections with a comma "," as the delimited values
sc.Add(proj_id + "," + Convert.ToDateTime(box1.Text, ci) + "," + Convert.ToDateTime(box2.Text, ci));
rowIndex++;
}
//Call the method for executing inserts
InsertRecords(sc);
Response.Redirect(Request.Url.ToString());
//r.Close();
//conn.Close();
}
}
}
First of all: concatenating together your INSERT statement is really really bad practice and opens the door to SQL Injection. Don't do it - use parametrized queries instead!
const string sqlStatement =
"INSERT INTO Iterations (ProjectID, StartDate, EndDate) " +
"VALUES(@ProjectID, @StartDate, @EndDate)";
and here:
string sql =
"INSERT INTO ProjectIterationMember (ProjectIterationID, MemberID) " +
"SELECT ProjectIterationID AS pro_it_id, @member_id " +
"FROM Iterations WHERE ProjectID = @ProjectID";
You will need to setup parameters for your SqlCommand and pass in the values before executing the query.
SqlCommand _cmd = new SqlCommand(sqlStatement, _connection);
_cmd.Parameters.Add("@ProjectID", SqlDbType.Int);
_cmd.Parameters["@ProjectID"].Value = 42;
_cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
_cmd.Parameters["@StartDate"].Value = Convert.ToDateTime(your textbox string);
_cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);
_cmd.Parameters["@EndDate"].Value = Convert.ToDateTime(your textbox string);
Second: SQL Server has a range from 1/1/1753 to the end of the year 9999 - if any of your strings represent a date before 1753, you're getting this problem. Validate your inputs! When you use parametrized queries, you can do this at the point where you're setting the values of the SqlCommand.Parameters - anything outside the range that SQL Server supports (e.g. dates like "1/1/0001" and so forth) must be "sanitized" before being passed into SQL Server.
这篇关于插入错误：varchar数据类型到datetime数据类型的转换导致超出范围的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
