How to filter data between two date range from ms access database using C#

Question

0.00/5 (No votes)

See more:

Selection of date from ms access database DateTime column shows "No Record Found".
The connection to datbase using "Microsoft.ACE.OLEDB.12.0" and the front end is VS C#

What I have tried:

connectionstring — ImgBB[^]
db — ImgBB[^]
form — ImgBB[^]
query — ImgBB[^]

C#

  con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\\xxxxxxx\\yyyyy\\zzzzz\\database.accdb;Persist Security Info=True;Jet OLEDB:Database Password=xyz";
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
        
 DateTime fromdate = Convert.ToDateTime(dtpfdt.Value.ToString("dd-MMM-yyyy") + " 00:00:00 AM");
                    DateTime todate = Convert.ToDateTime(dtptodt.Value.AddDays(1.0).ToString("dd-MMM-yyyy") + " 00:00:00 AM").AddSeconds(-1.0);
                string query = "SELECT * FROM [TRANSACTION] WHERE ([DT1] BETWEEN #" + fromdate + "# AND #" + todate + "#) OR ([DT2] BETWEEN #" + fromdate + "# AND #" + todate + "#)";
-----------;
----------;
--
.
.
.

dtpfdt==> Datetime Picker(From date)
dtpto==> Datetime Picker(To date)
[TRANSACTION]--- Table
[WT1]--- Column; Type--> Date/Time    Format--> General Date
[WT2]--- Column; Type--> Date/Time    Format--> General Date

THE DATATABLE

+---------------------+----------------------+
|      DT1            |         DT2          |
+--------------------------------------------+
| 24-04-20 1:38:35 AM | 25-04-20 1:41:35 AM  |
+---------------------+----------------------+
| 24-04-20 1:39:27 AM | 25-04-20 1:42:11 AM  |
+---------------------+----------------------+
| 25-04-20 1:40:22 AM | 25-04-20 1:42:39 AM  |
+---------------------+----------------------+

I have tried >= and <= condition instead of 'BETWEEN' and changed datetime format of datatime data column and date time picker and also system clock but nothing works.
Is anything wrong in my coding?
Kindly advise me to correct my mistakes.
Thanks.

Posted 23-Apr-20 19:52pm

Member 11291660

Updated 23-Apr-20 20:05pm

Maciej Los

v2

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)

Maciej Los · Accepted Answer · 2020-04-23T20:06:00

SQL

string query = "SELECT * FROM [TRANSACTION] WHERE ([DT1] BETWEEN #" + fromdate + "# AND #" + todate + "#) OR ([DT2] BETWEEN #" + fromdate + "# AND #" + todate + "#)";

Such of query is SqlInjection[^] vulnerable!

Rather than it, use parameterized query:

SQL

string query = "SELECT * FROM [TRANSACTION] WHERE (([DT1] BETWEEN ? AND ?) OR ([DT2] BETWEEN ? AND ?));";

Usage:

C#

	string sConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourDatabase.accdb;Persist Security Info =False;";
	DataTable dt = new DataTable();

	DateTime fromdate = DateTimePickerDateFrom.Value;
	DateTime todate = DateTimePickerDateTo.Value;
	string sComm = "SELECT * FROM [TRANSACTION] WHERE (([DT1] BETWEEN ? AND ?) OR ([DT2] BETWEEN ? AND ?));";

	using (OleDbConnection oConn = new OleDbConnection(sConn))
		{
			oConn.Open();
			using (OleDbCommand oComm  = new OleDbCommand(sComm, oConn))
			{ //first part of Where condition
				oComm.Parameters.Add(new OleDbParameter(){Value=fromdate, OleDbType = OleDbType.DBTimeStamp ;});
				oComm.Parameters.Add(new OleDbParameter(){Value=todate, OleDbType = OleDbType.DBTimeStamp ;});
				//second part of Where condition
				oComm.Parameters.Add(new OleDbParameter(){Value=fromdate, OleDbType = OleDbType.DBTimeStamp ;});
				oComm.Parameters.Add(new OleDbParameter(){Value=todate, OleDbType = OleDbType.DBTimeStamp ;});				using (OleDbDataReader oRdr = oComm.ExecuteReader())
				{
					dt.Load(oRdr);
				}
			}
		}

if(dt.Rows.Count==0)
//no data!
else
//use can use data from dt ;)

Note: OleDb provider for MS Access database engine does not support named parameters! So, you have to add as many parameters as many is used in a query. The order of adding parameters to the SqlParametersCollection is very important!