使用查询验证ms访问的日期 [英] validate the date in ms access using query
问题描述
注意它是windows应用程序
保存代码如下;
尝试
{
sql =插入到Tb_Declared_Holidays（[Holiday]，[Reason]+values（''+ txt_date.Text +''，''+ txt_reason。文字+''）;
GFun.Error =;
GFun.InsertAccessData（sql）;
if（GFun.Error.ToString（）！=）
{
MessageBox.Show（GFun.Error.ToString（），Error ）;
this.Cursor = Cursors.Arrow;
}
MessageBox.Show（记录插入成功，记录插入，MessageBoxButtons.OK，MessageBoxIcon.Information）;
LoadDeclarHolidayDetails（）;
GFun.OleDbCon.Close（）;
}
catch（例外ex）
{
MessageBox.Show（ex.ToString（），Error，MessageBoxButtons.OK，MessageBoxIcon.Error）;
this.Cursor = Cursors.Arrow;
}
when我点击保存按钮并保存在数据库中。
数据库记录如下;
假日原因
1/14/2013离开
//检查日期是否已经存在
DateTime holidaydate = Convert.ToDateTime（txt_date.Text）;
sql =select * from Tb_Declared_Holidays where Holiday =''+ holidaydate + '';
oledr = GFun.ReadAcessSql（sql）;
oledr.Read（）;
if（oledr.HasRows = = true）
{
MessageBox.Show（选定日期已存在，选择差异nt Date，Date Exists，MessageBoxButtons.OK，MessageBoxIcon.Information）;
Btn_Calendar.Focus（）;
return;
}
当我点击保存按钮错误时显示如下;
字符串未被识别为有效datetime。
来自我上面的代码有什么问题。
注意它是windows应用程序
note it is windows application
Save Code as follows;
try
{
sql = "insert into Tb_Declared_Holidays ([Holiday],[Reason] " + "values(''" + txt_date.Text + "'',''" + txt_reason.Text + "'')";
GFun.Error = "";
GFun.InsertAccessData(sql);
if (GFun.Error.ToString() != "")
{
MessageBox.Show(GFun.Error.ToString(), "Error");
this.Cursor = Cursors.Arrow;
}
MessageBox.Show("Record inserted successfully", "Records Inserted", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadDeclarHolidayDetails();
GFun.OleDbCon.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Cursor = Cursors.Arrow;
}
when i click the save button and record saved in the database.
Database records as follows;
Holiday Reason
1/14/2013 Leave
//checking Date is already exists or not
DateTime holidaydate = Convert.ToDateTime(txt_date.Text);
sql = "select * from Tb_Declared_Holidays where Holiday = ''" + holidaydate + "''";
oledr = GFun.ReadAcessSql(sql);
oledr.Read();
if (oledr.HasRows == true)
{
MessageBox.Show("Selected Date is already exists,Choose Different Date", "Date Exists", MessageBoxButtons.OK, MessageBoxIcon.Information);
Btn_Calendar.Focus();
return;
}
when i click the save button error shows as follows;
string was not recognized a valid datetime.
from my above code what is the problem.
note it is windows application
推荐答案
你在这里犯了四大错误：首先你假设SQL服务器PC上的日期格式总是与上面的设置相同用户机器 - 这不是真的。这意味着如果使用的日期格式设置为US并且SQL机器设置为UK，则03/04/13这样的条目将被假定为2013年4月3日，当用户意味着3月4日时。 br />
其次，你假设用户不犯错误：他们这样做。并且你有责任确保当他们犯错时，他们会被抓住并报告，而不是引起随机问题。因此，请检查日期，整数以及它们触摸的其他所有内容 - 因为一旦错误进入您的数据库，它就会非常好......
第三，您假设您的用途不会试图破坏你的数据库。当您连接字符串以形成SQL语句时，您会对意外或故意的SQL注入攻击敞开大门，这可能会破坏或破坏您的数据库。改为使用参数化查询。
第四，假设您的用户喜欢打字并且不像您自己那样懒惰。这是使用文本框进行日期输入的唯一原因。好的程序员使用DateTimePicker控件，所以用户不必输入，也不能输入无效的日期。
所以：用你的文本框替换一个DateTimePicker，使用它的Value参数，并通过参数化查询传递所有参数。您的代码不仅更易于阅读和维护，而且对您的用户来说更容易，并且您的问题可能会同时消失......
There are four big mistakes you are making here: firstly you are assuming that the date format on teh SQL server PC is always going to be the same as the setting on the user machine - this is not true. That means that if the use has his date format set to US and the SQL machine is set to UK, an entry like "03/04/13" will be assumed to be 3rd Apr 2013, when the user meant the 4th March.
Secondly, you are assuming that users do not make mistakes: they do. And it is your responsibility to be sure that when they do make mistakes, they are caught and reported instead of causing random problems. So check dates, and integers, and everything else they touch - because once an error gets into your database, it''s pretty much there for good...
Thirdly, you are assuming that your uses will not try to destroy your database. When you concatenate strings to form an SQL statement, you leave yourself wide open to accidental or deliberate SQL injection attack which can damage or destroy your DB. Use Parametrised queries instead.
Fourthly, you are assuming your users like typing and are not as lazy as yourself. That''s the only reason for using a textbox for date entry. Good programmers use DateTimePicker controls instead, so the user doesn''t have to type, and can''t enter an invalid date.
So: Replace your textbox with a DateTimePicker, use the Value parameter of that, and pass all your parameters via parametrized queries. Not only will your code be easier to read and maintain, but it will be easier for your users, and your problem will probably disappear at the same time...
这篇关于使用查询验证ms访问的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
