Click here to Skip to main content
14,176,870 members
Rate this:
 
Please Sign up or sign in to vote.
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:

<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
Updated 27-Jun-18 21:28pm
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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:
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:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
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?
   
Comments
Arnav121 28-Jun-18 0:41am
   
is my code is correct or not?
and my date column data type is text.
OriginalGriff 28-Jun-18 1:30am
   
And that is your problem. STRINGS COMPARE USING STRING COMPARISON, not dates.

Change your DB to use appropriate datatypes. If you don't, this will be the very least of your problems ... you will get huge failures later that are really difficult to deal with.
Arnav121 29-Jun-18 2:39am
   
i can change the datatype text to date/time and its work great now.
thnks man for help Original Griff And Richard Deeming.
OriginalGriff 29-Jun-18 2:40am
   
You're welcome!
Rate this: bad
 
good
Please Sign up or sign in to vote.

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:
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:
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. ;)
   
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190526.1 | Last Updated 28 Jun 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100