Datetimepicker problem in C# winform

Question

1.00/5 (1 vote)

See more:

i use Two DateTimePicker for show data from my database

This Code Work Fine but Problem Start when i Select date in my first DateTimePicker may-2019 Code Break in my Code Break Point is --con.Open();

Error Show:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Unspecified error

in My DateBase Date is

number -- Date
1-----1/2/2020
3-----10/3/2020
5-----11/6/2020
6-----17/6/2020

What I have tried:

private void Count()
{
OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);
string sql1 = "select count(Number) as totalcount from customer Where [Date] between @From and @To ";

con.Open(); // this is code break Point
OleDbCommand sc = new OleDbCommand(sql1, con);
sc.Parameters.AddWithValue("@From", dtpFrom.Text);
sc.Parameters.AddWithValue("@To", dtpTo.Text);
OleDbDataReader reader;
reader = sc.ExecuteReader();
while (reader.Read())
{
var f = reader["totalcount"].ToString();
var a = (f == "" ? 0 : Convert.ToInt32(f));
labCount.Text = a.ToString();
}
}

Posted 13-Jul-20 19:51pm

Amar chand123

Updated 13-Jul-20 20:32pm

v3

Add a Solution

2 solutions

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 · 2020-07-13T20:14:00

Solution 1

It's a datetime picker, and you are using DATETIME columns in your DB.
So why are you using a text version of the DTP value?

sc.Parameters.AddWithValue("@From", dtpFrom.Text);
sc.Parameters.AddWithValue("@To", dtpTo.Text);

When you do that, you convert a DateTime value to a string, pass that to the DB engine which tries to convert it back to a DATETIME object, and gets it wrong.
Pass the DateTime value itself directly and no conversions need to happen, so it's more efficient, and it should work:

sc.Parameters.AddWithValue("@From", dtpFrom.Value);
sc.Parameters.AddWithValue("@To", dtpTo.Value);

Oh, and "Date" is a reserved word in many DB engines, so it probably need to be quoted when you use it, or better changed to a more meaningful name: "ExpiryDate" maybe, or "InsertDate" perhaps.

Posted 13-Jul-20 20:14pm

OriginalGriff

Updated 13-Jul-20 20:16pm

v2

Comments

Amar chand123 14-Jul-20 2:23am

i try this Before but not work
Error show
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Data type mismatch in criteria expression.

OriginalGriff 14-Jul-20 2:44am

Check your DB. Have you tried to store darte info in a text based column? If so, change it to a DATE or DATETIME column - and I'll bet you'll find that fails as well because something in there is not a valid date ... and you'll need to fix that first.

Amar chand123 14-Jul-20 2:53am

in DB my Column Data Type is Date/Time and Format is ShortDate

but Problem is not this Because Code Work Fine
Problem is if i Select Date Before May-2019 or After jun-2021 my Code Break and Code Break Point is ---con.Open();

OriginalGriff 14-Jul-20 3:10am

If it's failing on the Open line, then the problem isn't the date at all - it's the connection string. Us eteh debugger to find out exactly what it contains.

We can't do that for you!

Amar chand123 14-Jul-20 3:18am

i know Problem in Connection String but i Don't Know why Code Break when i Select Date Before May-2019 or After Jun-2021, i Don't Know What Connection Between My DateTimePicker Date and My Connection String Because when i Select Date Between jun-2019 to may-2021 Code Work Fine Without any Error

OriginalGriff 14-Jul-20 3:40am

It doesn't, if it fails at the Open, then the values in the DTP haven't been used yet.

PUt a breakpoint on the Open line, and look at exactly what is in the connection string.

Maciej Los · Answer 2 · 2020-07-13T20:22:00

Replace this:

C#

string sql1 = "select count(Number) as totalcount from customer Where Date between @From and @To ";

with:

C#

string sql1 = "select Nz(count(Number),0) as totalcount from customer Where [Date] between ? and ? ";

Then...

C#

sc.Parameters.Add(new OleDbParameter(){Type= OleDbType.Date, Value=dtpFrom.Value});
sc.Parameters.Add(new OleDbParameter(){Type= OleDbType.Date, Value=dtpTo.Value});
int result = (int)sc.ExecuteScalar();

Note #1: Date is reserved word[^] for MS Access database engine, so it should be arrounded with [] brackets.
Note #2: OleDb is using un-named parameters.
Note #4: Nz function[^] is used to replace NULL with default value.
Note #4: Use OleDbCommand.ExecuteScalar Method (System.Data.OleDb) | Microsoft Docs[^] to get single value.