validate the date in ms access using query

Question

0.00/5 (No votes)

See more:

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

Posted 27-Feb-13 2:34am

Member 8054539

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-02-27T02:48:00

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...