varchar数据类型到datetime数据类型的转换导致超出范围的值错误 [英] The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error
问题描述
varchar数据类型到datetime数据类型的转换导致超出范围的值误差
我试图将数据输入到我的表使用的一种形式，日期格式，同时在表单验证和SQL Server都DD / MM / YY，但是当我试图从形式用了一天提交数据高于12（例如13/12/2012），它抛出一个异常，其原因是varchar数据类型到datetime数据类型的转换导致超出范围的数值错误，如果我尝试输入数据成在MM / DD / YY格式的形式，它指出：错误的日期格式，这是假设意味着DD / MM / YY格式是正确的格式
这里的$ C $下我下面的表格：
私人无效btnAddProject_Click（对象发件人，EventArgs的）
{
日期时间的startDate;
DateTime的结束日期;
如果（txtProjectName.Text ==）//客户端验证
{
的MessageBox.show（请输入项目名称）;
返回;
}
尝试
{
的startDate = DateTime.Parse（txtProjectStart.Text）;
结束日期= DateTime.Parse（txtProjectEnd.Text）;
}
赶上（例外）
{
的MessageBox.show（错误的日期格式）;
返回;
}
fa.CreateProject（txtProjectName.Text，的startDate，结束日期，（INT）cbCustomers.SelectedValue，ptsUser.Id）;
txtProjectName.Text =;
txtProjectStart.Text =;
txtProjectEnd.Text =;
cbCustomers.SelectedIndex = 0;
的MessageBox.show（项目创建）;
adminControl.SelectTab（2）;
} //结束btnAddProject
这是code在我的DAO：
公共无效的createProject（字符串名称，日期时间的startDate，日期时间结束日期，诠释客户ID，INT administratorId）
{
SQL字符串;
SqlConnection的CN;
的SqlCommand CMD;
的Guid projectId = Guid.NewGuid（）;
SQL =INSERT INTO项目（ProjectId，名称，ExpectedStartDate，ExpectedEndDate，客户ID，AdministratorId）;
SQL + =的String.Format（VALUES（'{0}，{1}，{2}，{3}，{4}，{5}），projectId，姓名，的startDate，结束日期，客户ID，administratorId）;
CN =新的SqlConnection（Properties.Settings.Default.WM75ConnectionString）;
CMD =新的SqlCommand（SQL，CN）;
尝试
{
cn.Open（）;
cmd.ExecuteNonQuery（）;
}
赶上（SQLEXCEPTION前）
{
抛出新的异常（错误创建项目，前）;
}
最后
{
cn.Close（）;
}
} //结束的createProject方法
这是我的$ C $下我的门面：
公共无效的createProject（字符串名称，日期时间的startDate，日期时间结束日期，诠释客户ID，INT administratorId）
{
dao.CreateProject（姓名，的startDate，结束日期，客户ID，administratorId）;
} //结束的createProject
基本上，你不应该传递的DateTime 值的字符串的在您的SQL所有。使用参数化的SQL，而直接设置参数值。你应该的总是的使用参数化的SQL尽可能：
- 在它提供了更好的code /数据分离
- 避免了问题的转换（像这样）
- 避免了SQL注入atttacks
此外，你的异常处理是毫无意义的复杂。只需使用使用语句，并让 SQLEXCEPTION 泡了直接 - 为什么还要将其包装到香草异常？
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error
I'm trying to enter data into my table using a form, the date formats in both the form validation and the sql server are both dd/mm/yy, however when i try to submit data from the form with a day higher than 12 (e.g. 13/12/2012) it throws an exception whose cause is "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error", and if i try to enter data into the form in a mm/dd/yy format it states "wrong date format" which is suppose means that the dd/mm/yy format is the correct format
here's the code for my form below:
private void btnAddProject_Click(object sender, EventArgs e)
{
DateTime startDate;
DateTime endDate;
if (txtProjectName.Text == "") //client side validation
{
MessageBox.Show("Enter Project Name");
return;
}
try
{
startDate = DateTime.Parse(txtProjectStart.Text);
endDate = DateTime.Parse(txtProjectEnd.Text);
}
catch (Exception)
{
MessageBox.Show("Wrong Date Format");
return;
}
fa.CreateProject(txtProjectName.Text, startDate, endDate, (int)cbCustomers.SelectedValue, ptsUser.Id);
txtProjectName.Text = "";
txtProjectStart.Text = "";
txtProjectEnd.Text = "";
cbCustomers.SelectedIndex = 0;
MessageBox.Show("Project Created");
adminControl.SelectTab(2);
}// end btnAddProject
And this is the code in my DAO:
public void CreateProject(string name, DateTime startDate, DateTime endDate, int customerId, int administratorId)
{
string sql;
SqlConnection cn;
SqlCommand cmd;
Guid projectId = Guid.NewGuid();
sql = "INSERT INTO Project (ProjectId, Name, ExpectedStartDate, ExpectedEndDate, CustomerId, AdministratorId)";
sql += String.Format("VALUES('{0}', '{1}', '{2}', '{3}', {4}, {5})", projectId, name, startDate, endDate, customerId, administratorId);
cn = new SqlConnection(Properties.Settings.Default.WM75ConnectionString);
cmd = new SqlCommand(sql, cn);
try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Error Creating Project", ex);
}
finally
{
cn.Close();
}
}//end CreateProject Method
This is my code for my facade:
public void CreateProject(string name, DateTime startDate, DateTime endDate, int customerId, int administratorId)
{
dao.CreateProject(name, startDate, endDate, customerId, administratorId);
}//end CreateProject
Basically, you shouldn't be passing the DateTime values as strings in your SQL at all. Use parameterized SQL, and just set the parameter value directly. You should always use parameterized SQL as far as possible:
- It gives better code/data separation
- It avoids problematic conversions (like this one)
- It avoids SQL injection atttacks
Additionally, your exception handling is pointlessly complex. Just use a using statement, and let the SqlException bubble up directly - why bother wrapping it in a vanilla Exception?
这篇关于varchar数据类型到datetime数据类型的转换导致超出范围的值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
