How to show data between two dates..please help me

Question

3.00/5 (2 votes)

See more:

How to show data in listview1 between two dates. Showing Some ERROR
Please help me...........

Error
https://ibb.co/hOOgPa

Listview
https://ibb.co/gyjo4a

Database
https://ibb.co/eTJ4cv

What I have tried:

VB

Public Sub Displayitemrpt()
        If cn.State = ConnectionState.Open Then
            cn.Close()
        End If
        cn.Open()
        Dim cmd As New OleDb.OleDbCommand("SELECT * FROM pur_inv,inv_type, party_ldg WHERE pur_inv.partyID=party_ldg.partyID and pur_inv.invtypid=inv_type.invtypid and purinvdt BETWEEN '" & txtdtf.Text & " And " & txtdtt.Text & "' ORDER BY purinvdt  ", cn)
        Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader()
        ListView1.Items.Clear()
        Do While dr.Read()
            Dim new_item As New  _
                     ListViewItem(dr.Item("purinvdt").ToString)
            new_item.SubItems.Add(dr.Item("purinvid").ToString)

            new_item.SubItems.Add(dr.Item("invno").ToString)
            new_item.SubItems.Add(dr.Item("invdt").ToString)

            new_item.SubItems.Add(dr.Item("prtynm").ToString)
            new_item.SubItems.Add(dr.Item("invtyp").ToString)
            new_item.SubItems.Add(dr.Item("taxamt").ToString)
            new_item.SubItems.Add(dr.Item("tottaxblamt").ToString)
            new_item.SubItems.Add(dr.Item("invamt").ToString)
            ListView1.Items.Add(new_item)
        Loop
        cn.Close()
    End Sub

Posted 10-Jul-17 3:41am

Jayanta Modak

Updated 10-Jul-17 3:59am

Andy Lanng

v2

Add a Solution

Comments

Michael_Davies 10-Jul-17 9:49am

Your dates and the AND are in quotes so treated as a single entity;

and purinvdt BETWEEN '" & txtdtf.Text & " And " & txtdtt.Text & "' ORDER BY

You open the quote after the BETWEEN and close it after the second date, whereas it ought possibly to be;

and purinvdt BETWEEN '" & txtdtf.Text & "' And '" & txtdtt.Text & "' ORDER BY

2 solutions

Solution 1

Never build queries in strings! This mistake is common AND it leave you open to SQL Injection! This is a bad thing.

First off, This is your mistake:

VB

"'" & txtdtf.Text & " And " & txtdtt.Text & "'"

this evaluates to

VB

"'mytest And myother'"

See, you missed some "'"'s

Use parameters instead. This kind of issue wouldn't occur!
OleDbCommand.Parameters Property[^]

Here's a note on SQL Injection:
SQL Injection[^]

Posted 10-Jul-17 3:52am

Andy Lanng

Updated 10-Jul-17 3:53am

v2

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 · 2017-07-10T03:59:00

Solution 2

Two things:
1) 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. Use Parametrized queries instead.
2) Never trust user input: parse your dates into DateTime values (reporting problems to the user) and pass the DateTime values to SQL

VB

Dim dtFrom as DateTime
If Not DateTime.TryParse(txtdtf.Text, dtFrom) Then
	' Report problem
        ...
	Return
End If
Dim dtTo as DateTime
If Not DateTime.TryParse(txtdtt.Text, dtTo) Then
	' Report problem
        ...
	Return
End If

Dim cmd As New OleDb.OleDbCommand("SELECT * FROM pur_inv,inv_type, party_ldg WHERE pur_inv.partyID=party_ldg.partyID AND pur_inv.invtypid=inv_type.invtypid AND purinvdt BETWEEN @DF AND @DT ORDER BY purinvdt", cn)
cmd.Parameters.AddWithValue("@DF", dtFrom)
cmd.parameters.AddWithValue("@DT", dtTo)
...

Posted 10-Jul-17 3:59am

OriginalGriff

Comments

Jayanta Modak 11-Jul-17 2:05am

Hello sir It is work fine, But a problem date show with time how i can show only date without time please help me.
https://ibb.co/iYyxfF

OriginalGriff 11-Jul-17 2:18am

Use the format options on the ToString for your date-based items:

https://www.codeproject.com/Tips/54577/Formatting-a-DateTime-for-display-format-string-de

Jayanta Modak 11-Jul-17 4:12am

WHERE I USED THE DATE FORMAT (WHICH LINE)

OriginalGriff 11-Jul-17 4:27am

DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously - or if you want help from people.

Jayanta Modak 11-Jul-17 4:41am

I am sorry. unfortunately caps lock button on in my keyboard I have no intention to do this. Again I am very sorry. And I am newer in vb.net 1st time I used the vb.net so, I don't know the where I use the format.
Thanks for your Reply and suggestion
Please forgive me, sorry

OriginalGriff 11-Jul-17 4:44am

Probably, here:

new_item.SubItems.Add(dr.Item("invdt").ToString)
The ToString converts the DateTime value to a string - and you can use a format specification here. If you don't it uses the system-wide default string format.