How to search between 2 dates on access database vs?

Question

0.00/5 (No votes)

See more:

I used parameters but it not working it shows error on

da.Fill(ds);

= Data type mismatch in criteria expression. how to solve it I really search everywhere but found nothing similar to this any help with this?

What I have tried:

string ccc = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ahmed\OneDrive\Documents\shop.accdb";

using (OleDbConnection conn = new OleDbConnection(ccc))
            {
string query = "select * from tbl3 where datetime between @ddlStates and @ddlStates2";
using (OleDbCommand myCommand = new OleDbCommand(query, conn))
                {

              myCommand.Parameters.AddWithValue("@ddlStates", dateTimePicker1.Value);
              myCommand.Parameters.AddWithValue("@ddlStates2", dateTimePicker2.Value);
                    conn.Open();

                    using (OleDbDataAdapter da = new OleDbDataAdapter(myCommand))
                    {
                   DataSet ds = new DataSet();
                        da.Fill(ds);
                        dataGridView1.DataSource = ds;

                    }
                }

            }

Posted 13-Jan-20 3:32am

Member 14630006

Updated 13-Jan-20 3:51am

Add a Solution

2 solutions

Solution 2

Two possibillities:
1) Don't use keywords as column names: DATETIME is a SQL datatype, so that may not be helping. You can use them, but you need to "escape" the whole column name:

SQL

... WHERE [datetime] BETWEEN ...

But ... it is considered "bad practice". Instead, use a name that describes the values in the column, not their datatype: "EnterDate", "BirthDate", "ExpiryDate" or similar.

2) Check the column datatype: if you are storing date based info in text based columns, that can also cause problems when the system tries to convert them to a DateTime for comparison. Always store values in the most appropriate datatype, not string based just "because it's easier to code" - it always causes problems later on, when they are much, much harder to fix.

Posted 13-Jan-20 3:51am

OriginalGriff

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)

Slacker007 · Accepted Answer · 2020-01-13T03:50:00

select * from tbl3 where datetime >= @ddlStates and datetime <= @ddlStates2

between does not always give you the results you are looking for

Also, I think that you should try dateTimePicker1.Text instead of Value (which is usually the index not the actual date)

and what Griff said below, make sure you have the data types correct for the ddl value/text your are sending.