Im trying to create a date range search in VB.NET and ms acces

Question

1.00/5 (1 vote)

See more:

I am completely newbie in programming and love to learn new things especially code. Can you please help me on solving this.
Im trying to Create a date range search but when I try to search from 1/6/2016 to 2/2/2017 in my Datagridview a date of 2018 appeared.
In my Db my DATE_OF_PAYMENT data type is Date/Time and the format (short date) 11/12/2015.
Can you show me the right code for this. I spend 8hours looking for the solution but still I failed.

VB

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    con.Open()
    Dim ds As New DataSet
    Dim dt As New DataTable
    ds.Tables.Add(dt)
    Dim da As New OleDbDataAdapter
    da = New OleDbDataAdapter("select * from VOUCHER", con)

    da = New OleDbDataAdapter(" Select * from VOUCHER WHERE DATE_OF_PAYMENT between '" + DateTimePicker1.Value + "' and '" + DateTimePicker2.Value + "'", con)
    'da = New OleDbDataAdapter(" Select * from VOUCHER WHERE DATE_OF_PAYMENT >='" & TextBox2.Text & "' AND DATE_OF_PAYMENT <='" & TextBox3.Text & "'", con)



    da.Fill(dt)
    DataGridView1.DataSource = dt.DefaultView
        con.Close()

End Sub

What I have tried:

I tried this code but no display in the datagridview

<pre lang="vb">
Dim str As String
str = "SELECT * FROM VOUCHER WHERE DATE_OF_PAYMENT  = " + DateTimePicker1.Value + " and " + DateTimePicker2.Value
        da = New OleDbDataAdapter(str, con)

Posted 27-Jun-18 22:56pm

Member 13890537

Updated 28-Jun-18 0:45am

Leo Chapiro

v2

Add a Solution

3 solutions

Solution 2

Try BETWEEN: SQL BETWEEN Operator[^]

But don't do it like 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?

Posted 27-Jun-18 23:25pm

OriginalGriff

Updated 27-Jun-18 23:26pm

v2

Solution 3

Date datatype exist for some reasons, that is because you can't compare date in human form. When you compare 2 date, you know that you compare the year first, then the month and day, because you have an understanding of the date.
Computer do not have this understanding when the date is a string, you tell the SQL server that the date is a date and not a simple string.

VB

da = New OleDbDataAdapter(" Select * from VOUCHER WHERE DATE_OF_PAYMENT between '" + DateTimePicker1.Value + "' and '" + DateTimePicker2.Value + "'", con)

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Posted 28-Jun-18 0:46am

Patrice T

Comments

Member 13890537 17-Jul-18 20:59pm

Thanks ppolymorphe for the link and for correcting the way I code. Sorry for the late thanks ^_^

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 · 2018-06-27T23:25:00

Solution 1

Please, read comments and answers to this thread: How to find between two dates like 01-may-2018 to 25-may-2018?[^]
Solution #3 provides an information about proper way to filter data through OleDb.

Posted 27-Jun-18 23:25pm

Maciej Los

Updated 27-Jun-18 23:27pm

v2

Comments

Member 13890537 28-Jun-18 22:48pm

Thank you for teaching me of how to use a parameter in a proper way on searching data.
1 last question?
is it okay for me to leave my Values as (?) in this code?
This is for my input button

Dim str As String
str = "Insert Into HISTORYLOG([VOUCHER_NUMBER],[DATE_OF_PAYMENT],[PAID_TO],[AMOUNT],[ITO],[IFROM],[SALES_OF_INVOICE_NUMBER],[ACTIVITY],[PAYEE_COMPANY],[TYPE_OF_EXPENSE],
[ACCOUNT_CODE],[CHECKE],[BANK],[DATE1],[TYPE_OF_SUPPORTING_DOCUMENT],[VATABLE],[SUBJECT_TO_W_TAX],
[W_TAX_RATE],[INPUT_VAT],[W_TAX],[BALANCE],[TOTAL_PHP],[DATEANDTIME],[EXPORTER_NAME]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

Dim cmd As OleDbCommand = New OleDbCommand(str, con)
cmd.Parameters.Add(New OleDbParameter("VOUCHER_NUMBER", CType(VOUCHER_NUMBERTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DATE_OF_PAYMENT", CType(DATE_OF_PAYMENTTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("PAID_TO", CType(PAID_TOTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("AMOUNT", CType(AMOUNTTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("ITO", CType(TOTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("IFROM", CType(FROMTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("SALES_OF_INVOICE_NUMBER", CType(SALES_OF_INVOICE_NUMBERTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("ACTIVITY", CType(ACTIVITYTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("PAYEE_COMPANY", CType(PAYEE_COMPANYTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("TYPE_OF_EXPENSE", CType(TYPE_OF_EXPENSETextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("ACCOUNT_CODE", CType(ACCOUNT_CODETextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("CHECKE", CType(CHECKTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("BANK", CType(BANKTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DATE1", CType(DATE1TextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("TYPE_OF_SUPPORTING_DOCUMENT", CType(TYPE_OF_SUPPORTING_DOCUMENTTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("VATABLE", CType(VATABLETextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("SUBJECT_TO_W_TAX", CType(SUBJECT_TO_W_TAXTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("W_TAX_RATE", CType(W_TAX_RATETextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("INPUT_VAT", CType(INPUT_VATTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("W_TAX", CType(W_TAXTextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("BALANCE", CType(BALANCETextBox.Text, String)))
cmd.Parameters.Add(New OleDbParameter("TOTAL_PHP", CType(TOTAL_PHPTextBox.Text, String)))
'cmd.Parameters.Add(New OleDbParameter("INPUTER_NAME", CType(InputerNametxt.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DATEANDTIME", CType(DATEANDTIMEtxt.Text, String)))
cmd.Parameters.Add(New OleDbParameter("EXPORTER_NAME", CType(ExporterNametxt.Text, String)))

cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()

Maciej Los 29-Jun-18 1:52am

Yeah, this is proper statement. Try it to find out. ;)

Member 13890537 29-Jun-18 3:43am

I have been using it. Just worrying if it's okay to leave the values (?) because I saw some article that they naming the Values.
Before I don't know how to use this parameter code for Selecting(Searching) that is why i use concatenate strings to build a SQL command Until you
taught me. Thank you very much for being kind ^_^

Maciej Los 29-Jun-18 3:44am

You're very welcome.
Cheers,
Maciej

Member 13890537 2-Jul-18 1:15am

Healthy Day Mr.Maciej. Here I am again feeling shy about asking question But i have to do this cause i want to learn more.
Can you help me about the proper way to close Excel the code that i use is in the link https://www.codeproject.com/Questions/1250678/How-do-I-properly-close-excel-in-VB-NET