How to detect one date field value is later/greater than the other date field?

Question

0.00/5 (No votes)

See more:

I have one textbox(displays count result), one button(performs the counting), and two DateTimePickers(for date period from and to) in a form. I am using MS Access database for my program with two date fields(date1 and date2). What I want is to count how many records dated with(dates given from datetimepickerfrom and datetimepickerto). The counting is with condition like: If date2 is greater or later than date1 then the counting will base on date2, else on date1. Any suggestion or modification in my code is highly appreciated.

What I have tried:

This is my code but it results error:"Item cannot be found in the collection corresponding to the requested name or ordinal."

VB

 countingconnection()
        countrec = New ADODB.Recordset
        With countrec

 If .Fields("Date2").Value > .Fields("Date1").Value Then
    .Open("select count(*) as count2 from Docstable where Date2 >=#" & DateTimePickerfrom.Value.Date & "# and Date2 <=#" & DateTimePickerto.Value.Date & "#", countcon, 2, 3)
            textbox1.Text = .Fields("count2").Value
            .Close()
else
       .Open("select count(*) as count1 from Docstable where Date1 >=#" & DateTimePickerfrom.Value.Date & "# and Date1 <=#" & DateTimePickerto.Value.Date & "#", countcon, 2, 3)
            textbox1.Text = .Fields("count1").Value
            .Close()
        
end if
   


        End With

Posted 3-Jun-20 1:26am

kyrons

Updated 3-Jun-20 1:44am

Add a Solution

Comments

Richard Deeming 3-Jun-20 7:28am

Why are you still using the long-dead ADODB library in a .NET application? ADO.NET has been around for almost 20 years, and is far superior and much easier to use.

You should also be using parameters rather than trying to stuff the parameter values directly into the query string. In this specific instance you're probably OK, but using string concatenation can and will lead to SQL Injection[^] vulnerabilities.

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 · 2020-06-03T01:46:00

Don't do that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

While your code fragment doesn't directly expose you to SQL Injection, it does expose you to other problems - like the DB server not using the same default date format as your application, at which point you start to get intermittent problems: app crashes, invalid data, you know the kind of thing.
Pass the dates as DateTime directly as parameters instead of converting them to strings, and those problems disappear.

BTW: You can also use SQL BETWEEN to compare:

SQL

... WHILE @DateToCompare BETWEEN Date1 AND Date2

But check the rest of your app and replace any concatenation with parameters!