How to find between two dates like 01-may-2018 to 25-may-2018?

Question

0.00/5 (No votes)

See more:

When i Filter Date like 01-May-2018 To 31-May-2018 then result show may month complete but its show other months date to like 02-Apr-2018 and all date in 01to31 in datagridview.

i have to find only month To month.

its show like this

02-Apr-2018
03-Apr-2018
01-May-2018
08-May-2018
10-May-2018
12-May-2018
31-May-2018
15-Jun-2018
28-Jun-2018
?????

What I have tried:

VB

<pre> Private Function GetRows() As DataTable
        Dim DateStr As DateTime = DateTimePicker1.Value.Date
        Dim DateEnd As DateTime = DateTimePicker2.Value.Date

        Dim dtable As DataTable = New DataTable
        Dim con1 As OleDbConnection
        '  Try
        con1 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\WB001.mdb;Jet OLEDB:Database Password=jhk312;")

        Using cmd1 As OleDbCommand = New OleDbCommand("SELECT * FROM DW1 WHERE Date BETWEEN #" & DateStr.ToString & "# And #" & DateEnd.ToString & "#", con1)
            ' With cmd1.Parameters
            '.Add("@dt1", OleDbType.Date).Value = DateTimePicker1.Value
            ' .Add("@dt2", OleDbType.Date).Value = DateTimePicker2.Value
            ' End With
            Dim adp As New OleDbDataAdapter(cmd1)
            ' con1.Open()
            adp.Fill(dtable)
            DataGridView1.DataSource = dtable
            con1.Close()
            'End Using

        End Using




        ' Catch ex As Exception
        'MsgBox("Error")

        '  End Try
    End Function

Posted 27-Jun-18 3:28am

Arnav121

Updated 27-Jun-18 21:28pm

Add a Solution

Comments

Richard Deeming 27-Jun-18 9:51am

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Using cmd1 As OleDbCommand = New OleDbCommand("SELECT * FROM DW1 WHERE Date BETWEEN ? And ?", con1)
    cmd1.Parameters.AddWithValue("p1", DateStr)
    cmd1.Parameters.AddWithValue("p2", DateEnd)

Richard Deeming 27-Jun-18 9:55am

You can simplify your connection string:

con1 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\WB001.mdb;Jet OLEDB:Database Password=jhk312;")

By default, |DataDirectory| will always point to the application folder for Windows applications, and the App_Data folder for ASP.NET applications.

However, you should be careful if your application will be deployed under the "Program Files" folder. Regular users don't have permissions to write to files in that path, so your application will not be able to use an Access database stored in the application directory. See Where should I store my data?[^] for more information.

Richard Deeming 27-Jun-18 9:56am

What data type is your Date column defined as? It sounds as though it's stored as a string instead of a real date.

Arnav121 28-Jun-18 0:31am

i have stored date Columns Data Type is Text.

Arnav121 28-Jun-18 0:47am

so you say Application Startpath remove and type there |DataDirectory| ??????

Arnav121 29-Jun-18 2:50am

Thnaks again for help after change its work great.

2 solutions

Solution 3

As OriginalGriff[^] mentioned, you have to change Date field to appropriate data type. Note: you also need to change its name, because Date is reserved word[^] for MS Access. Using reserved words may cause several problems...

How to change data type?
1. Open database in exclusive mode[^]
2. Open DW table in design mode
3. Add new field, for example: DateOfEvent and select Date data type (allow nulls)
4. Save changes and close DW table
5. Create new query and execute:

SQL

UPDATE DW SET DateOfEvent = CDate(Date)

6. If update has been successful, you can remove Date field

Well, respectivelly to Richard Deeming[^] 's comment about Sql Injection[^], you have to use parameterized queries instead of concatenated strings:

Seems, you were pretty close to proper solution. Here is "improved" version of your code:

VB

Dim sCon As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\WB001.mdb;Jet OLEDB:Database Password=jhk312;", Application.StartupPath)
Dim sSql As String = "SELECT * FROM DW1 WHERE DateOfEvent BETWEEN @dt1 And @dt2;"

Using con1 As OleDbConnection = New OleDbConnection(sCon)
	con1.Open()
	Using cmd1 As OleDbCommand = New OleDbCommand(sSql, con1)
	    With cmd1.Parameters
		    .Add("@dt1", OleDbType.Date).Value = DateTimePicker1.Value
		    .Add("@dt2", OleDbType.Date).Value = DateTimePicker2.Value
	    End With
	    Dim adp As New OleDbDataAdapter(cmd1)
	    adp.Fill(dtable)
	    DataGridView1.DataSource = dtable
	End Using
    con1.Close()
End Using

Note: Even if official documentation states that OleDb provider does not allow to use named parameters, you can use them, but there's one requirement: you have to add parameters in the same order as their order in sql statement. ;)

Posted 27-Jun-18 21:29pm

Maciej Los

Updated 27-Jun-18 21:30pm

v2

Comments

Richard Deeming 28-Jun-18 12:51pm

"... you have to add parameters in the same order as their order in sql statement."

So they're not really named parameters, then. :)

Maciej Los 29-Jun-18 1:49am

You got me there! :laugh:
It's only semantics ;)

Member 13890537 28-Jun-18 22:57pm

Thank you Maciej Los. I learned something great on you.

Maciej Los 29-Jun-18 1:49am

YOu're very welcome.

Arnav121 29-Jun-18 2:43am

i will try to your improved code
thnks to you for give ans and improvement code.

Maciej Los 29-Jun-18 3:00am

You're very welcome.

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 · Accepted Answer · 2018-06-27T04:10:00

The most likely reason is bad design in your DB. From the values you show, it looks like your date column in the DB is VARCHAR or NVARCHAR rather than DATE.
When you do that, your comparisons for dates will be string comparisons, which means the entire comparison is based on the first different character in the two strings. No further characters are checked.
That means that the sort order for "normal intgers" becomes:

1
10
11
...
19
2
20
21
...
29
3
30
...

And a very similar sort order happens with dates.
The solution is simple: change your DB design, and always store values in appropriate datatypes. Integer values int INT, floating point in FLOAT or DECIMAL, dates in DATE or DATETIME. If you don't you get hassle every time you try to use them...

And as Richard said: 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?