Resolved Search data between two dates and by Customer name

rajdh75

Member
Joined
Mar 30, 2020
Messages
19
Programming Experience
Beginner
Hi,
I am using vb.net and Access database.
In Access database there is a table named TblInvoiceQuery. It has DateofSale, InvoiceID, CustomerName, TypeofSale, SumOfTotal columns.
The DateofSale is set to general date format.
I have four datepickers named as - Datefrom, TimeFrom and DateTo, TimeTo.
Datagridview is used to display result. The Datagridview is unbounded for datasource because TblInvoiceQuery is a query based table.
A button named Showdata is used for code.
The code is -
VB.NET:
 Private Sub Showdata()

        con.Close()
        con.Open()
        Dim cmd As New OleDbCommand("select * from [TblInvoiceQuery] where DateofSale between #" & DateFrom.Value.Date & " " & TimeFrom.Value.ToShortTimeString & "# And #" & DateTo.Value.Date & " " & TimeTo.Value.ToShortTimeString & "#", con)

        Dim da As New OleDbDataAdapter
        Dim dt As New DataTable
        da.SelectCommand = cmd
        dt.Clear()
        da.Fill(dt)
        DataGridView1.AutoGenerateColumns = False
        DataGridView1.Columns(0).DataPropertyName = "DateofSale"
        DataGridView1.Columns(1).DataPropertyName = "InvoiceID"
        DataGridView1.Columns(2).DataPropertyName = "CustomerName"
        DataGridView1.Columns(3).DataPropertyName = "TypeofSale"
        DataGridView1.Columns(4).DataPropertyName = "SumOfTotal"
        DataGridView1.DataSource = dt

        con.Close()
I have a combo box named as CmbCustomername which has Customer names in it.
I want to search by date range and by Customer name.
How should I write code for searching both ?

Thanks in advance.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,283
Location
Sydney, Australia
Programming Experience
10+
First of all, is there a good reason to have separate controls for date and time? A DateTimePicker will display both and allow you to set both. The only advantage I can see that separate controls would provide would be the ability to display a drop-down calendar for the date and spin buttons for the time. If you don't specifically want that then just use one control for both and use a format that displays both. You can still use the up and down arrow keys to set the time if you have a drop-down calendar. Whether you use two controls or one, you should be using parameters in your ADO.NET code. You should ALWAYS use parameters in ADO.NET code.

As an aside, DateFrom and TimeFrom are bad names for those controls. Those would be appropriate names for variables that actually contained a date and a time, not for controls used to pick them. DateFromPicker and TimeFromPicker would be far more appropriate names.

Another point is that your invoice table should not contain any customer names. The customer table should contain those names and an ID and then your invoice table should contain a foreign key to that ID column. That's how relational databases work. You can display names in the ComboBox and still get the corresponding ID for the query.

Ignoring that last point for now, your code should look something like this for a single control:
VB.NET:
Dim cmd As New OleDbCommand("SELECT * from TblInvoiceQuery WHERE DateOfSale BETWEEN @DateOfSaleFrom AND @DateOfSaleTo AND CustomerName = @CustomerName", con)

With cmd.Parameters
    .Add("@DateOfSaleFrom", OleDbType.Date).Value = DateTimeFromPicker.Value
    .Add("@DateOfSaleTo", OleDbType.Date).Value = DateTimeToPicker.Value
    .Add("@CustomerName", OleDbType.VarChar, 50).Value = CmbCustomerName.Text
End With
If you use two controls for each date/time then only two lines change:
VB.NET:
.Add("@DateOfSaleFrom", OleDbType.Date).Value = DateFromPicker.Value.Date + TimeFromPicker.Value.TimneOfDay
.Add("@DateOfSaleTo", OleDbType.Date).Value = DateToPicker.Value.Date + TimeToPicker.Value.TimeOfDay
I would also tend to use a data reader and call Load on the DataTable but that's not a big deal. If you are going to use a data adapter though, don't do it that way. You don't need to create a command and set the SelectCommand like that. Just do this:
VB.NET:
Dim adapter As New OleDbDataAdapter("SELECT * from TblInvoiceQuery WHERE DateOfSale BETWEEN @DateOfSaleFrom AND @DateOfSaleTo AND CustomerName = @CustomerName", con)

With adapter.SelectCommand.Parameters
    .Add("@DateOfSaleFrom", OleDbType.Date).Value = DateTimeFromPicker.Value
    .Add("@DateOfSaleTo", OleDbType.Date).Value = DateTimeToPicker.Value
    .Add("@CustomerName", OleDbType.VarChar, 50).Value = CmbCustomerName.Text
End With
Also, don't open and close the connection manually when using a data adapter. Fill and Update will both open and close the connection automatically. It will be left in the same state it was found in. When using data adapters, only open and close manually if you are using multiple in a row.
 

rajdh75

Member
Joined
Mar 30, 2020
Messages
19
Programming Experience
Beginner
Thank you for your solution.
It help me lot.
 
Top Bottom